Look like Oracle is doing exactly what it's been told
Step 1 - create an internal temporary table from the inline view
with a sort (unique) for the DISTINCT
Step 2 - for each row in step one, (i.e. nested loop) get the
streets related to the output from step 1
Step 3 - with the row source produced from step 1 and
step 2, generate a hash table, then scan the F15 table
to probe the hash table. Unfortunately, there is a hint
to use and index when accessing the F15 table, so
Oracle uses a full scan in order to meet the requirements
of the hint and the hash simultaneously.
Ideally you probably want to get better stats on the F15 table
so that Oracle realises that an indexed NL access into F15
is a good idea; or you want to add a USE_NL(F15) hint to
stop the hash join happening.
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Author of:
Practical Oracle 8i: Building Efficient Databases
Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.
-----Original Message-----
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 05 October 2001 15:16
I'm a little perplexed by this query and it's associated plan. It's
also a big
performance problem. The problem is the 35 million row table clearly.
But
looking at the plan at the bottom, I'm not sure where the sorting is
going on.
Would anyone say the index full scan on the 35 million row table is
being
sorted? Or does it look more like it's being fed to a nested loops
query?
Thanks,
Doug
SELECT /*+ ORDERED INDEX(S_ S15_IX1) INDEX(BUS_FID F15_UK1)
INDEX(STREET
A15_IX1) */ SDE.STREET.CFCC, SDE.STREET.BUS_FID
,S_.eminx,S_.eminy,
S_.emaxx,S_.emaxy, BUS_FID.fid,BUS_FID.numofpts,BUS_FID.entity,
BUS_FID.points,BUS_FID.rowid
FROM
(SELECT /*+ INDEX(SP_ S15_IX1) */ DISTINCT
sp_fid,eminx,eminy,emaxx,emaxy
FROM SDE.S15 SP_ WHERE SP_.gx >= :1 AND SP_.gx <= :2 AND SP_.gy >=
:3
AND
SP_.gy <= :4 AND SP_.eminx <= :5 AND SP_.eminy <= :6 AND SP_.emaxx
>= :7
AND SP_.emaxy >= :8) S_, SDE.STREET , SDE.F15 BUS_FID WHERE
S_.sp_fid =
BUS_FID.fid AND S_.sp_fid = SDE.STREET.BUS_FID
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 45 473.15 475.04 223532 66153503 0
4494
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 47 473.15 475.04 223532 66153503 0
4494
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 20
Rows Row Source Operation
------- ---------------------------------------------------
4494 HASH JOIN
4494 NESTED LOOPS
4495 VIEW
4495 SORT UNIQUE
4817 INDEX RANGE SCAN (object id 7356)
4494 TABLE ACCESS BY INDEX ROWID STREET
8988 INDEX UNIQUE SCAN (object id 7355)
33065402 TABLE ACCESS BY INDEX ROWID F15
33065403 INDEX FULL SCAN (object id 7283)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Doug C
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jonathan Lewis
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).
