Using Join instead of Minus or not in operator for improving query performance

Akshs

Having two table Tab_A and Tab_B.  And the requirement is my query should return the result ( Tab_A - Tab_b) i.2 10,20,30 .

Tab_A      Tab_B

10               40

20               50

30

40

50

Option -1:   (Using minus)                   

Select * from Tab_A

minus

Select * from Tab_B;

Option -2: (Using Not In)

Select * from Tab_A

where Tab_A.c1 not in ( Select Tab_B.c1 from Tab_B);

Option - 3 (Using Left outer Join to Improve the query performance)

Select Tab_A.c1 from Tab_A left outer join Tab_b

on (Tab_A.c1=Tab_B.c1)

where Tab_b.c1 is null;

Please check the detail explanation  :

top.gun

So there is nothing to vote for?

William Robertson

This seems to be a tip rather than a suggestion for Oracle to implement something new.

Chris Hunt

And your suggestion is?

I wouldn't bank on any of these options "improving the performance." The Oracle optimiser is pretty clever, and may well use the same query plan in all three cases.

Akshs

And your suggestion is?

I wouldn't bank on any of these options "improving the performance." The Oracle optimiser is pretty clever, and may well use the same query plan in all three cases.

Could you please go through the below link where I attached the explain plan.

Martin Preiss

as others I fail to see here a proposal of a new feature - since the optimizer is already able to do corresponding transforms internally.

Furthermore I think it's common knowledge that NOT IN will not return a result set when the subquery contains NULL values.

Unknown

This may not be an actionable idea for the Database Ideas list...

May I suggest we adjourn to the space to continue the discussion.

LKR

Akshs

This may not be an actionable idea for the Database Ideas list...

May I suggest we adjourn to the space to continue the discussion.

LKR

Thanks for guiding me.

But I tried to publish in SQL PLSQL Space..But because of permission issue I'.m not able to publish...

Unknown

Thanks for guiding me.

But I tried to publish in SQL PLSQL Space..But because of permission issue I'.m not able to publish...

I can move this on your behalf.

Regards,

LKR

Akshs

I can move this on your behalf.

Regards,

LKR

Thank you....

Akshs

I can move this on your behalf.

Regards,

LKR

Even I'm not able to send any message. Could you please publish the below document in SQL PL\SQL space.

Could  please accept the request, So I'm able to message you.