Re: Function Based Indexes with in clause?

On Fri, 9 Aug 2002, Norman.Dunbar_at_lfs.co.uk wrote:
> Galen,
>
> from your first post :
>
>>> ORA>create table emp as select * from all_tables;
>>> Table created.
>
>>> ORA>update emp set table_name = initcap(table_name);
>>> 276 rows updated.
>
> You only have 276 rows in your table. I suspect that you need
> many many more rows to avoid a FTS. Oracle gets more data in
> one go from a FTS than from index/table reads. Hence with a
> small number of rows, and a decent sized multi-block read count
> setting, the optimizer (sic) will FTS.
>
> Apart from that, you didn't analyse your table or index either,
> so no stats !

Okay, I tried another one to try to increase the size of the rows just for you :-). (I took out extraneous prompt stuff and timings)

    ORA>create table t1 as select * from all_tables;

    Table created.

    ORA>insert into t1 select * from t1;

         insert into t1 select * from t1;
         insert into t1 select * from t1;
         insert into t1 select * from t1;
         insert into t1 select * from t1;
         insert into t1 select * from t1;
         insert into t1 select * from t1;
         insert into t1 select * from t1;
         insert into t1 select * from t1;
         insert into t1 select * from t1;
         commit;
         create index t1_upper_idx on t1(upper(table_name));
         analyze table t1 compute statistics ;
         analyze table t1 compute statistics for all indexed columns;

    276 rows created.

    552 rows created.

    1104 rows created.

    2208 rows created.

    4416 rows created.

    8832 rows created.

    17664 rows created.

    35328 rows created.

    70656 rows created.

    141312 rows created.

    Commit complete.

    Index created.

    Table analyzed.

    Table analyzed.

Okay, now lets try the tests:
1) A multiple in clause.

    ORA>explain plan set statement_id = 'GBOYERSPLAN' into plan_table for

         select table_name, owner from t1 where upper(table_name)
         in ('T1','HD_STORE_COLORS_TBL2');

    Explained.

    ORA>@explain_galen

             1   SELECT STATEMENT
             2     TABLE ACCESS FULL T1

2) A single in clause (Works in an equality as well)

    ORA>explain plan set statement_id = 'GBOYERSPLAN' into plan_table for

         select table_name, owner from t1 where upper(table_name) in ('T1');

    Explained.

    ORA>@explain_galen

             1   SELECT STATEMENT
             2     TABLE ACCESS BY INDEX ROWID T1
             3	 INDEX RANGE SCAN T1_UPPER_IDX

The T1 table is 55M now.

--
Galen Boyer