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

"lfree" <a_at_a.a> wrote in message news:et36c4$efd$1_at_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".
>
>

Looks like a bug.

    http://jonathanlewis.wordpress.com/2007/03/12/methods/

There have been others in the same area of optimisation

    http://www.jlcomp.demon.co.uk/cbo_book/ch_04.html#index_only_anomaly

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html