RE: perplexing plan?

From: Christopher Spence <cspence_at_FuelSpot.com>
Date: Fri, 05 Oct 2001 09:11:12 -0700
Message-ID: <F001.003A37DE.20011005083022@fatcity.com>

Your sort unique is to satisfy the DISTINCT against the table S15.

One thing may be a problem is you do a full scan of the index 7283, then retrieve each and every row but one from the table. There isn't really any point of even using this index as it will only slow down the query as it has to go through the index 33 Million times and read the 33 million rows one by one. It would be much more effective to just full table scan against the table directly. Perhaps look at using Partitioning and parallel query.

I would try removing the index hint for the 33 Million row table.

"Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence
Oracle DBA
Phone: (978) 322-5744
Fax: (707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863

-----Original Message-----
Sent: Friday, October 05, 2001 10:30 AM
To: Multiple recipients of list ORACLE-L

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: dcowles_at_i84.net

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).
--

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

Author: Christopher Spence
INET: cspence_at_FuelSpot.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------