Re: SELECT Hints

In article <852s2o$b85$1_at_nnrp1.deja.com>,   gbergerpios_at_my-deja.com wrote:
> 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
>

Are you running rule or cost based? If cost when was the last time you updated your statistics and how large was your sample size?

Note - All hints except the RULE hint will cause the SQL to run cost based even if the init.ora sets the database to RULE and statistics do not exist on the tables.

If you are running rule based the OR will prevent the use of an index. The optimizer should transfor the SQL into the union of an indexed query for each value. The CBO should probably behave the same way in this instance, but bad statistics would affect it. --
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Jan 07 2000 - 12:30:21 CST