Re: Performance issue on Oracle 9i

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