Re: Doubt on Funtional Indexes

Not sure how you generated the tkprof output, it is difficult to read. But to the question
> 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.

I think this is how ORACLE handled it:
Index range scan on USR_I_FIRSTNAME_STATUS_IN would not work since you use "like" on first_name column. So either FTS or full index scan is needed. ORACLE chose FIS on USR_I_LASTNAME_STATUS_IN because this eliminated the sorting requirement on last_name, as specified in your order clause. Though sorting on first_name is still required. Apparently CBO figured that this is more efficient than doing a FTS on the table and then sort the entire 2-billion records on both last_name and first_name.

• Jusung Yang

spraveen2001_at_yahoo.com (Praveen) wrote in message news:<98d8ec76.0302280148.177bf777_at_posting.google.com>...
> Hi All,
>
> 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
>
> When execute this query.. the TKPROF gave the following output...
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.02 0.05 0 0 0
> 0
> Execute 1 0.00 0.00 0 0 0
> 0
> Fetch 8 0.48 1.98 1021 3097 5
> 100
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 10 0.50 2.03 1021 3097 5
> 100
>
> Misses in library cache during parse: 1
> Optimizer goal: CHOOSE
> Parsing user id: 199 (IMDB_TUNE)
>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: CHOOSE
> 0 SELECT STATEMENT GOAL: CHOOSE
> 0 SORT (ORDER BY)
> 0 SORT (ORDER BY)
> 0 COUNT (STOPKEY)
> 0 COUNT (STOPKEY)
> 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'USR'
> 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'USR'
> 0 VIEW
> 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'USR'
> 0 VIEW
> 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'USR'
> 0 VIEW
> 0 VIEW
> 0 INDEX (FULL SCAN) OF 'USR_I_LASTNAME_STATUS_IN'
> (NON-UNIQUE)
>
> 0 INDEX (FULL SCAN) OF 'USR_I_LASTNAME_STATUS_IN'
> (NON-UNIQUE)
>
> 0 INDEX (FULL SCAN) OF 'USR_I_LASTNAME_STATUS_IN'
> (NON-UNIQUE)
>
> 0 SORT (ORDER BY STOPKEY)
> 0 INDEX (FULL SCAN) OF 'USR_I_LASTNAME_STATUS_IN'
> (NON-UNIQUE)
>
> 0 SORT (ORDER BY STOPKEY)
> 0 INDEX (FULL SCAN) OF 'USR_I_LASTNAME_STATUS_IN'
> (NON-UNIQUE)
>
> 0 SORT (ORDER BY STOPKEY)
> 0 INDEX (FULL SCAN) OF 'USR_I_LASTNAME_STATUS_IN'
> (NON-UNIQUE)
>
> 0 SORT (ORDER BY STOPKEY)
> 0 INDEX (FULL SCAN) OF 'USR_I_LASTNAME_STATUS_IN'
> (NON-UNIQUE)
>
> 0 SORT (ORDER BY STOPKEY)
> 0 INDEX (FULL SCAN) OF 'USR_I_LASTNAME_STATUS_IN'
> (NON-UNIQUE)
>
> 0 SORT (ORDER BY STOPKEY)
> 0 SORT (ORDER BY STOPKEY)
> 0 SORT (ORDER BY STOPKEY)
> 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'USR'
> 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'USR'
> 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'USR'
> 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'USR'
> 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'USR'
> 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'USR'
> 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'USR'
> 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'USR'
> 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'USR'
> 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'USR'
> 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'USR'
> 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'USR'
> 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'USR'
> 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'USR'
> 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'USR'
> 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'USR'
> 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.
>
> Thanks in Advance
> Praveen
Received on Fri Feb 28 2003 - 20:51:56 CET