Database statistics & Hints

[Vinod_Chandran]

Hi,

I have few question regarding Database statistics & Hints.

1. I understand that after creating a database index, the database statistics for that table must be updated. Otherwise the optimiser will not detect the index. Can I overcome this using the database hints?

2. As far as I am aware of we are safe to create index for all transparant tables. Are there any restictions?

How about table VEPO?

The syntax I have used for hints is

%_HINTS ORACLE 'INDEX("VEPO~Z1")'

Hope this is correct.

Thanks in advance

Vinod

[jayesh_vorani1]

Hello Vinod,

Hints should be the last alternative only if land yourself in a vierd situation where you need a specific index to be used and for some reasons (inspite of your table and index stats are up to date) Oracle chooses not to use the right one. (This should not happen ideally as Oracle optimizer is smart enough to pick up the right index if stats are updated).

Bottom line, DO NOT use HINTS just to get away from updating your stats. Certainly HINTS clause was never designed keeping this in mind !

Cheers,

Jay

[Vinod_Chandran]

Hi Guys,

Thanks for all the answers.

It seems SAP does not recommand update statistics on frequently changed tables and I think VEPO is of that kind. So the only(?) option here is to use hints. Any other alternatives?

Also if I use hints, then the select must bypass the optimiser and choose the index specified in the SELECT statement. Am I right?

Thanks

Vinod

[manuel_bassani]

hi Vinod,

could you kindly tell me where you found those informations?

I don't understand why SAP should not recommend to update statistics on those tables.

As far as I know the Hints are deprecated, not the statistics update...

Regards, Manuel

[Former Member]

Hello,

Even if you put hints and say do not specify the fields. For eg. you have an P1 on VEPO and IEPO and you put the statement

select *....

where vepo eq V_VEPO

hints oracle "'p1'".

This is not going to work. You will have to use all the fields in the i.e. the field IEPO must also be used and in that event you do not need hints oracle.

Regards,

SHekhar KUlkarni

[Former Member]

Hi,

For certain tables, the standard mechanisms of creating statistics are not optimal. Possible reasons are mentioned in SAP note 122718. However by modifying the table DBSTATC you can change the default setting and create statistics for VEPO table

Regards

CS

[Vinod_Chandran]

Hi Guys,

My problem is,

There is an index called Z1 in VEPO with fields VBELN and POSNR and I am using both the fields in the WHERE clause. Here is my code.

SELECT a_{venum a}vepos a_{vbeln a}matnr a_{vemng b}vhilm b~exidv2

b_{brgew b}gewei_max b~ntgew

b_{btvol b}voleh_max

INTO TABLE i_vepo

FROM vepo AS a INNER JOIN vekp AS b

ON a_{venum = b}venum

FOR ALL ENTRIES IN i_bill

WHERE a~vbeln = i_bill-vbelv

AND a~posnr = i_bill-posnv.

The database statistics for VEPO is not updated and is not taking the index Z1, so the select is taking lot of time.

I am yet to test with the hints. Requested to transport to test system.

Thanks

Vinod

[manuel_bassani]

Hi Vinod,

I'll try to answer your questions:

1. with hints you can force the DB to use a specific index (also without updating the statistics). Consider that SAP suggest to not use hints if possible ! because they are DB dependent. for example: Oracle uses statistics, but others like DB2 (i think) don't .

2. it is possible to create index for alla transparente tables, but the optimizer could have some problems to find the best index if you create too many of them.

I think the syntax is correct, i can suggest you to create an index only if it is really required and it could bring to high performance.

Best regards, Manuel

Message was edited by: Manuel Bassani

[Former Member]

Hello,

Even if you put the hints command you will have to use all the fields on the index as SAP automatically finds the index the hints command only hints ie. request's the sql engine to use the following index. Which is exactly what sap does. So it is suggested not to use the hints. Instead use in the where clause all the fields given in the index.

Regards,

Shekhar Kulkarni