Re: SQL (optimize) question

Stephan wrote:
>
> Hi there,
>
> In a table store data coming in from measurements outside.
> In another table i store commands send to another application.
> Many of the data is 'old' after a month, but for reporting activities may
> not be deleted.
>
> Therefore I introduced a field HISTORY_Y_N in both tables.
> For new records the field HISTORY_Y_N is set to 'N' and after some time the
> HISTORY_Y_N field is set to 'Y'.
>
> Now I wonder for performance reasons:
> Imagine you have to read some records from the table.
> You only want to use records where where HISTORY_Y_N = N.
> Do I have to use hints?
> Do I have to create an index on the HISTORY_Y_N field?
>
> Example (say the data_table has 500.000 records, 10.000 have HISTORY_Y_N=N
> and all other reocords haev HISTORY_Y_N=Y)
> SELECT incoming_data FROM data_table WHERE status=' I' and HISTORY_Y_N=N
> Thanks
> Stephan

If a field is going to be a primary key for retrieving the data, by all means try to incorporate it into an index if possible.

Rather than complicating the issue with another field like History_Y_N, you already have a status field. Update the Status field (and index it) to have a status for old or something along those lines.

> Does Oracle optimize for me (that is only look in 10.000 records for records
> having status=' I')?

If STATUS is the leading edge of an index then it will do a range retrieval of that portion of the index.

-- 
Regards,

Michael Austin            OpenVMS User since June 1984
First DBA Source, Inc.    Registered Linux User #261163
Sr. Consultant            http://www.firstdbasource.com
                         
http://www.firstdbasource.com/donation.html
704-947-1089 (Office)     704-236-4377 (Mobile)