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

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL (optimize) question

Re: SQL (optimize) question

From: Sivaraj Shanmugham <Sivaraj.Shanmugham_at_oracle.com>
Date: Mon, 01 Jul 2002 18:14:15 -0700
Message-ID: <3D20FE67.34AC3D7F@oracle.com>


Did you explore the option of using histograms? The data distribution you are talking about can use histograms to determine if an index is needed.
Histograms help you identify the density of a particular value within a column..

In your case, only 2% of records have HISTORY_Y_N = 'N' and the rest have 'Y' If you use histograms, optimizer will determine if an index is required for the query. If the
query has where clause: " where history_y_n = 'N' ", index will be used. If "history_y_n = 'Y',
index may not be used .. You will be able to see that from the explain plan you generate.
You can create an index on the column and use explain plan to see if further tuning is required.

Thanks,
Sivaraj.

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


Received on Mon Jul 01 2002 - 20:14:15 CDT

Original text of this message

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