function index
- From: johan Eriksson <johan.eriksson@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 23 Jan 2006 15:06:37 +0100
Hi
The database is 10.2 on RHEL 3.
I have a table on which I have 2 indexes, one normal and one
function-based.
The different queries I run are
select count(*) from t1 where username='user1';
and
select count(*) from t1 where lower(username)='user1';
the index are created with
create index f_t1_username on t1(lower(username));
create index uq_t1_username on t1(username);
The difference comes when the queries are executed, the first sql gives
me an trace like
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2
(0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 12 |
| |
|* 2 | INDEX UNIQUE SCAN| UQ_T1_USERNAME | 1 | 12 | 2
(0)| 00:00:01 |
-----------------------------------------------------------------------------------------
and that looks like it should, there is only one row that has
username='user21'
the second one gives
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%
CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 3
(0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 12 |
| |
|* 2 | INDEX RANGE SCAN| F_T1_USERNAME | 25000 | 292K| 3 (0)|
00:00:01 |
---------------------------------------------------------------------------------------
(the same here, only on row that has username='user21')
What I cant figure out or find on the net is why it will give 25000 rows
when it uses function based index. Anyon care to explain why this
happens?
/johan
--
//www.freelists.org/webpage/oracle-l
Other related posts:
