Did you try using a histogram on the indexed column?
Mike
Galen Boyer <galenboyer_at_hotpop.com> wrote in message news:<u1y98umzz.fsf_at_grossprofit.com>...
> 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.
Received on Fri Aug 09 2002 - 13:19:07 CDT
