Suppose I have two tables.
SQL> describe test
Name Null? Type
------------------------------- -------- ----
NVALUE NOT NULL NUMBER(2)
SQL> describe test2
Name Null? Type
------------------------------- -------- ----
NVALUE NUMBER(2)
CVALUE VARCHAR2(10)
with the following keys
SQL> l
1 select a.constraint_name, r_constraint_name, b.column_name, constraint_type
2 user_constraints a, user_cons_columns b
3 where a.constraint_name = b.constraint_name
4 and a.table_name = b.table_name
5 and a.table_name in ('TEST', 'TEST2')
6* order by 2 desc
SQL> /
CONSTRAINT_NAME R_CONSTRAINT_NA COLUMN_NAME C
------------------------------ --------------- --------------- -
TEST_PK NVALUE P
TEST2_FK TEST_PK NVALUE R
and the primary key is enforced via a unique index.
You issue the following query
select a.nvalue, a.cvalue from
test2 a, test b
where a.nvalue = b.nvalue(+)
Oracle explain plan is
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS (OUTER)
2 1 TABLE ACCESS (FULL) OF 'TEST2'
3 1 INDEX (UNIQUE SCAN) OF 'TEST_PK' (UNIQUE)
---------------------------------------------------------------------------------------------------
Why does Oracle even look at the test_pk index? All vaues returned by the query are
from one table? The outer join says to print out the rows from test2 reguardless of
whether a matching row is found in test1. The outer join would have to check test in
case there are more duplicate join keys which match the join key in test2; except that
the field in test 1 is uniquely indexed, and Oracle knows that.
If a natural join is requested
select a.nvalue, a.cvalue from
test2 a, test b
where a.nvalue = b.nvalue
The plan is
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (FULL) OF 'TEST'
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'TEST2'
4 3 INDEX (RANGE SCAN) OF 'TEST2_FK' (NON-UNIQUE)
Again why does it look at test. A natural join does have to make sure the join keys
are in both tables. However Oracle knows via the constraints that whatever key
exists in TEST2 must exist in TEST. It also knows because of the unique index
on TEST(nvalue) table that it doesn't need to consider the possiblility of duplicate
keys.
--------------------------------------------------------------------------------------------------------------------
The above is trivial. However there are times when Oracle's behavior can be very
frustrating. Imagine TEST and TEST2
with more columns, and a great many rows, imagine a view joining them, imagine the
performance increase if Oracle would join the tables in the view only when necessary.
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: MacGregor, Ian A.
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
