function index

From: johan Eriksson <johan.eriksson_at_bossmedia.se>
Date: Mon, 23 Jan 2006 15:06:37 +0100
Message-Id: <1138025197.14281.39.camel@erijoh.ad.bossmedia.se>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jan 23 2006 - 08:06:37 CST