Can this range be combind into one?

Based on what you're saying, no. (you may get slightly different performance using a 'UNION ALL', as it doesn't have to perform the sort-merge, but if you're using any aggregate functions, you'll erase the benefit) (you won't get duplicates as REC_STATUS wouldn't match between the two sub-queries)

Could you please explain me little bit more on special index which you mentioned. both the column mean, which columns?

Sure -- instead of specifying indexes in with the individual column definitions, you do the following:

CREATE INDEX index_name ON table_name ( column1, column2 column3 )

(this also allows you to specify an alternate tablespace for your index, to reduce disk contention on writes)

Now, if you run queries that act on those columns, it can use the single index, rather than needing to merge between multiple indexes. However, index design is tricky -- if you're just searching on a single column, this could be worse than a single column index on that value. It's probably more than I can really go into, and this isn't really Perl related anymore ... try searching for 'multicolumn index' or 'composite index')

...

Oh -- and it seems that the use of bind variables and histograms may cause problems I wasn't aware of -- since 9i, Oracle does 'bind variable peeking', (ie, it looks at the bind variables to determine the best execution plan). Unfortunately, it then caches the plan, and it may be optimized for an outlier case (the first one run). Some folks are actually recommending not using histograms on skewed data

In reply to Re^3: 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.