You can force Oracle to use specific indexes or join methods using hints. It's been a couple of years since I've done heavy Oracle work, but I think it's possible that the 'between' may not use the indexes if it doesn't know how many values it's going to return (as if its returns more than 5-20% (number dependant on oracle version) of the table, it's going to be faster to do a full table scan for a typical table) Generating histograms can help, but if you have skewed data, the optimizer won't know the best situation for the bind variables.

One thing that looks odd to me is that you've got two ranges on the same table, with a different status flag -- if they are the same range, I'd suggest instead using:

WHERE (
    rec_status IN ( 'A', 'I' )
  AND
    ucsiiu_receipt_at_mot_dt BETWEEN TO_DATE( ?, 'mm/dd/yy') AND TO_DA
+TE( ?, 'mm/dd/yy' )
)

[download]

(if they were the same range, Oracle could sort it out when it generates the execution plan ... if it couldn't, it has to do two queries, then merge them, which might mean a sort-merge, which is very slow for large sets)

If this is a common query, you might also make a special index for it, that contains both columns.

In reply to Re: Excessive execution time passing parameters to Oracle by jhourcle
in thread Excessive execution time passing parameters to Oracle by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.