Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Cross-posted on SO. –  Alex Poole Jan 27 at 14:35
    
Unless I'm missing something, there was no index to use for query2. You created it after?! Why didn't you just create an index on result.id_res ? You may be better off creating a functional index on TO_NUMBER(ss.ID_SML||ss.ID_KT||ss.ID_GRADUATE) –  Phil Jan 27 at 18:43
    
@Phil - query 2 is against id_res, not id_res2... but it's not clear if it has the PK or UI against it (since they can't both be there against the same column as written). I mentioned on the SO version that it might have switched from a full index scan to a full table scan because it's selecting r.* in query 2; but awaiting the execution plans to see why query 4 didn't do the same... I'm guessing stats, or because id_res2 is nullable. Maybe. –  Alex Poole Jan 27 at 19:04
1  
Oh, I should read more carefully. Slaps self –  Phil Jan 27 at 19:05
    
Did you gather statistics for the table and the indexes before you ran your query? On Oracle 10g, you may not have up to date complete statistics on the newly-created indexes immediately. exec dbms_stats.gather_table_stats(user, 'RESULT', cascade=>true); will gather table and index stats. –  Mark Stewart Nov 5 at 13:27

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.