Re: Help needed optimize code

Hi Jeff,

If the columns named in your BETWEEN condition are indexed Oracle will ignore
your indexes and do a full table scan. You can get around this by using AND
conditions so that the index will be used. The use of AND instead of BETWEEN
will result in the last two conditions in your WHERE clause being:

	         ( (trn_cnts.product_type >= ls_from_product 


>                   AND trn_cnts.product_type <= ls_to_product ) AND

>            ( trn_cnts.product_type not >= ls_not_from_product

>              AND trn_cnts.product_type <= ls_not_to_product ))

Regards

Jerry

Jeff Wyant wrote:
>
> Below is a sample of the code I am trying to execute. The problem seems
> to be with the between statements in the where clause. With the
> between
> statements the way they are this codes will take about a minute to
> execute. If I take the between statements out it will run about 2
> seconds. Then here is the weird part. If I put the between statements
> in
> and use literals instead of variables it will also run about 2
> seconds.
> Why is it taking so much longer with I use variables instead of
> literals?
> Your suggestions are appreciated.
>

        code omitted

-- 
Jerry Gitomer		Since I know how to spell DBA I became one. 
jgitomer_at_p3.net