Re: Performance issue on Oracle 9i

Are you allowed to rewrite the query or do you have to live with it and tweak the database.
How long does it take to run (pre- and post- index creation), and what did the two execution plans look like.
Was the index on Just the send_date, or was it on (send_date, some non-null declared column). If the former then it shouldn't have made any difference to the plan (in the general case - but there are some "exotic" anomalies) which would suggest that the improvement was from side effects (like better statistics, or the effects of blocks being cleaned out during the index build).

If you can rewrite the query there are several possibilities, but the best strategy depends on know the effects of each of the data predicates and combinations of predicates. For example, how many rows have a null send_date, how many rows have exp_cmnt != projno||tno. (It would be better to include the "t." table alias in that predicate - even though it should make no difference in this case (unless you've been getting the wrong results because it was supposed to be a predicate correlating to "t1.".))

Example step - if rewrites are allowed and necessary:

    t.exp_cmnt not like 'PR%'
is equivalent to

    t.exp_cmnt >= 'PS' or t.exp_cmnt < 'PR'

(Note: greater than or equal to, strictly less than) This means the subquery could be rewritten as a UNION ALL of two query blocks that could operate through an index.

Regards
Jonathan Lewis

On Wed, 10 Jan 2024 at 22:58, Sandra Becker <sbecker6925_at_gmail.com> wrote:

> OS: SunOS 5.8
> DB: Oracle 9.2.0.5
>
> We're sitting on really old hardware with a really old version of Oracle.
> There is a project to migrate to another application, but they estimate it
> will take another 18 months given the human resources that are available.
> That being said, this is a production financial application and is
> performing extremely poorly for queries against a specific table. It's not
> a huge table, 388,000 rows, but given the age of the hardware/software, I'm
> surprised we don't have more issues.
>
> The query itself is poorly written, but I haven't figured out how to make
> it more efficient. I did manage to reduce the cost and execution time of
> the query by 50% by creating an index on the SEND_TO_DATE column--which can
> contain nulls--but it's still very slow. I also set the degree on the
> table to 8, which gave us a minor bump in performance. Any suggestions
> would be appreciated, specifically on how I can change the "not like" and
> "!=" predicates.
>
> select *
> from aps1.txn_proc t1
> where t1.tx_ref not in
> (select t.tx_ref
> from aps1.txn_proc t
> where t.send_date is null
> and t.exp_cmnt not like 'PR%'
> and t.exp_cmnt != projno||tno
> )
> and t1.send_date is null
> /
>
>
> Thank you,
> --
> Sandy B.
>
>

--
http://www.freelists.org/webpage/oracle-l