0

I have two tables with the structure below:

create table Table_One
(Ef_date           Date,
Customer_Num      Number,
<Other_Columns>   <Combination of Number/varchar2(50)>);


create table Table_TWO
(Ef_date           Date,
Customer_Num      Number,
<Other_Columns>   <Combination of Number/varchar2(50)>);

There is a Normal index on the customer_num column of both tables and there is a partition on the ef_date column. I want to know which query is better in terms of performance when there is a huge amount of data, for instance there are nearly 54,962,020 records added each month .

QUERY-1:

select Customer_Num
from Table_one 
<Conditions on Other_columns>

    intersect

select Customer_Num
from Table_TwO 
<Conditions on Other_columns>


QUERY-2:

select t1.customer_Num
  from Table_one t1
inner join Table_Two t2
on t1.customer_num = t2.customer_num
  • 2
    I edited your question - but the number of records added each month (which table?) is not important for this question - what is important is the number of records in each table at the time of the query! Can you not run an EXPLAIN PLAN on both - even seeing the timing of both queries should give you an idea of which runs faster - which I presume is what you want? – Vérace Jun 9 '20 at 16:05
  • 1
    Also, it would greatly depend on the number of unique Customer_Num on each table, and if there is a huge number of rows on both tables, the intersect would likely take much longer. – Mark Stewart Jun 18 '20 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.