Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Cartesian Join query optimization

Alexei H.Apr 16 2012 — edited Apr 17 2012
Hello Experts!

I have a question about cartesian join query which might look a bit weird.

Assume the SQL query like that:

SELECT DISTINCT A.NAME
FROM A, B, C;

Here we have cartesian join for 3 tables A, B and C.
It looks to me, in order to get the result for such a particular SQL tables/sets B and C need to be accessed only to ensure they return at least 1 row (otherwise result set is empty), query table A, but there is no actual need to join the data.
If you run such a query Oracle is doing full table scans and actually joins the data.
Yes, DBMS is doing exactly what you are asking for, but I wonder if there is any way (SQL hint or db parameter or anything else) to enforce more optimal access path here?

Obvious solution to remove B and C tables from the SELECT statement is not acceptable. :-)

Thank you!
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 15 2012
Added on Apr 16 2012
8 comments
862 views