01-06-2014 7:06 AM
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.
01-06-2014 7:08 AM
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
01-06-2014 7:12 AM
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
01-06-2014 7:15 AM
Hi,
Can you show me the screen shot of secondary index which you created?
01-06-2014 7:58 AM
i have attached the screen shot of the secondary index
best regards,
jenifer
01-06-2014 11:26 AM
01-06-2014 11:40 AM
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
01-06-2014 11:43 AM
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
01-06-2014 11:56 AM
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
01-06-2014 8:13 AM
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' .
01-06-2014 8:14 AM
Hi Jenifer,
does your where clause match with your secondary index? Can you please post your SQL statement for the table?
Thanks,
Dirk
01-06-2014 8:24 AM
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.
01-06-2014 8:27 AM
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
01-06-2014 11:44 AM
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
01-06-2014 3:14 PM
thank you,
I think we solved the issue using DB20
Thanks you all
Jenifer
01-06-2014 3:23 PM
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