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 -> Re: How is optimization calculated on a query

Re: How is optimization calculated on a query

From: Jonathan Gennick <gennick_at_worldnet.att.net>
Date: 26 Sep 1998 18:45:55 GMT
Message-ID: <36151ea7.7483203@netnews.worldnet.att.net>


On Fri, 25 Sep 1998 22:23:07 GMT, ricke1_at_my-dejanews.com wrote:

>I have been trying to do a select on a table like
>select
>c1, c2
>from t1
>where
>c1='01-jan-1997'
>order by c2;
>I have 2 indexes, one on c1 and one on c2. Yet the execution plan sometimes
>shows a full table scan.

My guess is that oracle probably won't use the c2 index, and will just do a sort to order the results. The reason your c1 index is not being used may have something to do with the way you are expressing the date in your query. Comparing the date to a string implies a conversion, and Oracle may be converting C1 to a character string. This would preclude the use of the index. Try rewriting your query like this:

select c1, c2
from t1
where c1=to_date('01-jan-1997','dd-mon-yyyy') order by c2;

With the conversion coded explicitely, Oracle won't convert C1, and will be free to use the index.

One other thing to be aware of is that Oracle dates also contain a time value. If the time for each occurance of C1 is not exactly midnight, then the above statement will fail to return the desired results. If time of day is a consideration, rewrite your statement to look like this:

select c1, c2
from t1
where c1 >= to_date('01-jan-1997','dd-mon-yyyy')   and c1 < to_date('02-jan-1997','dd-mon-yyyy') order by c2;

Note that you use ">=" when comparing to the begin time, and "<" when comparing for the end. This may look cumbersome, but it allows you to avoid using the TRUNC function on c1, which would force a table scan again.

regards,

Jonathan Received on Sat Sep 26 1998 - 13:45:55 CDT

Original text of this message

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