Re: How is optimization calculated on a query

Thanks for the hint. I tried it and it didn't seem to have any effect on my execution plan. I do sometimes get an index scan on a 1 date constraint and I have gotten in the past index scans on multiple dates without the to_date. I am getting the correct number of rows selected.

I would think that doing a select --+ index would force an index scan if possible and it doesn't.

Thanks in advance for any other suggestions. -Rick

In article <36151ea7.7483203_at_netnews.worldnet.att.net>,   gennick_at_worldnet.att.net wrote:
> 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
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Mon Sep 28 1998 - 14:09:56 CDT