Re: newbue question: understanding indexes

You have actually brought up a very deep topic, which could be the source of an entire book; therefore, I will limit my comments to the very top level to help satisify your immediate needs.

First of all, a table does not really use an index at all. In reality, Oracle merely uses an index to improve performance of a query; an index and table or two different animals! Oracle decides whether to use an index based on a number of criteria such as your optimizer mode, the presence of table and index statistics, the indexed column appearing in a WHERE clause, and many others. Oracle may or may not use the index. Because of this, in order to determine if the index is being used, a great tool is the EXPLAIN PLAN statement. This illustrates how Oracle retrieved your data by specifying which index(es), if any, were used. There are other utilities that will identify the use of indexes, but EXPLAIN PLAN is very easy to use.

To simplify the use of EXPLAIN PLAN, Oracle Corporation has included to useful scripts: utlxplan.sql and utlxpls.sql. The first script builds a table to store the query execution plan. The second script is used to query the results of an EXPLAIN PLAN statement.

Here's what you do:

• Execute the first script above, the user must have the CREATE TABLE privilege. @ORACLE_HOME/rdbms/admin/utlxplan.sql

EXPLAIN PLAN FOR
SELECT <enter your query here>;

• Execute the second script above @ORACLE_HOME/rdbms/admin/utlxpls.sql

One more thing to think about. If an index consists of multiple columns (which yours does), the leading edge columns (identified by the column_position column in user_ind_columns) must be specified in the WHERE clause.

In other words, I create an index for the PERSONNEL table, using the LAST_NAME, FIRST_NAME, and TEL_NUM columns. If I reference only the TEL_NUM column in a WHERE clause:

SELECT *
FROM personnel
WHERE tel_num = '123-456-7890';

The index will not be used!

One more note, you actually specified two different values. The first paragraph referenced BUKR, while later, you specified RBUKRS. Make sure you are looking at the same columns.

cm

"Vince Laurent" <vlaurent_at_NOSPAM.networkusa.net> wrote in message 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 Fri Jul 05 2002 - 23:42:41 CDT