is this possible without using a stored procedure? nned to cut down on disk reads

using 9.2.0.3.0 on Sun Starfire running SunOS 5.8, with 8 CPUS, 12 gigs ram, 2 TB disk

we need to do recursive lookups on a table for certain rows. We are doing it with a stored procedure but its doing 6,000,000 disk reads (using 2 tables one 70k rows other 7k rows with indexes)

we want to know if it is possible to do this via sql using analytical functions or some other 9i feature to cut down on disk reads

we have a table A (70k rows) as follows:

Ticket_num      ticket_type      Master_Ticket_num Comp. Category    
2344                    M                       2344                z        1
3543                    S                       2344                d       3
3976                    S                       2344                x        4 
854                     M                       854                  c       7 

each row is a separate trouble ticket with its unique tick num type (m-master
- s - sub_to_a_master) the master ticket it references (itself if a master
type, the master it is a sub to if it is not a master tick type) the comp (component is references) and its status. All tickets come as masters but users, if the tickets are related, will make one the master and others subs to it, any comments added to a master are reflected in the subs (but no other changes just comments)

and another table B

componet_id_sq  Componet        Category
344543                  z                       1
3644                      d                       2
8432                    c                       1

each row has a unique component id

what we need to do is the following:

for each master ticket in table A,

• find all the subtickets associated with it in table A.
• find all the componet ids associated with those tickets
• for all those ids look in table B to detemine which on has the highest category
• update that category to the appropriate master ticket in table A

the stored procedure works fine, but takes 90 mins to run and does 6,000,000 disk reads. we want to know if it is possible to do via analytic functions or some way were we cut down on reads.

the code for it in the proceudre is

CURSOR master_cur IS
SELECT DISTINCT vt.MASTER_TICKET_SQ_NUM   FROM V_LM_COMP_TICKETS vt WHERE cat <> 9 AND ticket_type = 'M';    master_row master_cur%ROWTYPE;

BEGIN  FOR master_row IN master_cur

            LOOP

                BEGIN   
                                SELECT MIN(cat) INTO v_cat 
                                FROM LM_COMP WHERE COMPOMENT_ID_SQ IN (
                                SELECT COMPOMENT_ID_SQ
                                FROM V_LM_COMP_TICKETS vt
                                WHERE vt.MASTER_TICKET_SQ_NUM =                
                                master_row.MASTER_TICKET_SQ_NUM
                                AND cat <> 9);
                                
                                
                          UPDATE TICKET_TRACKING SET RECAT_CAT = v_cat 
                        WHERE MASTER_TICKET_SQ_NUM = 
                        master_row.MASTER_TICKET_SQ_NUM;
                
                END;    
END LOOP;
        

plan for the select cause is

SELECT STATEMENT Optimizer Mode=CHOOSE          1               141            
  SORT AGGREGATE                1       53                                     
    HASH JOIN           88      4 K     141                                    
      HASH JOIN         88      4 K     126                                    
        TABLE ACCESS BY INDEX ROWID     TICKET_TRACKING 118     944     33     
             INDEX RANGE SCAN      MASTER_TIC_IDX  118             4           
           VIEW    V_LM_COMP_ALL   53 K    1 M     92                          
             UNION-ALL                                                         
               TABLE ACCESS FULL   LM_COMP 61      488     14                  
               TABLE ACCESS FULL   LM_COMP_SUMMARY_ARCHIVE 53 K    523 K   78  
         TABLE ACCESS FULL LM_COMP 6 K     38 K    14                          
             

on the update

UPDATE STATEMENT Optimizer Mode=CHOOSE          118             33             
  UPDATE        TICKET_TRACKING                                                
    INDEX RANGE SCAN    MASTER_TIC_IDX  118     590     4                      
                  

any way to do this faster or more efficiently? Received on Fri Aug 13 2004 - 09:53:07 CDT