Doubt on Funtional Indexes

From: Praveen <spraveen2001_at_yahoo.com>
Date: 28 Feb 2003 01:48:04 -0800
Message-ID: <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 - 10:48:04 CET