By default null values are not indexed.
If you plan to query null values , you may create a functional index like
Nvl(send_date,to_date(
‘01.01.1990’,’DD.MM.YYYY’))
This index will record null values as 01.01.1990.
Now rewrite your query ‘s null filter as:
Nvl(t.send_date,to_date(
‘01.01.1990’,’DD.MM.YYYY’))=
to_date(
‘01.01.1990’,’DD.MM.YYYY’)
With this query you are filtering the nulls you are looking for.
Also apply this logic to the outer select with t1.
Hope it helps.
Osman DİNÇ
Insanedba
https://insanedba.blogspot.com
11 Oca 2024 Per, saat 01:58 tarihinde Sandra Becker <sbecker6925@xxxxxxxxx>
şunu yazdı:
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.
