Skip to Content
0

Selecting from SAP table acoording to Index

Oct 19, 2016 at 06:16 PM

161

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Fidel Vales
Oct 19, 2016 at 07:59 PM
0

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.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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'.

0
Fidel Vales
Oct 20, 2016 at 06:58 AM
0

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)

Share
10 |10000 characters needed characters left characters exceeded
Kay Kanekowski Oct 20, 2016 at 07:31 AM
0

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


bckqj.png (46.7 kB)
Share
10 |10000 characters needed characters left characters exceeded
Kay Kanekowski Oct 20, 2016 at 07:44 AM
0

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.


moiz9.png (41.3 kB)
Share
10 |10000 characters needed characters left characters exceeded
Fidel Vales
Oct 20, 2016 at 01:50 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded