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