Secondary Index Select Statement Problem

[Former Member]

Hi friends.

I have a issue with a select statement using secondary index,

SELECT SINGLE * FROM VEKP WHERE VEGR4 EQ STAGE_DOCK

AND VEGR5 NE SPACE

AND WERKS EQ PLANT

%_HINTS ORACLE

'INDEX("&TABLE&" "VEKP~Z3" "VEKP^Z3" "VEKP_____Z3")'.

given above statement is taking long time for processing.

when i check for the same secondary index in vekp table i couldn't see any DB index name with vekp~z3 or vekp^z3 or vekp____z3.

And the sy-subrc value after select statement is 4. (even though values avaliable in VEKP with given where condition values)

My question is why my select statement is taking long time and sy-subrc is 4?

what happens if a secnodary index given in select statement, which is not avaliable in that DB Table?

[ThomasZloch]

The syntax looks strange to me, if index Z3 of table VEKP should be forced, then for Oracle it would look like

%_HINTS ORACLE 'INDEX("VEKP" "VEKP~Z3")'.

We have no idea if your Z3 index exists, how it looks like, how much data is in VEKP, how distinctive the fields in your where-condition are, so it is not possible to answer your question.

If the index does not exist, then the hint is being ignored and the best index is chosen by the CBO, as far as I know. You can verify this yourself by doing a SQL-trace ST05.

Thomas

[Former Member]

Hi Thomas.

Thanks for spot reply, the DB tablex doesn't have index called 'Z3'.

when i use the same where condition in DB table to find the records its giving 306 records.

ONe more question: is it possible to give more than one index name in select statement.

[HermannGahm]

Hi,

> ONe more question: is it possible to give more than one index name in select statement.

yes you can:

read the documentation:

http://download.oracle.com/docs/cd/A97630_01/server.920/a96533/hintsref.htm#5156

index_hint:

This hint can optionally specify one or more indexes:

- If this hint specifies a single available index, then the optimizer performs 
a scan on this index.  The optimizer does not consider a full table scan or 
a scan on another index on the table. 

- If this hint specifies a list of available indexes, then the optimizer 
considers the cost of a scan on each index in the list and then performs 
the index scan with the lowest cost. The optimizer can also choose to 
scan multiple indexes from this list and merge the results, if such an 
access path has the lowest cost. The optimizer does not consider a full 
table scan or a scan on an index not listed in the hint.
 
- If this hint specifies no indexes, then the optimizer considers the 
cost of a scan on each available index on the table and then performs 
the index scan with the lowest cost. The optimizer can also choose to 
scan multiple indexes and merge the results, if such an access path 
has the lowest cost. The optimizer does not consider a full table scan. 

Kind regards,

Hermann

[HermannGahm]

Hi,

read your second post.

you want to transform the oracle hint

SELECT SINGLE * FROM VEKP WHERE VEGR4 EQ STAGE_DOCK

AND VEGR5 NE SPACE

AND WERKS EQ PLANT

%_HINTS ORACLE

'INDEX("&TABLE&" "VEKP~Z3" "VEKP^Z3" "VEKP_____Z3")'.

to a DB6 DB.

Read SAP Note: 868888.

The corresponding hint would be:

SELECT SINGLE * FROM VEKP WHERE VEGR4 EQ STAGE_DOCK

AND VEGR5 NE SPACE

AND WERKS EQ PLANT

%_HINTS DB6 'IXSCAN TABLE = "VEKP" INDEX = ''"VEKP~Z3"'' />'.

Kind regards,

Hermann