Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

Oracle Index Question

From: Muggle <zimpany_at_gmail.com>
Date: 7 Feb 2007 13:05:34 -0800
Message-ID: <1170882330.807371.304380@v33g2000cwv.googlegroups.com>


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 Received on Wed Feb 07 2007 - 15:05:34 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US