How to tune query to fetch result faster | Oracle 19c |-CodePudding

I have a table which as huge records in table

My tables : employee and customer

Now the issue here is I have 2 billion records in employee table and 1 billion records in customer table

Employee columns

empid
empname
empage
empdcourse

Customer columns

custid 
custdesc
custmessage

My query :

select emp_id from employee where empid not in (  select custid from customer);

Error : It throws me table space issue. Not allowed to increase table space

Is their any way I can tune my query or run in batch by batch so I get output

Any solution is much appreciated !!!

Need it on high priority

CodePudding user response：

NOT EXISTS may be more efficient and less memory consuming in such case.

(The query suggests Customer and Employee share the same PK, does it mean you have an "super" table Person ?)

CodePudding user response：

You can add indexes for columns, for example, if they aren’t primary keys:

CREATE INDEX empid_index
ON employee(empid);

Also, you can update the query:

select e.empid from employee e where not exists (select 1 from customer c where c.custid = e.empid);