Creating Secondary Index in Z's Customer Table

[Former Member]

Hello All,

I have created Customer Table and then added secondary index for improving fetching ( in QA),

after I created the Index I Used SE14 for Adjusting and activating the Index

and when i'm selecting with all the columns of the Index it still uses the primary (~000) Index instead of the secondary one.

I'm using Oracle DB.

What can I do In order to use the secondary index ?

Is DB20 will Solve the Problem?

Thanks,

Jenifer.

[FredericGirod]

Hi,

make a SQL trace on your report using the transaction ST05.

In the report of this transaction you could ask the analyser why SAP used an index instead of another.

Most of the times, the error is to forget the client (MANDT) field in the index.

regards

Fred

[Former Member]

Hi Fred,

thank you for your quick answer,

I've tried ST05, and I have the MANDT field in the Index,

After I ran DB20 , I saw in the ST05 that my secondary index was selected but I haven't saw in any forum that after creating Secondary Index we should run DB20 .

We don't have analyser in our company so I have to solve it by myself.

best regards,

Jenifer

[Former Member]

Hi,

Can you show me the screen shot of secondary index which you created?

[Former Member]

i have attached the screen shot of the secondary index

best regards,

jenifer

[hiriyappa_myageri]

This message was moderated.

[Former Member]

Hi Hiriyappa,

i don't understand why only after I used DB20 my index started working without any HINT ?

Is it the right way for adding Secondary INDEX ? run after you add an index db20 and create new stat. ?

regards,

jenifer

[Former Member]

Hi Jennifer

yes , you need to create the Statistics via DB20 before the CBO actually would start using the index in the explain plan.

If the statistics for any index is stale or incorrect  - then it will get excluded during query executions.

Regards

Rishi

[hiriyappa_myageri]

Hi Jenifer,

DB20 will updates statistics for leading columns of the indexes.

DB20 will work for All Database systems.

This will update the statistics to latest in your system.

Regards,

Hiriyappa

[Former Member]

Is your where clause include fields in order of your secondary index?

i.e.  Query like below does not use your index even if it contains all fields of your sec. index.

You should take care of order of fields at where clause.

SELECT ... WHERE monat = 'A' AND

                               cell_num= 'blabla'  AND

                               gjahr = 'XXXX' .

[DirkAltmann]

Hi Jenifer,

does your where clause match with your secondary index? Can you please post your SQL statement for the table?

Thanks,

Dirk

[Former Member]

Hi all,

My primary key fields are :

MANDT            type mandt

FILE_NAME     type char18

CELL_NUM      type Char12

SEQNR            type numc6

CALL_TYPE     type char2

MONAT            type monat

GJAHR             type gjahr

and the selection from table is :

select *

from /mbtk/bil_callin

into table outtab

where cell_num = iv_cellnum

and monat        = iv_monat

and gjahr          = iv_gjahr.

and in the st05, it says found 4 of 4 columns.

[Former Member]

Looking in trace,

the fetch is using /mbtk/bil_callin~0 and not /mbtk/bil_callin~bil

only after running db20 it start using /mbtk/bil_callin~bil.

thanks,

jenifer

[ThomasZloch]

DB20 is used for building the statistics of the data distribution inside the table, which is very important for the DB optimizer to be able to determine the correct (= least costly) access path.

As you have seen, after building the DB statistics the correct index is being picked, so is there actualy an issue left?

DB20 and related stuff should be done by the system administrator.

Thomas

[Former Member]

thank you,

I think we solved the issue using DB20

Thanks you all

Jenifer

[raymond_giuseppi]

That is standard behavior of an Oracle database today, read some document like 10 Optimizer Statistics Concepts in online Oracle help. There are also some OSS notes like 588668 - FAQ: Database statistics.

Regards,

Raymond