CODEWITHSUNDEEP
sqloracle-databasequery-performancedatabase-indexes
3bdl1
3bdl1

Reputation: 33

Are indexes used if the WHERE clause contains unindexed columns

I am using a table with the following indexes: 

ind1 : column A,B,C,D
ind2 : column G
ind3 : column C
ind4 : column F
ind5 : column D
ind6 : column E

In my query, I am using the following selection criteria: 

SELECT * FROM table WHERE
    A=a
AND B=b
AND C=c
AND D=d
AND E=e
AND F=f
AND H=h  (H does not have an index)

My question is whether or not the indexes of the table are used, and if the performances are enhanced in this query, note that the table contains a total of 12 columns and 32M records.

Upvotes: 3

Views: 1131

Answers (1)

wolφi
wolφi

Reputation: 8361

Yes, the query will use almost certainly one of the indexes to preselect which rows might fullfill at least some of the criteria. To check if the WHERE clause is true for unindexed columns (like your column H), Oracle just checks in the table itself. As the index points to the correct physical location of the table, this is normally quite fast.

Which index is used, depends on many factors like size of the table, size of the index, uniqueness of the table columns, uniqueness of the index, data distribution of the column values etc.

To see which indexes are used in your query, have a look at the execution plan, which you can see for instance in SQL Developer by hitting F10.

EDIT: In my experience, Oracle selects the most promising index (which will reduce the amount of rows most), and then checks all columns in the WHERE clause by such a table look up.

Please make also sure that the statistics of the table are up to date. If in doubt, check with

SELECT table_name, last_analyzed FROM USER_TABLES;

If last_analyzed is empty or an old date, please search for DBMS_STATS.GATHER_TABLE_STATS to refresh the stats.

Upvotes: 2

Related Questions