Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle Index Question

Re: Oracle Index Question

From: Robert Klemme <>
Date: Wed, 07 Feb 2007 22:36:54 +0100
Message-ID: <>

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


        robert Received on Wed Feb 07 2007 - 15:36:54 CST

Original text of this message