SQL> SELECT * FROM V$Version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

Elapsed: 00:00:00.08
SQL> 


Here is the explain plan of the query. Improving the performance of this query is the one I am lookin into.

SQL>  SELECT PARENT_PK, CONTENTITEM_PK, FOLDER_PK, MDK, LEVEL FROM CMF_CONTENTITEM 
  2   START WITH CONTENTITEM_PK = 1 CONNECT BY PRIOR CONTENTITEM_PK=PARENT_PK 
  3   ORDER BY LEVEL DESC;

no rows selected

Elapsed: 00:00:03.01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4407 Card=732073 Byt
          es=17569752)

   1    0   SORT (ORDER BY) (Cost=4407 Card=732073 Bytes=17569752)
   2    1     CONNECT BY (WITH FILTERING)
   3    2       NESTED LOOPS
   4    3         INDEX (UNIQUE SCAN) OF 'PK_CMF_CONTENTITEM' (UNIQUE)
           (Cost=2 Card=1 Bytes=6)

   5    3         TABLE ACCESS (BY USER ROWID) OF 'CMF_CONTENTITEM'
   6    2       HASH JOIN
   7    6         CONNECT BY PUMP
   8    6         TABLE ACCESS (FULL) OF 'CMF_CONTENTITEM' (Cost=975 Card=732073 Bytes=17569752)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        255  bytes sent via SQL*Net to client
        235  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          0  rows processed


--The count of records
SELECT COUNT(DISTINCT PARENT_PK ) FROM VIGN_DATA.CMF_CONTENTITEM  --6684 

SELECT COUNT(DISTINCT CONTENTITEM_PK) FROM VIGN_DATA.CMF_CONTENTITEM--732083 

CONTENTITEM_PK is the primary key.