RE: perplexing plan?

From: Koivu, Lisa <lisa.koivu_at_efairfield.com>
Date: Fri, 05 Oct 2001 08:30:29 -0700
Message-ID: <F001.003A361F.20011005071522@fatcity.com>

Doug, it's your distinct in the subquery that's causing the sort.  Do you need to have distinct in there?

By the way, why do you have index hints in the outer query?  Are you only sending us half the query? 

Lisa Koivu
Oracle Database Administrator
Fairfield Resorts, Inc.
954-935-4117

-----Original Message-----

From:   Doug C [SMTP:dcowles_at_i84.net]
Sent:   Friday, October 05, 2001 10:30 AM
To:     Multiple recipients of list ORACLE-L

Subject:        perplexing plan?

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

Received on Fri Oct 05 2001 - 10:30:29 CDT