Best way to tune the below query.

From: James Williams <willjamu_at_mindspring.com>
Date: Fri, 05 Jan 2001 11:13:11 GMT
Message-ID: <3a55abea.83287056@nntp.mindspring.com>

Below is the query and the explain plan. I am trying to get this query from 30 seconds down to about 5 seconds.

1 Select

2     it_order.cis_ordr_nbr order_number,
3     to_char(to_date(it_order.appointment_dte,'yyyymmdd'),'mm/dd/yy')
appointment_date_c,
4     to_char(to_date(it_order.appointment_tme, 'hh24miss'),'hh:mi
am') appointment_time_c,
5     to_char(to_date(iv_order_trak.cmpl_dte,'yyyymmdd'),'mm/dd/yy')
completion_date_c,
6     to_char(to_date(iv_order_trak.cmpl_tme, 'hh24miss'),'hh:mi am')
completion_time_c,
7     it_order.ordr_typ_cd,
8     it_order.ordr_stat_cd order_status,
9     ih_cust_tenant.acct_name,
10     js_location.complete_address premise_address,
11     js_location.city,
12     js_location.zip_9,
13     'M' data_source,
14     it_order.appointment_dte,
15     it_order.appointment_tme,
16  /*+ HASH(iv_order_trak) */
17     iv_order_trak.cmpl_dte,
18     iv_order_trak.cmpl_tme
19  From       js_location,ih_cust_tenant,iv_order_trak,it_order
20  WHERE      IT_ORDER.CIS_ACCT_NBR = IH_CUST_TENANT.CIS_ACCT_NBR(+)
and
21     IT_ORDER.LOCATION_ID = JS_LOCATION.LOCATION_ID and
22     ( IT_ORDER.ordr_stat_cd <> 'VOID' or IT_ORDER.CIS_ORDR_NBR like
'M%' ) and
23     ( IT_ORDER.ordr_uid = iv_order_trak.ordr_uid and
24     ( iv_order_trak.cmpl_dte >= to_char(sysdate - 30 , 'yyyymmdd' )
or  iv_order_trak.cmpl_dte is null ) and
25      iv_order_trak.trak_seq_nbr = it_order.trak_seq_nbr )

26* and JS_LOCATION.COMPLETE_ADDRESS like '4112 DUNWOODY%' QL> /

o rows selected

xecution Plan

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS

 2    1     NESTED LOOPS (OUTER)
 3    2       NESTED LOOPS
 4    3         TABLE ACCESS (FULL) OF 'IT_ORDER'
 5    3         TABLE ACCESS (BY INDEX ROWID) OF 'IV_ORDER_TRAK'
 6    5           INDEX (UNIQUE SCAN) OF 'IV_ORDER_TRAK_PK' (UNIQUE)
 7    2       TABLE ACCESS (BY INDEX ROWID) OF 'IH_CUST_TENANT'
 8    7         INDEX (UNIQUE SCAN) OF 'IH_CUST_TENANT_PK' (UNIQUE)
 9    1     TABLE ACCESS (BY INDEX ROWID) OF 'JS_LOCATION'
10    9       INDEX (UNIQUE SCAN) OF 'JS_LOCATION_PK' (UNIQUE)

tatistics

        0  recursive calls
        4  db block gets
   280456  consistent gets
        0  physical reads
        0  redo size
      798  bytes sent via SQL*Net to client
     1632  bytes received via SQL*Net from client
        3  SQL*Net roundtrips to/from client
        1  sorts (memory)
        0  sorts (disk)
        0  rows processed

Received on Fri Jan 05 2001 - 05:13:11 CST