CODEWITHSUNDEEP
oracle-databaseoracle11g
Rams
Rams

Reputation: 11

Doubts in Oracle index

 Tran_id         Tran_name     Tran_region     Tran_Date    Tran_Item 
    1             Tanya         Europe          04-FEB-16    book
    2             Tanya         Europe          04-FEB-16    book1
    3             Tanya         Europe          04-FEB-16    book2
    4             Tanya         Europe          04-FEB-16    book3

For this table am creating an Index as Index(Tran_region,Tran_date)

Lets assume there are one million rows in my test table

I need to fetch the records based on Tran_name='Tanya' , Tran_region='Europe', Tran_date='04-Feb-16'

My Oracle Query is

SELECT * 
FROM TEST_TABLE 
WHERE Tran_name='Tanya' , Tran_region='Europe', Tran_date='04-Feb-16'

How the index will run for my query, will it go for full table scan or scan based on the indexed columns

As I created the index for the column Tran_region, Tran_date but in my where clause I specified the tran_name also which is not indexed.

Appreciate if anyone provide the index functionality working principal. I am new to Oracle technologies.

Upvotes: 1

Views: 50

Answers (1)

Nick.Mc
Nick.Mc

Reputation: 19184

If you ran that query it'll just throw an error as it is invalid.

You don't really know what it will do until you run it. It might choose not to use the index for a bizarre reason. But it will probably use your index since you are searching on leading columns of the index. If another index existed with all of those columns (a covering index) it would probably use that instead.

If another index existed that was missing one of those as a leading column, it might do a skip scan on that one instead.

It's the order of columns in the index, not the order of columns in the where that matters.

Upvotes: 1

Related Questions