Re: Performance issue on Oracle 9i

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_at_gmail.com> ş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.
>
>

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