Re: TO_DATE function causes table scan

[Quoted] [Quoted] Thanks very much for the link, I'll give it a read!

[Quoted] I'm definitely not obessed with the index, and I know 3500 rows is nothing. I should have been more specific in my last post on why I need the index to work: When in production this table will grow at the rate of about 10,000 rows a day so I need to know if the query is going to use the index or not.

After some initial research I was worried that using the TO_DATE in the where would cause Oracle not to use the index on the ALM_DATE column. Could someone tell me definititively that this is or is not the case? Function Based Indexes don't work with TO_DATE because it's not deterministic, so using FBI's won't solve this particular problem -- unless I've done it wrong -- suggestions are welcomed :)

One other thing to note is that I am always going to grabbing the latest records out of this table (RE: where ALM_DATE >= TO_DATE('03-19-2006 06:26:45', 'MM/DD/YYYY HH24:MI:SS'). In SQL Server I would create a clustered index (leaf node of the b-tree points directly to the data) on the ALM_DATE field and this would improve performance dramatically. Does Oracle have a silver bullet like this? Or will a regular index work fine?

Thanks again,
Dave Received on Fri Mar 31 2006 - 15:45:53 CEST