topic Re: QUESTION ON OPEN SQL STATEMENT. in Application Development and Automation Discussions

QUESTION ON OPEN SQL STATEMENT.

Former Member — Mon, 27 Nov 2006 07:19:02 GMT

whenever we access data from the data base using SELECT STATEMENT then it will be processed by primary index but if i want to use secondary (i have created secondary index field) what will be my syntax in the programming 4 secondary index field?

Re: QUESTION ON OPEN SQL STATEMENT.

Former Member — Mon, 27 Nov 2006 07:20:43 GMT

U have to use all fields of the secondary index in WHERE condition:

So if your index use FIELD1, FIELD2 and FIELD3:

SELECT * FROM <TABLE> WHERE FIELD1 =

AND FIELD2 =

AND FIELD3 =

ENDSELECT.

The sequence of the fields in where condition has to be the same of the index, so here it's not using the index:

SELECT * FROM <TABLE> WHERE FIELD2 =

AND FIELD1 =

AND FIELD3 =

ENDSELECT.

It can partially use the index using only some fields (but in the same sequence):

SELECT * FROM <TABLE> WHERE FIELD1 =

AND FIELD2 =

ENDSELECT.

Max

Re: QUESTION ON OPEN SQL STATEMENT.

Former Member — Mon, 27 Nov 2006 07:22:21 GMT

You can force the sql statement to use a particular index by using %_HINTS parameter.

For eg:

SELECT carrid connid cityfrom
FROM spfli INTO (xcarrid, xconnid, xcityfrom)
WHERE carrid = 'LH ' AND cityfrom = 'FRANKFURT'
%_HINTS ORACLE 'INDEX("SPFLI" "SPFLI~001")'.
WRITE: / xcarrid, xconnid, xcityfrom.
ENDSELECT.

Re: QUESTION ON OPEN SQL STATEMENT.

christian_wohlfahrt — Mon, 27 Nov 2006 14:43:25 GMT

Hi Ramesh!

Maybe your SAP runs on a database with a cost based optimizer. This analysis expected execution times based on different ways using the indices. To be able to work correct, statistics of the field contents are necessary. When you create a new index, no statistic information was gather up to know -> the cost based optimizer can't work correct.

Go to DB20, enter your table and check, when the last statistics where created. You might need to trigger a new creation manually (STRG+F2).

By the way. the order of the fields in the where-clause don't influence the decision, which index will be used. It's only a question, if in other programs the same order will be found and a SQL-statement-buffer will have a hit.

Regards,

Christian

Re: QUESTION ON OPEN SQL STATEMENT.

Former Member — Thu, 08 Feb 2007 18:22:11 GMT

I use the hint command in a select like this:

SELECT mblnr mjahr budat

INTO CORRESPONDING FIELDS OF TABLE t_mov

FROM mkpf

WHERE mjahr IN s_mjahr

AND budat IN s_budat

%_HINTS oracle 'INDEX("MKPF" "MKPF~Z02")'.

But How can i use the %_HINTS oracle command in a join select like this:

SELECT s_{mblnr s}mjahr s~zeile

s_{bwart s}sobkz

s_{matnr s}werks

s_{menge s}meins s~shkzg

s_{bustm s}zustd s~xauto

INTO CORRESPONDING FIELDS OF TABLE t_mov

FROM mkpf AS k JOIN mseg AS s

ON k_{mblnr EQ s}mblnr

AND k_{mjahr EQ s}mjahr

WHERE k~mjahr IN s_mjahr

AND k~budat IN s_budat

AND s~matnr IN so_matnr

AND s~werks EQ p_werks

AND s~lgort IN s_lgort

Message was edited by:

riccardo di mario

Re: QUESTION ON OPEN SQL STATEMENT.

Former Member — Thu, 08 Feb 2007 20:12:52 GMT

Hi Ramesh,

Only issue with the %HINT command is it will run only on an ORACLE DB, not on a IBM database server (DB6) ....

Cheers,

Aditya

Re: QUESTION ON OPEN SQL STATEMENT.

Former Member — Wed, 05 Sep 2007 08:39:58 GMT

if the statistical update is performed no need to go with forceful read of secondary index using the %hint..

First make sure that a secondary index exists.

check the fields in the index

just use the fields in the secondary index in the where clause.

in sql trace analyse you can check whether the seconday index is picked or not..

thanks

Re: QUESTION ON OPEN SQL STATEMENT.

Former Member — Wed, 05 Sep 2007 11:05:12 GMT

Hi Ramesh,

After creating secondary index, if it is unique use as like as primary index.

1.If, for all fields in a unique index (primary index or unique secondary index), WHERE conditions are specified with '=' in the WHERE clause, the database optimizer selects the access strategy index unique scan.

2.For the index unique scan access strategy, the database usually needs to read a maximum of four data blocks (three index blocks and one table block) to access the table record.

3.In the SELECT statement shown above, the table DB is accessed. The fields MANDT and Field form the primary key, and are specified with '=' in the WHERE clause. The database optimizer therefore selects the index unique scan access strategy, and only needs to read four data blocks to find the table record requested.

Regards,

Ameer Baba.