Optimizer Question

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Tue, 07 Aug 2001 16:40:50 -0700
Message-ID: <F001.003638F1.20010807162051@fatcity.com>

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
ian_at_slac.stanford.edu

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: MacGregor, Ian A.
  INET: ian_at_SLAC.Stanford.EDU

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: ListGuru_at_fatcity.com (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). Received on Tue Aug 07 2001 - 18:40:50 CDT