Jump to content
vidhuvs

Index Full Scan

Recommended Posts

Hi Team,

As per Oracle docs

When the Optimizer Considers Index Full Scans

The optimizer considers an index full scan in a variety of situations.

The situations include the following:

  • A predicate references a column in the index. This column need not be the leading column.

  • No predicate is specified, but all of the following conditions are met:

    • All columns in the table and in the query are in the index.

    • At least one indexed column is not null.

  • A query includes an ORDER BY on indexed non-nullable columns.

 

Now to check this scenario I performed the following

CREATE TABLE Test_Table (
   Eid          NUMBER        NOT NULL,
   First_Name  VARCHAR2(100) NOT NULL,
   Last_Name   VARCHAR2(100) NOT NULL,
   Description VARCHAR2(100) NULL
);


BEGIN
   FOR counter IN 1 .. 10000 LOOP
      INSERT INTO Test_Table (EId, First_Name, Last_Name, Description)
         VALUES (CustomerSequence.NEXTVAL,
                 DBMS_RANDOM.STRING('A', 10),
                 DBMS_RANDOM.STRING('A', 20),
                 DBMS_RANDOM.STRING('A', 100));
   END LOOP;
END;
/

CREATE INDEX test_tbl_idx1 ON Test_Table (eId, first_name);

However when I generated the explain plan for the below query it showed Index range scan instead of Index Full Scan. Can you please explain why?

SELECT * FROM Test_Table where eid = 40005;

Because as per Oracle docs "A predicate references a column in the index. This column need not be the leading column." should show Index Full scan.

 

Thanks,

Vidhu V S

Share this post


Link to post
Share on other sites

Hi Vidhu, and welcome to the forum!

Based the specific where clause in your SQL, the optimizer realized that an index range scan was less expensive than an index fast full scan.

Also, what release are you on, and what are your optimizer parameter values?

http://www.dba-oracle.com/t_sql_optimizer_parameters.htm

You can verify that the optimizer made the best choice by forcing an index fast full scan with the index_ffs hint!

http://www.dba-oracle.com/t_index_fast_full_scan.htm

Hope this helps . . .

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×