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

"Have" to create an index? Of course not. But try the following:

1. Run: DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => schema_name, CASCADE => True);
2. EXPLAIN_PLAN the queries
3. Build indexes
4. EXPLAIN_PLAN the queries

No doubt you will know the answer as to whether you "want to" when you are finished.

Daniel Morgan Received on Mon Jul 01 2002 - 15:11:11 CDT