Re: Performance issue on Oracle 9i

Unfortunately, company policy prohibits me from providing the sql plan. I can tell you that pre-index the query was doing full table scans for both the query and subquery. Post-index build, it was using the index for both. I can create new indexes and/or rewrite the query. The sub-query returns 29k rows on average. The t.exp_cmnt not like replacement since it can be a combination of any two letters of the alphabet. I can play with that and see what I can come up with, possibly combining with the date column?

Thanks for your assistance.

Sandy

On Thu, Jan 11, 2024 at 6:05 AM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> 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.
>>
>>

-- 
Sandy B.

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