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
/
Two queries:
select count(*) from t1 where t1.send_date is null;
select count(*) from t1 where t1.send_date is not null;
would be useful metrics.
Using null send_date smacks of an overloaded date and action code.
IF that is not correct the rest of my note is only coincidentally useful (if
useful at all).
IF that is true, probably what you want to do is add a column do_not_send_yet.
(do_not_send_yet should be a bit if Oracle had single bit variables, I think
small numbers is still the smallest storage we can take up.)
So make it a number, nullable, on insert value 1, with all the current rows set
to 1 if send_date is null and null otherwise.
Index it.
Whenever and wherever send_date is populated, punch do_not_send_yet to null
(meaning okay to send You can define a meaning for null in a particular column
whilst Oracle cannot and this is precisely in order to create a “sparse index”
which has worked since at least Oracle 5. If you punch the value to 0 instead
of null, it will be needlessly large.)
select *
from aps1.txn_proc t1
where t1.tx_ref not in
(select t.tx_ref
from aps1.txn_proc t
where t. do_not_send_yet = 1
and t.exp_cmnt not like 'PR%'
and t.exp_cmnt != projno||tno
)
and t1. do_not_send_yet =1
/
Whether or not this prunes enough to ignore JL’s idea and figuring out how to
do that with dynamic values is a question of data.
As a “something to beat” easy implementation of JL’s idea for dynamic values
(so you don’t have PS in hand for the passed in PR),
Make the first pruning (do_not_send_yet=1) an inline view. (whether and
t.exp_cmnt != projno||tno is better in the first pruning inline view or the
second is a question of data, I’ll pretend that IS in the inline view, because
I have to type less that way).
From the inline view, select
where t.exp_cmnt < ‘PR’
or (t.exp_cmnt >= 'PR' and t.exp_cmnt not like 'PR%')
Now if you can’t add a column (or can add the column but can’t manage punching
it to null or even 0 when the send_date is set, you CAN do something similar
by making send_date “high values” on insert and setting all the existing
send_date values that are null to “high_values.” So that MEANS don’t send it
yet.
Then your index would be send_date, exp_cmnt
and the query
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 = high_values
and ( t.exp_cmnt < ‘PR’
or (t.exp_cmnt >= 'PR' and t.exp_cmnt not like 'PR%')
)
and t.exp_cmnt != projno||tno
)
and t1.send_date = high_values
/
(using whatever literal is high enough for high_values. If memory serves Oracle
does have a highest possible date value that takes no more space than any other
not null date. [which is quite different for default large number high values.]
That should not require any maintenance code change when send_date is set. You
might have other code that depends on send_date being null. That would be a
potential problem.
Good luck. I *think* all of that is right but I didn’t even start a database
today, let alone 9i.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Sandra Becker
Sent: Thursday, January 11, 2024 8:55 AM
To: jlewisoracle@xxxxxxxxx
Cc: oracle-l
Subject: 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@xxxxxxxxx> 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@xxxxxxxxx> 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.
