Query not using index when using 'or' clause

Advertisement
I have a problem with something I can't understand about indexes in Oracle 11g.
We can create test data with:
create table test2(field1 varchar2(100),field2 varchar2(100),field3 number,field4 varchar2(100));
create index test2_idx1 on test2(upper(field1));
create index test2_idx1b on test2(field1);
create index test2_idx2 on test2(field3);
DECLARE
j NUMBER :=1;
BEGIN
FOR i IN 1..500000
LOOP
INSERT
INTO test2
(field1,field2, field3, field4)
VALUES
('field1='||i,'a', j, 'i' );
IF (i mod 1000)=0 THEN
j := j+1;
END IF;
END LOOP;
COMMIT;
END;
EXEC DBMS_STATS.GATHER_TABLE_STATS ('system', 'test2');
Then I make some explain plans which result I can't understand
Query 1:
SELECT * FROM test2 WHERE field3=1;
Explain plan:
Explain plan for query 01
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1003 | 28084 | 10 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 1003 | 28084 | 10 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST2_IDX2 | 1003 | | 5 (0)| 00:00:01 |
Everything OK here. Index is used.
Query 2:
SELECT * FROM test2 WHERE upper(field1)='FIELD1=1';
Explain plan
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 28 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 1 | 28 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST2_IDX1 | 1 | | 3 (0)| 00:00:01 |
Everything OK again. Index is used.
Query 3:
SELECT /*+ USE_CONCAT */ * FROM test2 WHERE field1='FIELD1=1' OR field3=1;
Explain plan
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1004 | 28112 | 14 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST2 | 1 | 28 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TEST2_IDX1B | 1 | | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| TEST2 | 1003 | 28084 | 10 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | TEST2_IDX2 | 1003 | | 5 (0)| 00:00:01 |
Indenxes are used in concatenation. No problem again.
Query 4:
SELECT /*+ USE_CONCAT */ * FROM test2 WHERE upper(field1)='FIELD1=1' OR field3=1;
Explain plan
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1004 | 28112 | 641 (4)| 00:00:08 |
| 1 | CONCATENATION | | | | | |
|* 2 | TABLE ACCESS FULL | TEST2 | 1 | 28 | 631 (4)| 00:00:08 |
|* 3 | TABLE ACCESS BY INDEX ROWID| TEST2 | 1003 | 28084 | 10 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | TEST2_IDX2 | 1003 | | 5 (0)| 00:00:01 |
Here my problem arises. Why is test2_idx1 not being used? Is it because it is a function index? Is there any workaround in this cases?
Thanks a lot in advance.
Advertisement

Replay

Interesting. A "workaround" which I thought first was:
SELECT /*+ USE_CONCAT */ * FROM test2 WHERE upper(field1)='FIELD1=1'
UNION ALL
SELECT /*+ USE_CONCAT */ * FROM test2 WHERE field3=1;
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                                 
|   0 | SELECT STATEMENT             |            |  1001 | 28042 |    15  (74)| 00:00:01 |                                                                                                                                                                                                                 
|   1 |  UNION-ALL                   |            |       |       |            |          |                                                                                                                                                                                                                 
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST2      |     1 |    42 |     4   (0)| 00:00:01 |                                                                                                                                                                                                                 
|*  3 |    INDEX RANGE SCAN          | TEST2_IDX1 |     1 |       |     3   (0)| 00:00:01 |                                                                                                                                                                                                                 
|   4 |   TABLE ACCESS BY INDEX ROWID| TEST2      |  1000 | 28000 |    11   (0)| 00:00:01 |                                                                                                                                                                                                                 
|*  5 |    INDEX RANGE SCAN          | TEST2_IDX2 |  1000 |       |     5   (0)| 00:00:01 |                                                                                                                                                                                                                 
-------------------------------------------------------------------------------------------   But I do not like using UNION for such tricks. So I thought, what would ORACLE do without hint?
SELECT  * FROM test2 WHERE upper(field1)='FIELD1=1' or field3=1;
| Id  | Operation                        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                             
|   0 | SELECT STATEMENT                 |            |  1001 | 42042 |   176   (0)| 00:00:03 |                                                                                                                                                                                                             
|   1 |  TABLE ACCESS BY INDEX ROWID     | TEST2      |  1001 | 42042 |   176   (0)| 00:00:03 |                                                                                                                                                                                                             
|   2 |   BITMAP CONVERSION TO ROWIDS    |            |       |       |            |          |                                                                                                                                                                                                             
|   3 |    BITMAP OR                     |            |       |       |            |          |                                                                                                                                                                                                             
|   4 |     BITMAP CONVERSION FROM ROWIDS|            |       |       |            |          |                                                                                                                                                                                                             
|*  5 |      INDEX RANGE SCAN            | TEST2_IDX2 |       |       |     5   (0)| 00:00:01 |                                                                                                                                                                                                             
|   6 |     BITMAP CONVERSION FROM ROWIDS|            |       |       |            |          |                                                                                                                                                                                                             
|*  7 |      INDEX RANGE SCAN            | TEST2_IDX1 |       |       |     3   (0)| 00:00:01 |                                                                                                                                                                                                             
----------------------------------------------------------------------------------------------- Thist looks perfect to me. (Please ignore the "higher" cost on the second plan. On your real data, it should be faster. Is it?)