Please advise a better index

[melaniejp]

I created an index for a table but the performance increased just a little.
What the problem is and how should I create the index?
The SQL cannot be changed and only an index is required to increase the performance.

The following is detailed enviorenment.
(1)DB: Oracle10.2.0.3
(2)SQL:
select * from
(SELECT col1,col2, col3, col4 FROM table1
where col0 = 140 AND
col2>=to_date(' 2011-08-30','YYYY-MM-DD') AND
col2<=to_date(' 2011-08-30','YYYY-MM-DD') AND
upper(col4) = 'aaa'
ORDER BY col1 ASC)
where rownum<1000000;
(3)index
create index table1_idx on table1(col0,col 2,upper(col4));
(4)toltal record count in table1: 3,000,000
(5)selected record count of (2): 100,000
(6)explain plan of the SQL
PLAN_TABLE_OUTP UT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 207M|
| 7873 (1)| 00:01:35 |

|* 1 | COUNT STOPKEY | | | |
| | |

| 2 | VIEW | | 100K| 207M|
| 7873 (1)| 00:01:35 |

|* 3 | SORT ORDER BY STOPKEY | | 100K| 16M|
41M| 7873 (1)| 00:01:35 |

| 4 | TABLE ACCESS BY INDEX ROWID| TABLE1 | 100K| 16M|
| 4038 (1)| 00:00:49 |

|* 5 | INDEX RANGE SCAN | TABLE1_IDX | 97885 | |
| 374 (2)| 00:00:05 |

--------------------------------------------------------------------------------
----------------------------


PLAN_TABLE_OUTP UT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<1 000000)
3 - filter(ROWNUM<1 000000)
5 - access("COL0"=1 40 AND "COL2"=TO_DATE( '2011-08-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND

UPPER("COL4")=' AAA')

Please advice.
Thanks in advance.

[rski]

It does now work

upper(col4)='aa a'

[Rabbit]

There's not much else you can do except to include the select columns in the index as well. This would fully cover the query and it would not need to use the table references at all.

[rski]

If you do what rabbit says you will have to add to the index both col4 and upper(col4), because you use col4 in select statement and upper(col4) in where.

[melaniejp]

Thank you rski and Rabbit.

The data in table1 are created for test.
Col0 and col4 are all the same, only col2 is different.
In this case, col0=140 matches all records in the table and it the same as full scan,right?

I tested some patterns and elapsed time were almost the same.
(1)create index table1_idx on table1(col0,col 2,upper(col4));
(2)create index table1_idx on table1(col0,col 2,col4,upper(co l4));
(3)create index table1_idx on table1(col2,col 0,upper(col4));

[rski]

You have to add all columns you use in select

col1,col2, col3, col4

and also upper(col4) and col0
So create index on col0, col1,col2, col3, col4 ,upper(col4) and see if it speeds up the query.