CODEWITHSUNDEEP
sqloracle12c
milheiros
milheiros

Reputation: 619

Oracle is not using the index

I have a table A with 1 million rows. I have an index for the poliespa column with '03 or '04' values.

CREATE INDEX A21 ON A(POLIESPA);

My query:

     SELECT polinpol,
       policdde,
       polipext,
       polidext,
       DECODE (polipext,  'T', polipdmx,  'A', polipdmx,  polipdix),
       politipo,
       polipdtu,
       DECODE (TO_NUMBER (RTRIM (LTRIM (polivmrd))), 0, 0, polivmrd),
       RTRIM (LTRIM (polipdtx)),
       polifeca
  FROM A
 WHERE poliespa = '03';

The explain plan for this SQL query makes a full table scan:

Plan hash value: 3450718889

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |   369K|    14M|  6615   (3)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| A      |   369K|    14M|  6615   (3)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("POLIESPA"='03')

Blocks - 41735:

SELECT COUNT (
           DISTINCT (SUBSTR (DBMS_ROWID.rowid_to_restricted (ROWID, 1), 1, 8)))
           blocks
  FROM A;

Clustering:

SELECT index_name, clustering_factor
  FROM user_indexes
 WHERE index_name LIKE 'A21%'

INDEX_NAME  CLUSTERING_FACTOR
-----------------
A21         79247

1 row selected.

Can anyone help me to understand why the optimizer doesn't use the index or why this is the best approach? Thanks

EDIT: 

select count(*) from A
where poliespa = '03';

select count(*) from A
where poliespa = '04';

  COUNT(*)
----------
    221379
1 row selected.

  COUNT(*)
----------
    517095
1 row selected.

Upvotes: 2

Views: 8176

Answers (2)

nimdil
nimdil

Reputation: 1381

As a rule of thumb it's not beneficial to use an index if the subset is a significant part of the whole table. For example, something along the lines of 20% (but it depends obviously).

Why?

Think about what happens when you access this data through index - you access blocks with index one-at-a-time (and given ratio it can be as much as whole index) plus blocks of the table. Given how data may be stored on the table (the clustering factor), the index access may end up reading the entire table anyway.

Table scan can be better and faster as you can just sequentially go through all the blocks using multi-block reads instead of accessing data one-at-a-time.

Upvotes: 2

BobC
BobC

Reputation: 4432

The answer is ...

COST

Oracle uses a cost based optimizer. If Oracle thinks the cost of a full table scan is less than the cost of an index lookup, then it will choose a full table scan.

So you provided the explain plan for you query; thats good. Lets go one step further and get runtime diagnostics:

alter session set timed_statistics=ALL;

<run your query>

select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST ) );

Now add a hint to you query to force the use of the index. Run the query again, and get the execution plan for the index plan, using the statement above.

Upvotes: 0

Related Questions