Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle 9i database index

Re: Oracle 9i database index

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Sat, 07 Jul 2007 06:51:19 -0700
Message-ID: <1183816279.520955.29880@o61g2000hsh.googlegroups.com>


On Jul 7, 8:23 am, KL <klw..._at_swire.com.au> wrote:
> On Jul 7, 9:25 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> > On Jul 7, 5:55 am, KL <klw..._at_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.
>
> But how about if I would like to know if I need an index or not ?

There is no way to answer such a question with testing the effects of adding the index. In very general terms, if you expect less than 15% to 20% of the rows to be returned by a query, an index *might* be helpful. If you expect 75% of the rows to be returned by a query, and index *most likely* will not be helpful. If there are only a couple distinct values in a column, an index *probably* will not be too helpful. It is easy to find exceptions to these guidelines. Keep in mind that every index added to a table will further slow the rate of inserts into the table.

It is important to test performance to determine if a change is helpful or harmful.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sat Jul 07 2007 - 08:51:19 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US