Oracle 11g: index not used in "select different" -query

Question

My question is about Oracle 11g and the use of indexes in SQL queries.

There is a table in my database that is structured as follows:

Table tab ( rowid NUMBER(11), unique_id_string VARCHAR2(2000), year NUMBER(4), dynamic_col_1 NUMBER(11), dynamic_col_1_text NVARCHAR2(2000) ) TABLESPACE tabspace_data; 

I created two indexes:

 CREATE INDEX Index_dyn_col1 ON tab (dynamic_col_1, dynamic_col_1_text) TABLESPACE tabspace_index; CREATE INDEX Index_unique_id_year ON tab (unique_id_string, year) TABLESPACE tabspace_index; 

The table contains from 1 to 2 million records. I am extracting data from it by executing the following SQL command:

 SELECT distinct "sub_select"."dynamic_col_1" "AS_dynamic_col_1","sub_select"."dynamic_col_1_text" "AS_dynamic_col_1_text" FROM ( SELECT "tab".* FROM "tab" where "tab".year = 2011 ) "sub_select" 

Unfortunately, it takes about 1 hour to complete the request, although I created both indexes described above. The explanation plan shows that Oracle uses "Full table access", that is, a full table scan. Why is the index not used?

As an experiment, I tested the following SQL command:

 SELECT DISTINCT "dynamic_col_1" "AS_dynamic_col_1", "dynamic_col_1_text" "AS_dynamic_col_1_text" FROM "tab" 

Even in this case, the index is not used and a full table scan is performed.

In my real database, the table contains more indexed columns such as "dynamic_col_1" and "dynamic_col_1_text". The entire index file is about 50 GB in size.

Some additional information:

• The Oracle 11g database is installed on my local computer.
• I am using Windows 7 Enterprise 64bit.
• The entire index is divided into 3 dbf files about 50 GB in size.

I would really be happy if someone could tell me how to get Oracle to use the index in the first query. Since the first query is used by another program to retrieve data from the database, it can hardly be changed. Therefore, it would be nice to set up the table.

Thanks in advance.

[01.10.2011: UPDATE]

I think I found a solution to the problem. Both dynamic_col_1 and dynamic_col_1_text columns are NULL. After modifying the table to ban NULL values ​​in both columns and adding a new index exclusively for the year column, Oracle performs a quick index scan. The advantage is that the request takes about 5 seconds to complete, and not 1 hour, as before.

Answer 1

I don't know if this matches, but I tested the following query:

 SELECT DISTINCT "dynamic_col_1" "AS_dynamic_col_1", "dynamic_col_1_text" "AS_dynamic_col_1_text" FROM "tab" WHERE "dynamic_col_1" = 123 AND "dynamic_col_1_text" = 'abc' 

An explanation plan for this query shows that Oracle uses index scanning in this scenario.

The columns dynamic_col_1 and dynamic_col_1_text are NULL. Does this affect the use of the index?

10/01/2011: UPDATE]

I think I found a solution to the problem. Both dynamic_col_1 and dynamic_col_1_text columns are NULL. After changing the table to prohibit "NULL" values ​​in both columns and adding a new index exclusively for a year of columns, Oracle performs a quick index scan. The advantage is that the request takes about 5 seconds to complete, and not 1 hour, as before.

Answer 2

Are you sure index access will be faster than a full table scan? As a very rough estimate, a full table scan is 20 times faster than reading an index. If the tab contains more than 5% of the data in 2011, it is not surprising that Oracle will use a full table scan. And, as @Dan and @Ollie mentioned, with year as the second column, this will make the index even slower.

If the index is really faster than the problem, it's probably bad statistics. There are hundreds of ways that statistics can be bad. Very briefly, here is what I would look first:

• Run the explanation plan with and without an index. Are the power no more than 10 times? Is time off 10 or more times?
• If the power is turned off, make sure the table and index have up-to-date statistics and you use reasonable ESTIMATE_PERCENT (DBMS_STATS.AUTO_SAMPLE_SIZE is almost always better for 11g).
• If time is off, check workload statistics.
• Are you using parallelism? Oracle always suggests an almost linear improvement for parallelism, but on the desktop with one hard drive, you probably won't see anything at all.

Also, this is not particularly relevant to your problem, but you can avoid using quoted identifiers. Once you use them, you should use them everywhere, and this usually makes your tables and queries work with pain.

Answer 3

Your index should be:

 CREATE INDEX Index_year ON tab (year) TABLESPACE tabspace_index; 

In addition, your request may simply be:

 SELECT DISTINCT dynamic_col_1 "AS_dynamic_col_1", dynamic_col_1_text "AS_dynamic_col_1_text" FROM tab WHERE year = 2011; 

If your index was created exclusively for this query, but you can create it, including two selected columns, then the optimizer would not have to go to the table for the query data, it could get it directly from the index making your query more efficient again.

Hope this helps ...

Answer 4

I do not have an Oracle instance on hand, so this is a few guesses, but I am inclined to say this because you have a complex index in the wrong order. If you had year as the first column in the index, it could use it.

Answer 5

Second test request:

 SELECT DISTINCT "dynamic_col_1" "AS_dynamic_col_1", "dynamic_col_1_text" "AS_dynamic_col_1_text" FROM "tab" 

will not use the index because you have no WHERE clause, so you ask Oracle to read every row in the table. In this situation, a full table scan is a faster access method.

Also, as other posters have noted, your YEAR index has it in the second column. Oracle can use this index by scanning for gaps, but there is performance for that, and depending on the size of your table, Oracle may just decide to use FTS again.

Answer 6

Try the following:

1) Create an index in the year field (see Ollie's answer).

2) And then use this query:

 SELECT DISTINCT dynamic_col_1 ,dynamic_col_1_text FROM tab WHERE ID (SELECT ID FROM tab WHERE year=2011) 

or

 SELECT DISTINCT dynamic_col_1 ,dynamic_col_1_text FROM tab WHERE ID (SELECT ID FROM tab WHERE year=2011) GROUP BY dynamic_col_1, dynamic_col_1_text 

Perhaps this will help you.