Index on table.

Former Member · ‎05-19-2005

Hi,

I have made a new index on MSEG table.

Z09 --> including MANDT + MATNR + WERKS + BWART

I can se in ST05 that it do not hit my index....

I have done some investigation and run this in SQL ANALYSER (SQL SERVER 2000) (modifying it a little bit...)

TEST nr 1 will give me another index.

TEST nr 2 gives me Z09. (Which is the right one.)

SAP sends nr 1 to SQL SERVER.

Any pointers ?

1 .SELECT "MBLNR" ,"MJAHR" ,"BWART" ,"MATNR" ,"WERKS" ,"LGORT" FROM u01.MSEG WHERE "MANDT" = '100' AND "MATNR" = '000000000000117721' AND "WERKS" = '3100' AND "BWART" = '101'

2. SELECT "MBLNR" ,"MJAHR" ,"BWART" ,"MATNR" ,"WERKS" ,"LGORT" FROM u01.MSEG WHERE "MANDT" = 100 AND "MATNR" = '000000000000117721' AND "WERKS" = '3100' AND "BWART" = '101'

//Martin

christian_wohlfahrt · ‎05-19-2005

Hi Martin,

is the only difference between select 1 and 2: mandt = 100 / '100'?

Looks strange.

But how about running the statistics analysis -> cost based optimizer will like your index Z09 much more. Use transaction DB20.

Regards,

Christian

Former Member · ‎05-19-2005

Hi Martin,

I don't think that an index will always be used no matter what. there was some discussion on databases and indexes sometime back in this forum. You might want to refer to that.

Also think about using hints in your SQL stateent, to specify the index which you would like the database table to use. (even though it is not guaranteed that the index specification will be considered by the system).

Regards,

Anand Mandalika.

Regards,

Anand Mandalika.

Former Member · ‎05-19-2005

Hi,

I've read some of the threads concerning index stuff...

It seems a bit confusion...

I played around with it inside Query Analyser.

Look at this:

33.SELECT "MBLNR" ,"MJAHR" ,"BWART" FROM u01.MSEG

WHERE "MANDT" = 100

AND "RSNUM" = '0000000000'

34.SELECT "MBLNR" ,"MJAHR" FROM u01.MSEG

WHERE "MANDT" = 100

AND "RSNUM" = '0000000000'

Test nr 33 gives me one index in the execution plan and test nr 34 gives med another. Just selecting one field more.

INDEX FROM SQLSERVER/SAP: [MSEG~R] ON [u01].[MSEG]([MANDT], [RSNUM]) ON [PRIMARY]

It should always give me index R. But it doesn't...strange.

Pointers ?

(Problem with MSEG is that it has 4 million rows.)

//Martin

Former Member · ‎05-19-2005

Hi Martin,

The amount of information that I may be able to give you on this one is certainly limited. But I can tell you what I know.

What you are talking about is a rule-based optimizer. In that case, we can always say what the access plan the database system generates for fetching the data would like. Because, just what the database would do is merely follow a set of rules and determine the best way of fetching the data. this approach was found to not give satisfactory results always.

So for a few years now, most database servers have begun to use what is known as a cost-based optimizer. In this case, the hard and fast rules are no longer there. The determination of the access plan and index usage is therefore much more dynamic and unpredictable.

Your questions require the understanding o some of the important and modern concepts on Relational Database Systems, rather than SAP. Google has given me a lot of hits on this one, and I don't want to paste them here. You can do the same for a better picture of what I'm talking about.

Regards,

Anand Mandalika.

Former Member · ‎05-20-2005

Hi Martin,

Your results are indeed strange.

Looks like SQL Server is not really handling the selection of indexes very well.

Not sure there is much to be done from an SAP point of view. Perhaps digging into the SQL Server manual will shed some more light.

Or perhaps MSDN?

Brad

Former Member · ‎05-20-2005

Hi,

Just to confirm.....

If I write a OPEN SQL statement inside abap.

Do a trace inside st05.

Looks at the result and there I can see what index has been used.

Now to Question:

So SAP has NOTHING to do with what indexes the db are using, executing that OPEN SQL statement ?

//Martin

christian_wohlfahrt · ‎05-20-2005

Hi Martin,

if I have just the option for yes or no: yes.

But... you can influence (a little bit) index use. E.g. your index is very similar to standard index M: MANDT MATNR WERKS LGORT BWART -> because of this hugh overlap query optimizer can struggle; recommendation is to have as less common fields as possible.

