SELECT Hints

From: <gbergerpios_at_my-deja.com>
Date: Thu, 06 Jan 2000 19:58:11 GMT
Message-ID: <852s4a$b8l$1@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 Thu Jan 06 2000 - 13:58:11 CST