SQL (optimize) question

From: Stephan <test_at_test.com>
Date: Mon, 01 Jul 2002 19:03:06 GMT
Message-ID: <KH1U8.95616$pq3.2448470@nlnews00.chello.com>

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 Does Oracle optimize for me (that is only look in 10.000 records for records having status=' I')?

Thanks
Stephan Received on Mon Jul 01 2002 - 14:03:06 CDT