Re: Oracle 9i database index

Giganews Newsgroups
Subject: Re: Oracle 9i database index
Posted by:  Charles Hooper (hooperc20…@yahoo.com)
Date: Sat, 07 Jul 2007

On Jul 7, 5:55 am, KL <klw.…@swire.com.au> wrote:
> Are there anyway to tell/check if the database performance issue
> relates to an inefficient  database index or not ?? We are running 9i.
> Many thanks. KL

You should be able to use DBMS XPlan with a particular SQL statement
to determine if an index is being used, and the actual time required
to execute the SQL statement.  With a hint, you can tell Oracle to not
use that index, or to specifically use a different index.  You would
then use the same DBMS XPlan approach to determine if the execution
performance is better or worse for the particular SQL statement.
Let's assume that the table name is T1 and the index name in question
is T1_I1, and there is a second index on the same table named T1_I2.
Take a look at the following SQL statement:
SELECT
  MY_DATE,
  MY_NUMBER,
  TEMP_WATER_FREEZE
FROM
  T1
WHERE
  MY_DATE BETWEEN TRUNC(SYSDATE-100) AND (SYSDATE-1)
  AND TEMP_WATER_FREEZE=0;

---------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Starts | E-Rows | A-Rows |  A-
Time  | Buffers | Reads    |
---------------------------------------------------------------------------------------------------------
|*  1 |  FILTER             |        |        1 |        |    100 |00:00:00.02
|      459 |    185 |
|*  2 |  TABLE ACCESS BY INDEX ROWID| T1    |        1 |      3 |    100
|00:00:00.02 |      459 |    185 |
|*  3 |    INDEX RANGE SCAN        | T1_I1 |        1 |    120K|    100K|
00:00:00.20 |      184 |    183 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
  1 - filter(TRUNC(SYSDATE@!-100)<=SYSDATE@!-1)
  2 - filter(("MY_DATE"<=SYSDATE@!-1 AND
"MY_DATE">=TRUNC(SYSDATE@!-100)))
  3 - access("TEMP_WATER_FREEZE"=0)

[NOTE: INDEX(T1 T1_I1) hint was used to produce the above as a
demonstration]

If the T1_I1 index is on the TEMP_WATER_FREEZE column, and the T1_I2
index is on the MY_DATE column, the execution performance would
probably not be optimal if the data was accessed by the T1_I1 index.
If the DBMS XPlan showed that the index was being used, you could add
a hint to the SQL statement to see how the performance would be
affected:
SELECT /*+ NO_INDEX(T1 T1_I1) */
  MY_DATE,
  MY_NUMBER,
  TEMP_WATER_FREEZE
FROM
  T1
WHERE
  MY_DATE BETWEEN TRUNC(SYSDATE-100) AND (SYSDATE-1)
  AND TEMP_WATER_FREEZE=0;

-or-

SELECT /*+ INDEX(T1 T1_I2) */
  MY_DATE,
  MY_NUMBER,
  TEMP_WATER_FREEZE
FROM
  T1
WHERE
  MY_DATE BETWEEN TRUNC(SYSDATE-100) AND (SYSDATE-1)
  AND TEMP_WATER_FREEZE=0;

------------------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Starts | E-Rows | A-Rows |  A-
Time  | Buffers |
------------------------------------------------------------------------------------------------
|*  1 |  FILTER             |        |        1 |        |    100 |00:00:00.01
|        5 |
|*  2 |  TABLE ACCESS BY INDEX ROWID| T1    |        1 |      3 |    100
|00:00:00.01 |        5 |
|*  3 |    INDEX RANGE SCAN        | T1_I2 |        1 |      3 |    100 |
00:00:00.01 |        3 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
  1 - filter(TRUNC(SYSDATE@!-100)<=SYSDATE@!-1)
  2 - filter("TEMP_WATER_FREEZE"=0)
  3 - access("MY_DATE">=TRUNC(SYSDATE@!-100) AND
"MY_DATE"<=SYSDATE@!-1)

Keep in mind that the performance of the SQL statement could change
with a change in the WHERE conditions.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

Replies

In response to

Oracle 9i database index posted by KL on Sat, 07 Jul 2007