Maybe it's better to add all possible LGORT (normally just 2-3) and use this index -> you can skip Z09, less memory, less maintenance costs.

You have to be sure, all indizes are created in database: not open SQL, but still initiated in SAP.

In case database uses cost based optimizier, periodically statistic updates have to run; again a setting in SAP.

By using special 'wrong' where conditions (LGORT <> space), you can prohibite use of index M - but you can't force a special index with open SQL.

Of course it's possible to start with native SQL and database hints, but that's a lot of work a very individual tuning. Because it's statement specific, it should be the last point to try out.

Regards,

Christian

Former Member · ‎05-20-2005

Hi Christian!

Trying your advice concerning hiting all keys in index = M

(inside SQL SERVER)

Look at this:

select [MANDT], [MATNR], [WERKS], [LGORT], [BWART], [SOBKZ]

FROM u01.MSEG

WHERE MANDT = '100'

AND MATNR = '000000000000117721'

AND WERKS <> '0'

AND LGORT <> '0'

AND BWART = '101'

AND SOBKZ <> '0'

Above hits index = M

And know look at this:

select [MANDT], [MATNR], [WERKS], [LGORT], [BWART], [LINE_DEPTH]

FROM u01.MSEG

WHERE MANDT = '100'

AND MATNR = '000000000000117721'

AND WERKS <> '0'

AND LGORT <> '0'

AND BWART = '101'

AND SOBKZ <> '0'

*This hits index = 0 (which is the PK stuff...)

soooo...just by replacing [SOBKZ] wiht [LINE_DEPTH] it will not hit the right key.

I've done more test and if I enter a fieldnamne in the select statement that are not in the index key...it will not choose the right index here....

This issue (maybe) running out of scope of ABAP/SAP, but I think it's important to share this type of knowledge to other programmer as well.

//Martin

christian_wohlfahrt · ‎05-20-2005

Hi Martin!

I'm not sure, what you are looking at. When you select columns, which are all included in an index, it's possible to fetch values out of the index without reading the table itself (so not using the pointer from index to table).

But this has nothing to do with an execution plan. ALL your accesses will use an index - a full table scan just runs over the primary index. It's always a question, how much index fields are used for direct access of a line (or block).

Your first example in last post can only two valid fields of index M (mandt, matnr) - this results in an index range scan.

Also second example (because of identical where clause) should be executed in the same manner - just that for all entries in this index range, in a second step entries via primary key have to be read, to fetch column line_depth.

So please stay to ST05 for such analysis - here behavior is better understood.

If you don't have mass data in development system and need to change select statement in test or production enviroment, you can use runtime analysis (SE30) -> tips & tricks.

If you have a rule based optimizer (as earlier guessed), then execution plan is independent of database size (only execution time will differ) -> you can test in development system.

Come back with results of ST05 (and who is not longer interested might skip these posts without reading...)

Regards,

Christian

Former Member · ‎05-20-2005

Hi Christian!

I'll stay in ST05 for now on.

SELECT WAERS

FROM MSEG

into corresponding fields of table x_tab

WHERE

MATNR = '000000000000117721'

AND WERKS <> '0'

AND LGORT <> '0'

AND BWART = '101'

AND SOBKZ <> '0'

Above statement is a perfect match for INDEX = M right ?

Inside ST05 Mark the OPEN line and then click on Explain.

Result:

SELECT "WAERS" AS c FROM "MSEG" WHERE "MANDT" = '100' AND "MATNR" = '000000000000117721' AND "WERKS" <> '0' AND "LGORT" <> '0' AND "BWART" = '101' AND "SOBKZ" <> '0' /* R3:ZINDEXTEST:92 T:MSEG */ /*Y3A00E0005CF5K92644ZINDEXTESTi6o1ns -- no cursor, upto 0, conn. 0:2*/

SELECT

|--Clustered Index Seek(OBJECT:([U01].[u01].[MSEG].[MSEG~0]), SEEK:([MSEG].[MANDT]='100'), WHERE:(((([MSEG].[BWART]='101' AND [MSEG].[MATNR]='000000000000117721') AND ([MSEG].[LGORT]<'0' OR [MSEG].[LGORT]>'0')) AND ([MSEG].[SOBKZ]<'0' OR [MSEG].[SOBKZ]>'0')) AND ([MSEG].[WERKS]<'0' OR [MSEG].[WERKS]>'0')) ORDERED FORWARD)

Hmm... what I can see is that it's using the primary index (MSEG~0) to look up these values for me.

Should it be using MSEG~M instead ?

(I want to be able to confirm what indexes is used for my Query.)

