Re: Doubt on Funtional Indexes

spraveen2001_at_yahoo.com (Praveen) wrote in message news:<98d8ec76.0302280148.177bf777_at_posting.google.com>...
>
> I have table USR with 2 billion records, the columns are a.usr_id,
> a.owner_id,
> a.username, a.password,a.first_name,
> a.last_name,a.middle_initial,a.status_in. And i have created
> functional index on (first_name,status_in) and (last_name,status_in).
> SELECT rownum, usr_id, owner_id,
> username, password,first_name, last_name,middle_initial, status_in
> FROM (
> SELECT a.usr_id, a.owner_id,a.username, a.password,a.first_name,
> a.last_name,
> a.middle_initial, a.status_in
> FROM
> usr a
> WHERE UPPER(a.first_name) LIKE 'F3%'
> AND a.status_in = 'A'
> ORDER BY last_name,first_name )
> where rownum < 101
>

<< snip tkprof >>

> My doubt is why this query using index created on
> (last_name,status_in) USR_I_LASTNAME_STATUS_IN. why not using index
> (first_name,status_in) USR_I_FIRSTNAME_STATUS_IN.

Unless you've created an function-based index on UPPER( first_name ), status_in Oracle won't be able to use any index on first_name to process this query. Applying any function to a column makes it ineligible for standard indexes. That's why function-based indexes were created.

Justin Received on Fri Feb 28 2003 - 19:48:18 CET