Re: FBI? MAX? INDEX FULL SCAN (MIN/MAX)?

From: lfree <a_at_a.a>
Date: Tue, 13 Mar 2007 08:55:35 +0800
Message-ID: <et4sq7$d7k$1@news.yaako.com>

>Oracle did not even bother to use the function based index. Instead,
>it performed a fast full scan of the primary key index.

>Now, let's force Oracle to use the function based index:
>SELECT /*+ INDEX(T1 IND_T1) */
> MAX(ROUND(SIN(C1*3.141592/180),8))
>FROM
> T1;

thank you!

scott> select /*+ index(t,if_t_object_name) */ max(lower(object_name)) from t ;
Plan hash value: 3344583132

---

---

| Id  | Operation                   | Name             | Rows  | Bytes |
Cost (%CPU)| Time     |
----------------------------------------------------------------------------
--------------------
|   0 | SELECT STATEMENT            |                  |     1 |    24 |
249   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE             |                  |     1 |    24 |
|          |
|   2 |   FIRST ROW                 |                  | 50031 |  1172K|

249 (1)| 00:00:03 |
| 3 | INDEX FULL SCAN (MIN/MAX)| IF_T_OBJECT_NAME | 50031 | 1172K| 249 (1)| 00:00:03 |

---

---

---

          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        446  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Received on Mon Mar 12 2007 - 19:55:35 CDT