Selecting from SAP table acoording to Index

[Former Member]

I need to select from sap table TEVEN,

The table has one Key field PDSNR and 2 Indexes. One of the indexes is from the fields : PERNR, LDATE, LTIME.

in my Z program i am selecting from the table like that -

Select * into table lt_teven

from TEVEN

where

PERNR = lv_pernr and LDATE = '20161013' and LTIME is not initial and STOKZ neioned 'X'.

Will this select will use the index i've mentioned before? because of the the extre foled in the select (stokz)

thanks ELAD

[fidel_vales]

Hi,

there are no statistics so anything might happen. probably due to the parameters (and something called dynamic sampling) is taking the index, but as mentioned, it will depend on several things. there an index is being taken which will not be good if you have data on the table (MANDT+PDSNR) you have not specified PDSNR on your query.

[KayKa]

oops, i forget the MANDT column. But now you get a weird plan because the statistics shows that the table is empty, it has 0 rows.

So every index is a good access because the optimizer expects that no row match with the MANDT column.
If your statistics are not accurat and your table has millions of rows then this access plan is bad. And it runs much longer than a simple full table scan.

[KayKa]

Hi Elad,

try transaction sdbe and let it show you the execution plan for this:

Select *   from TEVEN where PERNR =  :A1  and LDATE =  :A2  and LTIME <> :A3  and STOKZ <> :A4

In my system the index is used. But my table teven has no statistics.

regards
Kay

[fidel_vales]

Thanks,

The answer still is the same, the statement sent to the DB would be more or less the same, the first to conditions will be the same and the CBO will decide if an index and what index is taken depending on the statistcs, version, patches, parameters. Probably will take the index, but nobody can tell you with 100% certanty.

For that reason there is a development and a test/quality/preproduction systems, so you can see it Oracle does what you think it should do (and sometimes the CBO is correct and you are wrong and other times the CBO is wrong and you are correct but always test)

[fidel_vales]

hi,

it might use the index. it mainly depends on the table and index statistics, also the oracle parameters and the oracle version and the patches.

I am not an ABAP developer, but probably the DBI will translate your query to something like (ASUMING that there is no MANDT field):

SELECT *

FROM "TEVEN"

WHERE

"PERNR" = :A0 AND

"LDATE" = :A1 AND

"LTIME" <> :A2 AND

"STOKZ" (I have no clue what neioned is so I stop here)

it does not matter that the field STOKZ is not in the index, if the statistics are "good" (that does not mean recent) it is very likely that Oracle uses the index. The first 3 fields as "access" predicates, the third one also as "filter" predicate.

[Former Member]

sorry i ment

Select * into table lt_teven

from TEVEN

where

PERNR = lv_pernr and LDATE = '20161013' and LTIME is not initial and STOKZ ne 'X'.