Why Indexes are not used and
performance checks:
Is
the table Indexed?
Check
that the table which you believe should be accessed via an index actually has
indexes defined on it. The indexes could have been dropped or could have failed
to create
-
For example, it is possible, after importing tables, or performing loads, that
indexes were not created due to errors (either software or human).
SELECT
index_name FROM user_indexes WHERE table_name = &Table_Name;
Why SHOULD the index be used?
Oracle
does not have to use an index simply because an index exists.
If
a query requires every row in the table to be queried (in table joins, for
example),
then
why query all the rows in the index AND all the rows in the table?
Ignoring
the index in this case results in better performance.
The
optimizer makes decisions based on statistics regarding the suitability of
various access methods,
including
indexes, for all queries and chooses the best one.
Are the indexed columns/leading column of
the index supplied in the where clause of the query (predicate list) as a
single table (non-join) predicate?
If
not, then remember that at least the leading column of an index is required in
the predicate
list
to use an index in a query (but see Skip Scan below.)
Example:
You have defined index EMPNO_I1 on single column EMP.EMPNO, and defined
concatenated index
EMPNO_DEPT_I2
on columns EMP.EMPNO and EMP.DEPT (EMP.EMPNO is leading column).
In
order for the optimizer to consider either index, you must use the column
EMP.EMPNO in the predicate list (WHERE clause)
Exceptions:
ü CBO can use an Index
Skip Scan (INDEX_SS). The leading column of an index is not required for an
INDEX_SS to be performed.
ü CBO can choose to use
an index to avoid sorting. The indexed columns would need to be in the order by
clause for this to happen
ü CBO can use a Index
Fast Full Scan (INDEX_FFS) as long as the index contains all the columns that
are needed for the query,and at least one column in the index key has the NOT
NULL constraint. The leading column of an index is not required for an
INDEX_FFS to be performed. Note that the use of an INDEX_FFS does not
necessarily return the rows in sorted order.Ordering is dependent on the order
that the index blocks are read and rows are only guaranteed to be returned in a
sorted order if an 'order by' clause is used
If the indexed columns ARE part of join
predicates, then: What type of join method is used to implement the join when
the query executes?
For
example, the following join predicate defines the join between the emp and dept
tables on the deptno column emp.deptno = dept.deptno
Hash / Sort Merge
Join:
With Hash joins and Sort Merge joins, information from the outer table is not
available at join time to enable row look ups on the inner table; rather both
tables are accessed separately and then the resultant data is joined. The inner
table of a Hash or Sort Merge cannot be probed solely using an index based on
the join columns . This is an inherent limitation of the implementation mechanisms
used by these join types.
Nested
Loops joins are different in as much as they allow index lookups on the join
columns.
Nested Loops Join:
Nested loop
joins work by reading the outer table and then using the information gathered to
probe the inner table. This algorithm allows index lookups to occur on the
inner table.
Is implicit type
conversion going on?
If
the datatypes of two values being compared are different, then Oracle has to
implement type conversion on one of the values to enable comparisons to be
made. This is called implicit type conversion. Typically this causes problems
when developers store numbers in character columns.
At
runtime oracle is forced to convert one of the values and (due to fixed rules)
places a
to_number
around the indexed character column. Adding any function to an indexed column
prevents use of the index. The fact that Oracle has to do this type conversion
is an indication of a design problem with the application.
Is it semantically
impossible to use an index?
Because
of cost considerations on the query as a whole, a plan may have been chosen
that means that the use of an index at a lower level is now not possible. The
index may have been considered in other join orders/methods but the method with
the lowest cost makes the index unusable.
Because of the way the query has been executed (i.e. join
orders/methods) it is now 'semantically impossible' to use an index.
PRODUCTION issue and
its resolution.
One
of the critical heavly used business program encountered performance issue in
Production. The program was executing couple of SQL’s.We put the program in
trace mode and checked the explain plan for offending SQL.
As
the program was running, it showed a SQL that was just looping and could not
give much inputs why it was getting in loop. The I/O reads for this session was
quickly growing to million records and so.
The
explain looked good with very less cost and other parameters. But there were
couple of full table scans and few full table scans were not avoidable. Their
were many SQL executed by the program so the complexity became more We noticed that one full table scan was
avoidable but the optimizer was not taking the Index..
Datatype
was character type and predicate value was given numeric. Therefore, Optimizer
was ignoring index
Advised
to development to review the code,once they changed the code and this was mode
to PROD.
The
program came back to its life and completing as expected.
Due
to data security of client am not giving the actual SQL of issue faced program.
I
will do a small demo:
SQL>
CREATE table test_jan (enum VARCHAR2(10),ename VARCHAR2(10));
Table
created.
SQL>
BEGIN
FOR
i IN 1..10000
LOOP
INSERT
INTO test_jan VALUES (i,'A');
END
LOOP;
END;
7 /
PL/SQL
procedure successfully completed.
SQL>
commit;
Commit
complete.
SQL>
CREATE INDEX edw_ind1 ON test_jan (enum);
Index
created.
Gather
Stats on this newly created table:
SQL>
exec dbms_stats.gather_table_stats(ownname => 'APPS', tabname =>
'test_jan', cascade => true);
PL/SQL
procedure successfully completed.
SQL>
SQL>
explain plan for
2
select * from test_jan where enum>500;
Explained.
SQL>
@$ORACLE_HOME/rdbms/admin/utlxpls;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------------------------------
|
Id | Operation | Name | Rows
| Bytes | Cost (%CPU)|
-------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 9500 | 129K|
9 (23)|
| 1 | TABLE ACCESS FULL| TEST_JAN | 9500 |
129K| 9 (23)|
Here
we can clearly see that though there is index on column enum,the optimizer
ignored index.
SQL>
explain plan for
2
select * from test_jan where enum>to_char('500')
3 /
Explained.
SQL>
@$ORACLE_HOME/rdbms/admin/utlxpls;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------------------------
|
Id | Operation | Name | Rows
| Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
500 | 3500 | 6
(0)|
| 1 |
TABLE ACCESS BY INDEX ROWID| TEST_JAN |
500 | 3500 | 6
(0)|
| 2 | INDEX RANGE SCAN | EDW_IND1 | 90 |
| 2 (0)|
-----------------------------------------------------------------------------
Now
we can see the index is used!!!
-Edwin.K
