Home » RDBMS Server » Performance Tuning » Better way to handle this query? (Oracle 9i)
Better way to handle this query? [message #335505] Tue, 22 July 2008 06:38 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member

    select /*+ rule*/ c.case_cd,c.c_item_cd,e.invoice_no,e.location_cd as "loc_cd",d.status_cd,cit.com_cd,f.trans_cd,g.upd_dt as "Submitted date"
            from item_navigator_tbl o, c_item_tbl cit, case_item c, item_activity d, app_form_tbl e, item_tbl f, comm_tbl g
            where obj_cd in 
			(select /*+ rule*/com_cd from item_tbl b 
			where b.upd_dt between to_date('01/01/2007', 'MM/DD/YYYY') and SYSDATE
            and b.trans_cd in 
			(select /*+ rule*/trans_cd from trans_tbl
            where ins_dt between to_date('01/01/2007', 'MM/DD/YYYY') and SYSDATE))
            and o.obj_cd = cit.com_cd
            and o.obj_cd = g.com_cd
            and o.child_obj_cd = cit.c_item_seq_no
            and o.obj_cd = f.com_cd
            and o.child_obj_cd = f.c_item_seq_no
            and cit.c_item_cd = c.c_item_cd
            and c.c_item_cd = d.c_item_cd
            and c.req_item_cd = e.order_item_id
            and not exists (select distinct 1
                                        from item_activity ia
                                        where ia.c_item_cd = cit.c_item_cd  
                                        and status_cd in ( select 'OP' from dual union 
                                        select 'CL' from dual union
                                        select distinct c_item_status_cd from item_action_tbl ));
										

Want to know if the above query can be optimized. Your suggestion would help me a lot.

Execution Plan
----------------------------------------------------------
   0       SELECT STATEMENT Optimizer Mode=HINT: RULE
   1    0    FILTER
   2    1      TABLE ACCESS BY INDEX ROWID DFLX1.ITEM_ACTIVITY
   3    2        NESTED LOOPS
   4    3          NESTED LOOPS
   5    4            NESTED LOOPS
   6    5              NESTED LOOPS
   7    6                NESTED LOOPS
   8    7                  NESTED LOOPS
   9    8                    NESTED LOOPS
  10    9                      VIEW SYS.VW_TMD
  11   10                        SORT UNIQUE
  12   11                          TABLE ACCESS BY INDEX ROWID DFLX1.ITEM_TBL
  13   12                            NESTED LOOPS
  14   13                              TABLE ACCESS BY INDEX ROWID DFLX1.TRANS_TBL
  15   14                                INDEX RANGE SCAN DFLX1.IR2_TRANS_TBL
  16   13                              INDEX RANGE SCAN DFLX1.id_comm_item
  17    9                      TABLE ACCESS BY INDEX ROWID DFLX1.ITEM_NAVIGATATOR_TBL
  18   17                        INDEX RANGE SCAN DFLX1.XPKITEM_NAVIGATATOR_TBL
  19    8                    TABLE ACCESS BY INDEX ROWID DFLX1.COMM_TBL
  20   19                      INDEX UNIQUE SCAN DFLX1.XPKCOMM_TBL
  21    7                  TABLE ACCESS BY INDEX ROWID DFLX1.ITEM_TBL
  22   21                    INDEX UNIQUE SCAN DFLX1.XPKITEM_TBL
  23    6                INDEX RANGE SCAN DFLX1.XPKC_ITEM_TBL
  24    5              TABLE ACCESS BY INDEX ROWID DFLX1.ITEM_TBL
  25   24                INDEX UNIQUE SCAN DFLX1.XPKITEM_TBL
  26    4            TABLE ACCESS BY INDEX ROWID DFLX1.APP_FORM_TBL
  27   26              INDEX UNIQUE SCAN DFLX1.PKFAX_APP_FORM_TBL
  28    3          INDEX RANGE SCAN DFLX1.XUPD_DT
  29    1      NESTED LOOPS
  30   29        VIEW SYS.VW_TMD
  31   30          SORT UNIQUE
  32   31            UNION-ALL
  33   32              TABLE ACCESS FULL SYS.DUAL
  34   32              TABLE ACCESS FULL SYS.DUAL
  35   32              TABLE ACCESS FULL DFLX1.ITEM_ACTION_TBL
  36   29        INDEX RANGE SCAN DFLX1.XC_ITEM_ID_DTCD
  


Regards,
Oli

[Updated on: Tue, 22 July 2008 06:45]

Report message to a moderator

Re: Better way to handle this query? [message #335508 is a reply to message #335505] Tue, 22 July 2008 06:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Want to know if the above query can be optimized

First remove the hints.
Then gather the statistics on your objects.
In the end, read - OraFAQ Forum Guide, Performance Tuning section
- How to Identify Performance Problem and Bottleneck
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide
and provide the requested and usual information.

Regards
Michel

[Updated on: Tue, 22 July 2008 06:45]

Report message to a moderator

Re: Better way to handle this query? [message #335509 is a reply to message #335505] Tue, 22 July 2008 06:46 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Any reason for using the RULE hint ?
Re: Better way to handle this query? [message #335511 is a reply to message #335508] Tue, 22 July 2008 06:53 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks Michel for providing the links.


Quote:
First remove the hints.


While trying to execute (in TOAD) after removing the hint. I am getting the below error:
Quote:

ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "SYS.VW_TMD"."$nso_col_1": invalid identifier



Regards
Oli

[Updated on: Tue, 22 July 2008 07:11]

Report message to a moderator

Re: Better way to handle this query? [message #335538 is a reply to message #335511] Tue, 22 July 2008 08:45 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member

Quote:

ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "SYS.VW_TMD"."$nso_col_1": invalid identifier


Can anyone please let me know why I am getting this error while trying to execute the above SQL after removing rule hint?
Thanks in advance!

Regards,
Oli

Re: Better way to handle this query? [message #335555 is a reply to message #335505] Tue, 22 July 2008 11:00 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
does below do any better/different?

SELECT c.Case_cd,
       c.c_Item_cd,
       e.Invoice_No,
       e.Location_cd AS "loc_cd",
       d.Status_cd,
       cIt.com_cd,
       f.tRans_cd,
       g.Upd_dt AS "Submitted date"
FROM   c_Item_Tbl cIt,
       Case_Item c,
       Item_Activity d,
       app_Form_Tbl e,
       Item_Tbl f,
       comm_Tbl g
WHERE  obj_cd IN (SELECT com_cd
                  FROM   Item_Tbl b
                  WHERE  b.Upd_dt BETWEEN To_date('01/01/2007','MM/DD/YYYY')
                                          AND SYSDATE
                         AND b.tRans_cd IN (SELECT /*+ rule*/ tRans_cd
                                            FROM   tRans_Tbl
                                            WHERE  Ins_dt BETWEEN To_date('01/01/2007','MM/DD/YYYY')
                                                                  AND SYSDATE))
       AND (f.c_Item_seq_No, f.com_cd, cIt.c_Item_seq_No, g.com_cd, cIt.com_cd) IN
           (SELECT o.Child_obj_cd, o.obj_cd , o.Child_obj_cd, o.obj_cd , o.obj_cd from Item_Navigator_Tbl o)
       AND cIt.c_Item_cd = c.c_Item_cd
       AND c.c_Item_cd = d.c_Item_cd
       AND c.req_Item_cd = e.Order_Item_Id
       AND NOT EXISTS (SELECT DISTINCT 1
                       FROM   Item_Activity ia
                       WHERE  ia.c_Item_cd = cIt.c_Item_cd
                              AND Status_cd IN (SELECT 'OP'
                                                FROM   Dual
                                                UNION 
                                                SELECT 'CL'
                                                FROM   Dual
                                                UNION 
                                                SELECT DISTINCT c_Item_Status_cd
                                                FROM   Item_Action_Tbl));

Previous Topic: optimize queries
Next Topic: Slow query with customer data
Goto Forum:
  


Current Time: Thu Dec 08 20:07:42 CST 2016

Total time taken to generate the page: 0.14937 seconds