Function Based Indexes with in clause?

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Fri, 9 Aug 2002 12:55:07 +0100
Message-ID: <E2F6A70FE45242488C865C3BC1245DA7027BD2A5@lnewton.leeds.lfs.co.uk>

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 !

regards,
Norman.

---

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------

Received on Fri Aug 09 2002 - 06:55:07 CDT