CONNECT BY queries are self joins -- joining the table to itself. For every
row, Oracle searches the table to find a match(s), then marches to the next
row, searches for a match, etc.
If you don't index then for a 500 row table you march down the table row by
row, and for every row you do a full table scan, resulting in 500 full table
scans.
So check the columns in your SQL that follow the START WITH and CONNECT BY
clauses. Indexing these columns is the first thing (and perhaps the only
thing) you need to do.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Greg Moore
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).
