Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle Index Question
On Feb 7, 4:36 pm, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> This is nonsense. First, filters should be dictated by business
> requirements - if the user wants only data for state = 'NY' you have to
> add that to the where clause - regardless of whether there is an index
> on state.
>
Thanks for your reply. But please read the post clearly before dismissing it nonsense. I did mention clearly the where clause :
> Second, filter criteria on non indexed columns will not prevent FTS.
> It's the other way round: using filter criteria on indexed columns makes
> a FTS more unlikely.
Again you did not get what I mentioned in my OP. I was talking about adding filter criteria on indexed columns.
> Again, filters should be determined by business requirements and not
> other considerations. I am not sure what exactly you mean by
> "defaulting the indexed columns". If you have an index on a column all
> values are covered (apart from nulls).
I agree. But business rules, being business rules and being very domain specific, are very difficult to explain in a post like this. And trying to present them would confuse the readers.
The thing is there is a maximum limit to the number of records we return(50) and there are millions of records with STATE='NY'. So obviously if the user specifies only one criteria (all records being of equal importance) we can send any arbitrary 50 and I was wondering, if adding a filter crtieria on a date-type indexed-coumn to the query would help, what filter would run faster: a 7 day range or a 30 day range on the indexed column ? Or to put it simpler, which query would run faster " SELECT * from TABLE_A where date_column_A BETWEEN sysdate-7 AND sysdate " or " SELECT * from TABLE_A where date_column_A BETWEEN sysdate-30 AND sysdate" if there is an index on coumn date_column_A ?
>
> Probably rather 8.1.7...
>
Yes, that was my bad.
Thanks again
Muggle
Received on Wed Feb 07 2007 - 16:25:23 CST
![]() |
![]() |