Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> connect by query tuning

connect by query tuning

From: Alex Ivascu <alexdivascu_at_hotmail.com>
Date: Wed, 7 Apr 2004 09:56:39 -0700
Message-ID: <NoWcc.58$Pw1.38@fed1read05>


Oracle 9.2x on Windows.

Any tips on getting rid of the buffer sort in this xplan?




| Id | Operation | Name | Rows |
Bytes | Cost |


| 0 | SELECT STATEMENT | | 10 |
460 |     5 |
|*  1 |  CONNECT BY WITH FILTERING    |                           |       |

| |
| 2 | NESTED LOOPS | | |
| |
|* 3 | INDEX SKIP SCAN | PK_SOL_OBJECT_MEMBER | 1 | 26 | 12 |
| 4 | TABLE ACCESS BY USER ROWID | SOL_OBJECT_MEMBER | |
| |
| 5 | NESTED LOOPS | | |
| |
| 6 | BUFFER SORT | | 10 |
460 | |
| 7 | CONNECT BY PUMP | | |
| |
| 8 | TABLE ACCESS BY INDEX ROWID| SOL_OBJECT_MEMBER | 10 |
460 | 5 | |* 9 | INDEX RANGE SCAN | PK_SOL_OBJECT_MEMBER | 10 |
| 3 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):


   1 -
filter(RAWTOHEX("SOL_OBJECT_MEMBER"."SOLUTION_ID")='0F9233A0836B91D892BEAC10 0

              0A90000' AND "SOL_OBJECT_MEMBER"."OBJECT_ID"=1 AND "SOL_OBJECT_MEM
              BER"."MEMBER_ID"<>0)
   3 - access("SOL_OBJECT_MEMBER"."OBJECT_ID"=1)

filter(RAWTOHEX("SOL_OBJECT_MEMBER"."SOLUTION_ID")='0F9233A0836B91D892BEAC10 0

              0A90000' AND "SOL_OBJECT_MEMBER"."OBJECT_ID"=1 AND "SOL_OBJECT_MEM
              BER"."MEMBER_ID"<>0)
   9 - access("SOL_OBJECT_MEMBER"."SOLUTION_ID"=NULL AND "SOL_OBJECT_MEMBER"."OB
              JECT_ID"=NULL) Received on Wed Apr 07 2004 - 11:56:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US