Hi,
null values are not indexed in a single column index. If you want to
create an Index inluding null values in 9i I suggest using a constant as
first column e.g. (1, send_date). No rewrite is required.
Thanks
Lothar
Am 11.01.2024 um 00:18 schrieb Osman DINC:
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.
