Re: SELECT Hints

Try putting the smallest tables last in the from clause. In our development environment we went from over a minute to under a second by doing this.

Also, look at the query plan for the statement so it is not doing anything stupid.

gbergerpios_at_my-deja.com wrote in message <852s4a$b8l$1_at_nnrp1.deja.com>...
>The following query takes 10 minutes to bring back 14 records:
>
>SELECT whatever
>FROM
>PAT_INVC_HDR_YEAR_V, PAT_CUST_MASTER_T,
>PAT_TAX_CODE_T, PAT_TERMS_CODE_T, PAT_INVC_DTL_YEAR_V
>WHERE
>(
> PAT_INVC_HDR_YEAR_V.INVC_NO = '350754101' OR
>PAT_INVC_HDR_YEAR_V.INVC_NO = '350826301'
>) AND
>PAT_INVC_HDR_YEAR_V.Process_Month = '200001' AND
>PAT_INVC_HDR_YEAR_V.Invc_No = PAT_INVC_DTL_YEAR_V.Invc_No AND
>PAT_INVC_HDR_YEAR_V.Process_Month = PAT_INVC_DTL_YEAR_V.Process_Month
>AND
>PAT_INVC_HDR_YEAR_V.TAX_CODE = PAT_TAX_CODE_T.TAX_CODE (+) AND
>PAT_INVC_HDR_YEAR_V.TERMS_CODE = PAT_TERMS_CODE_T.TERMS_CODE (+)
>AND
>PAT_INVC_HDR_YEAR_V.BILL_TO_CUST_NO = PAT_CUST_MASTER_T.CUST_NO (+)
>
>
>The PAT_INVC_HDR_YEAR_V view is the MAIN TABLE (view).
> Invc_No & Process_Month are indexed
>
>The PAT_INVC_DTL_YEAR_V view is the detail.
> Invc_No & Process_Month are indexed
>
>The rest of the views are lookup tables.
>All fields listed in the supporting tables are indexed.
>
>It seems that Oracle is using a wrong query plan.
>How can I force a query plan on the above so it takes
>a second or two to return a result instead of 10 minutes.
>
>We have looked up HINTS but do not understand the
>documentation.
>
>We are using Oracle 7.3
>
>Thanks,
>Gene Berger
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Mon Jan 10 2000 - 07:38:49 CST