Re: Oracle Index Question

From: Muggle <zimpany_at_gmail.com>
Date: 7 Feb 2007 14:25:23 -0800
Message-ID: <1170887123.822047.14190@q2g2000cwa.googlegroups.com>

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 :

> > (assuming the corresponding columns for State and SomeOtherField are
> > non-index columns), the query would be "SELECT * from TABLE_A WHERE
> > State='NY' and SomeOtherField='somevalue' AND (date_column_A BETWEEN
> > sysdate-7 AND sysdate) " .

> 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.

The table has 20 columns .Five columns are indexed individually and one of them is date
type, let us call it date_column_A.
> When the input contains only non-indexed colums , I am being told to
> append "And (date_column_A between sysdate-7 and sysdate) " to the
> query to avoid a full scan of the table.

> 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.

My question is would it reallly help ? And if yes, is defaulting the indexed column range to (sysdate-7 and sysdate) better than defaulting it to , say, (sysdate-30, sysdate) ?

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