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

From: lfree <a_at_a.a>
Date: Mon, 12 Mar 2007 17:26:27 +0800
Message-ID: <et36c4$efd$1@news.yaako.com>

FBI? MAX? INDEX FULL SCAN (MIN/MAX)? select * from v$version
BANNER

---

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production

scott> create table t as select * from all_objects;

scott> create index if_t_object_name on t(lower(object_name));

scott> create index i_t_object_name on t(object_name);

scott>BEGIN
  SYS.DBMS_STATS.GATHER_TABLE_STATS (

      OwnName        => 'SCOTT'
     ,TabName        => 'T'
    ,Method_Opt        => 'FOR ALL COLUMNS SIZE 1 '
    ,Degree            => 4
    ,Cascade           => TRUE;

END; scott> set autotrace traceonly ;
scott> select max(object_name) from t ;

Plan hash value: 348001784

---

---

| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |

---

---

| 0 | SELECT STATEMENT | | 1 | 24 | 2
(0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 24 |
| |
| 2 | INDEX FULL SCAN (MIN/MAX)| I_T_OBJECT_NAME | 50031 | 1172K| 2
(0)| 00:00:01 |

---

---

          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        439  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

scott> select max(lower(object_name)) from t ;

Plan hash value: 3320467716

---

---

| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |

---

---

| 0 | SELECT STATEMENT | | 1 | 24 | 57
(2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 24 |
| |
| 2 | INDEX FAST FULL SCAN| IF_T_OBJECT_NAME | 50031 | 1172K| 57
(2)| 00:00:01 |

---

---

          0  recursive calls
          0  db block gets
        253  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

why use FBI query max(lower(object_name)) ? execute plan is "INDEX FAST FULL SCAN". Received on Mon Mar 12 2007 - 04:26:27 CDT