Re: Best way to tune the below query.

James Williams wrote in message <3a55abea.83287056_at_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%'

You need to make JS_LOCATION the driving table. ( IT_ORDER is curently the driving table.)

To do this, move JS_LOCATION from the beginning to the end of the from clause, and change the where condition:

    IT_ORDER.LOCATION_ID = JS_LOCATION.LOCATION_ID to

    IT_ORDER.LOCATION_ID = JS_LOCATION.LOCATION_ID + 0 ( assuming LOCATION_ID is numeric ).

You'll need an index on IT_ORDER.LOCATION_ID (you've probably got one).

Hope this helps. Let me know how it works (send the revised plan and statistics).

Dave.

--
If you reply to this newsgroup posting by email, remove the "nospam"
from my email address first.