Re: newbue question: understanding indexes

Hello Vince,

you allready got two answers to your question, but both of them explain the topic from the "Oracle view". But because of the fact that you ask in a R/3 group I will try to explain how to do it with SAP tools.

Both answers you got so far told you to use explain plan. But if you are in a SAP environment, the CCMS will help you to do the job. Therfore you start transaction ST04 and go on into "Detailed Analysis". There you will find a button SQL Statements and Oracle Sessions. If the report was executed in the past, than you will find the statement inside the SQL Statements. In the SQL Statements dialog you could restrict the number of statements the system will display for you. Because of the release dependency of the restrictions you could make, I couldn't tell you exactly what you will see and what you should do. But with some experimentation you will find your statement in a list of statements. If you choos the statement, you could press the explain button to get the result of the explain plan for your statement. The plan will be shown up as a tree which visualize the execution plan of your statement. If you see an index "GLPCA~*" in the plan, the index is used to execute your query. If you only find the GLPCA and nothing else, the database did not use an index.

If the statement is currently running, you could also explain the execution plan for it in the Oracle Sessions.

There is another possible way to get the execution plan. With the Transaction ST05 you could trace your report. It is possible to trace only one transaction for a single user or even a single program of a single user. When you start the trace - run your report - stop the trace. You could list the trace. In the list you will find your statement and you could again explain that statement with the explain button.

Regards
Ralph

"Vince Laurent" <vlaurent_at_NOSPAM.networkusa.net> schrieb im Newsbeitrag news:0bmbiu4ov0vus0edrbk88qqrmlvao3r260_at_4ax.com...
> We have a report that uses a table (GLPCA) and I was asked to see if
> it used a certain field (BUKR) as a field. Well, the table has 6
> indexes: GLPCA~0 and so on...
>
> I ran the following SQL script:
> SQL> select index_name, column_name
> 2 from dba_ind_columns
> 3 where index_owner = 'SAPR3' and
> 4 index_name like '%GLPCA%'
> 5 order by index_name, column_position;
>
> And got back
>
> INDEX_NAME COLUMN_NAME
> ------------ ---------------
> GLPCA~0 RCLNT
> GLPCA~0 GL_SIRID
>
> GLPCA~1 KOKRS
> GLPCA~1 RYEAR
> GLPCA~1 RPRCTR
> GLPCA~1 RVERS
> GLPCA~1 RACCT
>
> GLPCA~2 DOCNR
> GLPCA~2 RYEAR
> GLPCA~2 DOCCT
> GLPCA~2 RBUKRS
> GLPCA~2 RLDNR
> GLPCA~2 RCLNT
> GLPCA~2 DOCLN
>
> GLPCA~3 REFDOCNR
> GLPCA~3 REFRYEAR
> GLPCA~3 REFDOCCT
> GLPCA~3 RBUKRS
> GLPCA~3 RLDNR
> GLPCA~3 REFDOCLN
> GLPCA~3 RCLNT
>
> GLPCA~7 RHOART
> GLPCA~7 KOKRS
> GLPCA~7 RYEAR
> GLPCA~7 ACTIV
> GLPCA~7 POPER
>
> As you can see, the field appears in an index
> GLPCA~2 RBUKRS
>
> But the index also has many other fields. So how can I answer the
> question: "Can you tell if GLPCA is index by BUKR?" Apparently there
> is a report that uses that field a lot.
>
> How does a table use an index that has more than 1 key in it?
>
> Thanks!
>
Received on Sat Jul 06 2002 - 05:03:18 CDT