Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Using Hints Oracle

0 Kudos

Hi All,

I have a statement which selects from WB2_V_MKPF_MSEG2

SELECT 
MBLNR MJAHR ZEILE_I BWART_I MATNR_I WERKS_I LGORT_I CHARG_I SHKZG_I MENGE_I MEINS_I UMMAT_I UMWRK_I  UMLGO_I UMCHA_I 
FROM WB2_V_MKPF_MSEG2 INTO TABLE ITAB_MSEG 
FOR ALL ENTRIES IN ITAB_BATCH WHERE
      MATNR_I = ITAB_BATCH-MATNR AND WERKS_I = STRC_T001L-WERKS AND LGORT_I IN S_LGORT1 AND
      BWART_I IN ('311', '312') AND CHARG_I = ITAB_BATCH-CHARG AND BUDAT IN R_BUDAT  %_HINTS ORACLE
      '&SUBSTITUTE VALUES&' ORACLE '&max_blocking_factor 20&'.

When I observed the trace, I found 2 different execution plans (I had executed the report multiple times)

First -

1. Index Range Scan MKPF~BUD

2. Table Access by Index Rowid MKPF

3. Index Range Scan MSEG~0

4. Table Access by Index Rowid MSEG

Second -

1. Index Range Scan MSEG~M

Index M contains the following fields - MATNR, WERKS, LGORT, BWART, SOBKZ, CHARG

2.Table Access by Index Rowid MSEG

3. Index Range Scan MKPF~BUD

4. Table Access by Index Rowid MKPF

The report running with second execution plan takes less time when compared to first. Though Optimizer is selecting a execution plan which goes through Index in both cases it is unable to select the Best Optimal Plan in all the cases ie Second.

I tried to use Hints,

ORACLE 'INDEX("MSEG" "MSEG~M" "MSEG^M")'

but the optimizer is not considering, still I get the First Execution Plan.

I could observe that Hints are working when I use a single table ie either MKPF or MSEG. But when I use View it is not working.

Is it possible to influence Optimizer to consider first MSEGM and then MKPFBUD using Hints

Can any one help me on this.

Regards,

Vipin.

4 REPLIES 4

former_member192616
Active Contributor
0 Kudos

Hi Vipin,

yes, it is possible to use hints for a view.

instead of the table name you have to use VIEWNAME.TABLEALIAS.

The table alias can be found in the view definition.

The view definition can be found in SE14 (object log)

or at db level "SELECT TEXT FROM DBA_VIEWS WHERE VIEW_NAME = '<view_name>';"

Forcing index usage would then look like this:

INDEX(WB2_V_MKPF_MSEG2 .T1 "MSEG~M" "MSEG^M")

if MSEG has alias T1.

back to your question:

>

> Is it possible to influence Optimizer to consider first MSEGM and then MKPFBUD using Hints

>

this requires much more hints... e.g. LEADING (to specify the starting table) USE_NL (to specify the join mechanism)

and the INDEX hints like the one above...

good luck

Kind regards,

Hermann

0 Kudos

Dear Hermann,

Thanks for your response. Now I am able to change MSEG0 to MSEGM using

INDEX(WB2_V_MKPF_MSEG2.T2 "MSEG~M" "MSEG^M").

Can you tell the Syntax for LEADING Hint and USE_NL Hint to influence the order of Selection ie first MSEGM and then MKPFBUD.

And I have one more doubt, when I am going through Oracle Performance Tuning Guide 10g for hints, I found this


Oracle does not encourage the use of hints inside or on views (or subqueries). This
is because you can define views in one context and use them in another. Also, such
hints can result in unexpected execution plans. In particular, hints inside views or
on views are handled differently, depending on whether the view is mergeable into
the top-level query.

I think for my problem, Hints will solve. Can you please give me your advice on this.

Thanks,

Vipin

0 Kudos

Hi,

> Can you tell the Syntax for LEADING Hint and USE_NL Hint to influence the order of Selection ie first MSEGM and then MKPFBUD.

sure, this hint:

%_HINTS ORACLE 'LEADING(T2)'

ORACLE 'USE_NL(T1)'.

will start with MSEG - T2 (alias from view) and use nested loop to access MKPF - T1 (alias from view)

> And I have one more doubt, when I am going through Oracle Performance Tuning Guide 10g for hints, I found this

>

>


> Oracle does not encourage the use of hints inside or on views (or subqueries). This
> is because you can define views in one context and use them in another. Also, such
> hints can result in unexpected execution plans. In particular, hints inside views or
> on views are handled differently, depending on whether the view is mergeable into
> the top-level query.
> 

>

> Can you please give me your advice on this.

hm i think this means that you should not hint IN the view itself because it may be used

from many different call positions.

you didn't hint in the view, you hinted your query that access the view.

Another query without hint may use a different execution plan.

Kind regards,

Hermann

former_member210621
Participant
0 Kudos

Hi Vipin,

Try this when you enter hint in the box.

INDEX ("MSEG" "MSEG~M")

Regards,

Ketan