Hello everyone,

I need some help in formatting a query to make use of indices.

The front end application collects user inputs and sends to our
application and our application queries a database table and sends the
results back. The user can choose any combination of inputs. My task
here is to formulate the query( the 'where' clause). 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.

For example, for the request:

<Request>
<State>NY</State>
<SomeOtherField>somevalue</SomeOtherField>
</Request>

(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) " .

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) ?

Please note I have no control over the overall design of the
application. Oracle used here is 8.17.

Thanks in advance,
Muggle

On 07.02.2007 22:05, Muggle wrote:
> The front end application collects user inputs and sends to our
> application and our application queries a database table and sends the
> results back. The user can choose any combination of inputs. My task
> here is to formulate the query( the 'where' clause). 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.

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.

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. Note that the logic with CBO is much more complex
so there is no clear "if you add a filter to column X and there is an
index on it no FTS will happen".

> For example, for the request:
>
> <Request>
> <State>NY</State>
> <SomeOtherField>somevalue</SomeOtherField>
> </Request>
>
> (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) " .
>
> 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) ?

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

> Please note I have no control over the overall design of the
> application. Oracle used here is 8.17.

Probably rather 8.1.7...

Regards

robert

On Feb 7, 4:36 pm, Robert Klemme <shortcut...@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

On 07.02.2007 23:25, Muggle wrote:
> On Feb 7, 4:36 pm, Robert Klemme <shortcut...@googlemail.com> wrote:
> Again you did not get what I mentioned in my OP. I was talking about
> adding filter criteria on indexed columns.

Ah, ok. Sorry for that. It was late and I was tired and there was no
DDL posted.

To answer your question: as I said, generally adding a filter on a
column with an index reduces likelihood of a FTS. Also, reducing the
amount of data selected also reduces the likelihood of a FTS. What
actually happens depends on the DDL, data, Oracle version, configuration
parameters and probably even more things.

Kind regards

robert

Muggle wrote:

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

It might ... it might not. That it will do so is an assumption that
needs to be tested.

> 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) ?

Again it depends.

> Please note I have no control over the overall design of the
> application. Oracle used here is 8.17.

8.17 is not an Oracle version numbering on which much can be said.
Hopefully it is 8.1.7.4 and hopefully you will find yourself a job with
a version of Oracle that has been supported during the current
millenium.

That said ... without knowing your data no one can answer the question.
You need to stop asking unanswerable questions and use EXPLAIN PLAN and
common sense. Might I suggest you purchase Cary Millsap's and Jonathan
Lewis' books.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org