The Oracle 9 plan is showing you an optimisation
option known as 'table pre-fetching'. Essentially
the plans are the same, but Oracle can make a
cost-based decision when using index range scans
in nested loops to stop the access path at the index
leaf block and accumulate all the rowids from the
inner table. These rowids can then be sorted and
the inner table accessed in block order.
On the plus side, you may see a reduction in latched
gets on table blocks and even a reduction in physical
I/O due to the elimination of highly random table accesses.
On the minus side, the rowids have to be sorted - which
may consume extra CPU and memory. This trade-off
can (apparently) be weighed by the v9 CBOptimizer.
(I've yet to see this path actually take place at run time,
the CBO always seems to fall back to the traditional
NL mechanism, even though it always seems to report
the new mechanism)
The v9 plan is essentially the same as the v8 plan -
but the outermost steps can be read as:
The nested loop joins only to the index, and then
feeds a sorted set of rowids to the table access
operation.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
Next public appearances:
March 2004 Hotsos Symposium - The Burden of Proof
March 2004 Charlotte NC OUG - CBO Tutorial
April 2004 Iceland
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February
____UK___June
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
----- Original Message -----
From: "Sai Selvaganesan" <ssaisundar@(protected)>
To: <oracle-l@(protected)>
Sent: Wednesday, February 04, 2004 7:13 PM
Subject: sql explain plan issue
hi
the below is a sql code and explain plan on two environments the first one
is 81.7.3 and the next one is 9i. can someone please point out why there is
a diff
SELECT SQ1_T2.ORG_ID
FROM S_PARTY SQ1_T1, S_ORG_BU SQ1_T2, S_ORG_EXT SQ1_T3
WHERE
(SQ1_T3.INT_ORG_FLG = 'Y') AND
(SQ1_T1.ROW_ID = SQ1_T3.PAR_ROW_ID AND SQ1_T2.BU_ID = SQ1_T1.ROW_ID) AND
(SQ1_T3.PAR_BU_ID = '0-R9NH')
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 NESTED LOOPS
2 1 NESTED LOOPS
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_EXT'
4 3 INDEX (RANGE SCAN) OF 'S_ORG_EXT_F13' (NON-UNIQUE)
5 2 INDEX (UNIQUE SCAN) OF 'S_PARTY_P1' (UNIQUE)
6 1 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_BU'
7 6 INDEX (RANGE SCAN) OF 'S_ORG_BU_M4' (NON-UNIQUE)
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_BU'
2 1 NESTED LOOPS
3 2 NESTED LOOPS
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_EXT'
5 4 INDEX (RANGE SCAN) OF 'S_ORG_EXT_F13' (NON-UNIQUE)
6 3 INDEX (UNIQUE SCAN) OF 'S_PARTY_P1' (UNIQUE)
7 2 INDEX (RANGE SCAN) OF 'S_ORG_BU_M4' (NON-UNIQUE)
the objects are the same but the plan is different. this is a third party
software, hence rule based.
thanks
sai
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
