How does Oracle 10g use indexes

oracleoracle-10g

I have table called result:

CREATE TABLE result
    (id_graduate                    NUMBER NOT NULL,
    id_sml                         NUMBER NOT NULL,
    last_update                    DATE NOT NULL,
    id_kt                          NUMBER NOT NULL,
    value_kt                       NUMBER NOT NULL,
    id_res                         NUMBER NOT NULL)
/
ALTER TABLE result
ADD CONSTRAINT pk_id_res PRIMARY KEY (id_res)

And an index:

CREATE UNIQUE INDEX x_pk_id_res ON result
  (
    id_res                          ASC
  )
  STORAGE   (
    INITIAL     65536
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
NOPARALLEL
LOGGING
/

When I run SQL query #1, Oracle uses index X_PK_ID_RES

select ss.*
from V_JOURNAL ss, result r
where TO_NUMBER(ss.ID_SML||ss.ID_KT||ss.ID_GRADUATE)=r.id_res

But, when I run SQL query #2, Oracle does not use index X_PK_ID_RES. Instead it uses an ACCESS FULL TABLE access path.

select ss.*,r.* 
from V_JOURNAL ss, result r
where TO_NUMBER(ss.ID_SML||ss.ID_KT||ss.ID_GRADUATE)=r.id_res

So, I added a new column id_res2:

ALTER TABLE RESULT  ADD (  ID_RES2 NUMBER );

And I created a new index:

CREATE UNIQUE INDEX x_id_res2 ON result
  (
    id_res2                          ASC
  )
  STORAGE   (
    INITIAL     65536
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
NOPARALLEL
LOGGING

When I run SQL query #3, Oracle uses index X_ID_RES2.

select ss.* 
from V_JOURNAL ss, result r
where TO_NUMBER(ss.ID_SML||ss.ID_KT||ss.ID_GRADUATE)=r.id_res2

And, when I run SQL query #4, Oracle uses index X_ID_RES2 too.

select ss.*,r.* 
from V_JOURNAL ss, result r
where TO_NUMBER(ss.ID_SML||ss.ID_KT||ss.ID_GRADUATE)=r.id_res2

Why did Oracle use an index in SQL #4, but did not use one in SQL #2?

Best Answer

When Oracle uses an index, it has to lookup the actual data of the table in the table itself. Remember, an index is just a list of pointers to the actual data ordered in some way. The index itself only contains the indexed columns.

So, if you specify

select ss.*,r.* 
from V_JOURNAL ss, result r
where TO_NUMBER(ss.ID_SML||ss.ID_KT||ss.ID_GRADUATE)=r.id_res2

You are asking for the contents of ALL columns in v_journal and result. Oracle can then choose based on heuristics (rule based) or statistics (nowadays) what is expected to be cheaper. When the number of expected returned rows is high compared to the total size of the tables, a full table scan is considered cheaper (and is often faster, but please remember it may purge your database cache as a nasty side effect).

When you replace it by something just needing the data in the index like:

select count(*)
from V_JOURNAL ss, result r
where TO_NUMBER(ss.ID_SML||ss.ID_KT||ss.ID_GRADUATE)=r.id_res2

All needed data can be found in the index and the index will be used as a cheap full table scan (full index scan), since the index is probably smaller in size in bytes.

Related Question