//Martin

christian_wohlfahrt · ‎05-20-2005

no, no - not perfect! With <> in a where clause, you prohibit using this field for a index use. Nevertheless, there are query optimizers, which use at least MANDT MATNR for index M. Apparently all these <> confused statement translation so much ( < 0 or > 0...), that no index was used any longer (except primary key for MANDT).

Simplify your test (if you like to use ~M, use only EQ in where).

I hope, with where MATNR WERKS BWART and SOBKZ <> you will get ~09 instead of ~M.

(Somehow I have seen more sophisticated databases.)

Former Member · ‎05-20-2005

Hi Christian!

I run this test and it seems to me that it used primary index MSEG~0 in order to look up items.

It should be a perfect match for MSEG~M right ?

SELECT WAERS

FROM MSEG

into corresponding fields of table x_tab

WHERE

MATNR EQ '000000000000117721'

AND WERKS EQ '3100'

AND LGORT EQ '1000'

AND BWART EQ '101'

AND SOBKZ EQ space

.

SELECT "WAERS" AS c FROM "MSEG" WHERE "MANDT" = '100' AND "MATNR" = '000000000000117721' AND "WERKS" = '3100' AND "LGORT" = '1000' AND "BWART" = '101' AND "SOBKZ" = '' /* R3:ZINDEXTEST:92 T:MSEG */ /*Y3A00E0005CF5KB0123ZINDEXTESTi6o1ns -- no cursor, upto 0, conn. 0:2*/

SELECT

|--Clustered Index Seek(OBJECT:([U01].[u01].[MSEG].[MSEG~0]), SEEK:([MSEG].[MANDT]='100'), WHERE:(((([MSEG].[BWART]='101' AND [MSEG].[WERKS]='3100') AND [MSEG].[MATNR]='000000000000117721') AND [MSEG].[LGORT]='1000') AND [MSEG].[SOBKZ]='') ORDERED FORWARD)

//Martin

christian_wohlfahrt · ‎05-20-2005

Hi Martin,

yes, this select looks perfect for index~M.

This looks now more like a serious database problem.

- Are all indices created on database level?

- I can't identify your type of optimizer (cost/rule), but still: when was your last statistic update for mseg (DB20)?

- Which database you are using (doesn't look like DB2 or Oracle)?

Christian

Former Member · ‎05-20-2005

Hi Christian!

After changing the the SOBKZ EQ SAPCE to SOBKZ EQ 'K' it sure worked. I got my INDEX M.

BUT my index Z09 works every now and then.. I use the same WHERE clause but do changes in the select statement.

1. Indexes are created at SE11 / indexes...

2. DB20 gives me an error telling me that this database system is not yet supported.

I think I will read a book or something... Maybe I post a example to SQLSERVER Newsgroup.

//Martin

christian_wohlfahrt · ‎05-20-2005

Hi Martin!

Let's define, I have made enough comments about sophisticated databases.

In case of cost based optimizers, selectivity of where clause plays a role: e.g. date

- if you chose a date with few records, an index with date is very likely taken

- if you chose a date with many records, other index might be prefered.

This looks like a random behavior, but is indeed a very clever optimazation (as long as paramerters of weighting function aren't bad defined).

The difference between SOBKZ eq SPACE and eq 'K' might come from this - on the other hand, statistics seem not be supported (at least in SAP).

Differences in select clause should influence amount of data, which is read from database, but shouldn't influence execution strategy. Either this is a new trick I never heard of, or...

Normally I would tune where-clause to get faster access - under real-life conditions amount of data (select-clause) is usually a fixed constant. Here behavior is really confusing: different access with select... I guess, database change is out of scope?

Maybe you provide a link to your posting in the SQLSERVER Newsgroup - I guess, from ABAP side no further help can be given.

Regards,

Christian

VijayasekarK · ‎05-19-2005

Hi Martin ,

Before creating index for any table ....you need do selectivity analysis. The tool which is available in sap is DB05 (Analsysis of Table w.r.t indexed fields).

In the program (DB05) give the table name and index fields which you are going to create ( Same order ) and execute the report.

At the end of execution you will get a report ...where you need to observe certain points....

1. Total no. of records selected should not be less than 10% of the total available records..

2. The no. of records selected should go in increasing order . Example ...

Field 1 -- 100 recs

field 2 -- 200 recs

Field 3 -- 500 recs

If the above 2 criteria satisfies ....the index u are creating will help you dbase access...other wise it will help you.

Thanks and Regards,

Vijay