
create table EMPLOYEE
(
company_id bigint(5),
dept_id bigint(5),
emp_id bigint(5),
emp_name varchar(100),
 primary key (company_id, emp_id)
) partition by list (company_id) (
	partition p_1000 values in (1000),
	partition p_2000 values in (2000),
	partition p_3000 values in (3000)
);


create table DEPT
(
company_id bigint(5),
dept_id bigint(5),
dept_name varchar(100),
primary key (company_id, dept_id)
) partition by list (company_id) (
	partition p_1000 values in (1000),
	partition p_2000 values in (2000),
	partition p_3000 values in (3000)
);

insert into dept values (1000, 10, 'Engineering');
insert into dept values (1000, 20, 'Product Management');
insert into dept values (1000, 30, 'QA');

insert into dept values (2000, 40, 'Support');
insert into dept values (2000, 50, 'Professional Services');

insert into employee values (1000, 10, 1, 'John');
insert into employee values (1000, 10, 2, 'Smith');

insert into employee values (1000, 20, 3, 'Jacob');
insert into employee values (1000, 20, 4, 'Brian');

insert into employee values (1000, 30, 5, 'Chris');
insert into employee values (1000, 30, 6, 'Ryan');

insert into employee values (2000, 40, 7, 'Karin');
insert into employee values (2000, 40, 8, 'Jay');

insert into employee values (2000, 50, 9, 'Ana');
insert into employee values (2000, 50, 10, 'Jessica');

commit;

explain partitions
select * from employee
where company_id = 1000
and dept_id in (select dept_id from dept where COMPANY_ID = 1000);