Home » SQL & PL/SQL » SQL & PL/SQL » connect by query
connect by query [message #257437] Wed, 08 August 2007 06:38 Go to next message
jinga
Messages: 116
Registered: January 2003
Senior Member
select /*+ FIRST_ROWS */ * 
from (select /*+ FIRST_ROWS */ 
tree_version_sid,col1,col2, b.tree_id as tree_code,col5,col6...
from ixrev_index_data a, tree_version b 
where a.tree_version_sid = b.tree_version_sid 
 and a.job_sid = 1 and a.tree_category in ('20','43','23') 
connect by prior a.PPI_INDEX_SID = a.HIGHER_INDEX_SID 
and prior a.tree_version_sid = a.tree_version_sid 
start with a.HIGHER_INDEX_SID is null )



I am using oracle 10.2. the above query takes so long to execute around 6 to 7 minutes to retrieve 30000 rows.


SELECT STATEMENT ()     [NULL] 
 VIEW ()        [NULL] 
  FILTER ()     [NULL] 
   CONNECT BY (WITH FILTERING)  [NULL] 
    NESTED LOOPS ()     [NULL] 
     TABLE ACCESS (FULL)        IXREV_INDEX_DATA 
     TABLE ACCESS (BY INDEX RO  TREE_VERSION 
      INDEX (UNIQUE SCAN)       PK_TREE_VERSION 
    NESTED LOOPS ()     [NULL] 
     NESTED LOOPS ()    [NULL] 
      BUFFER (SORT)     [NULL] 
       CONNECT BY PUMP ()       [NULL] 
      TABLE ACCESS (BY INDEX R  IXREV_INDEX_DATA 
       INDEX (SKIP SCAN)        PK_IXREV_INDEX_DATA 
     TABLE ACCESS (BY INDEX RO  TREE_VERSION 
      INDEX (UNIQUE SCAN)       PK_TREE_VERSION 
    NESTED LOOPS ()     [NULL] 
     TABLE ACCESS (FULL)        IXREV_INDEX_DATA 
     TABLE ACCESS (BY INDEX RO  TREE_VERSION 
      INDEX (UNIQUE SCAN)       PK_TREE_VERSION



it does not use the index on job_sid created on table ixrev_index_data.

i would like the query to execute faster.

[Updated on: Wed, 08 August 2007 06:41] by Moderator

Report message to a moderator

Re: connect by query [message #257439 is a reply to message #257437] Wed, 08 August 2007 06:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Remove FIRST_ROWS.

Regards
Michel
Re: connect by query [message #257443 is a reply to message #257437] Wed, 08 August 2007 06:52 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
FIRST_ROWS will tend to encourage the use of indexes because they don't require the overhead of building hash tables for hash joins or merging result sets. However, indexed access is often inefficient when it is used for the entire query across a large number of rows.

As Michel suggests, I would first remove FIRST_ROWS and see if that changes the plan. If the plan is the same, you may need to look at putting a FULL hint against ixrev_index_data to avoid the skip scan access path. In my experience, skip scan is a method which is best avoided unless you are retrieving a very small number of rows.

Also, it goes without saying that your stats should be up to date. In particular, you may have to do a "for all indexed columns" in your dbms_stats call so that histogram data is produced.
Previous Topic: Print code for a query
Next Topic: Joining Query after selecting TOP N
Goto Forum:
  


Current Time: Fri Dec 06 18:22:40 CST 2024