Re: Oracle Performance

Bill Roney <bill.roney_at_mailport.delta-air.com> wrote:

[snip]
>table T1
>(
> fielda char(5) not null,
> fieldb date not null,
> fieldc number not null,
>...
>) (row with is around 250 bytes)
>table T2
>(
> fielda char(5) not null,
> fieldb date not null,
> fieldc number not null,
> fieldd char(1) not null,
>...
>) (row with is around 150 bytes)
 

>These 2 tables are joined with the following where and order by clauses
> where T1.fielda = T2.fielda
> and T1.fieldb = T2.fieldb
> and T1.fieldc = T2.fieldc
> and T1.fieldb >= '[a start date]'
> and T1.fieldb <= '[an end date]'
> order by T2.fielda, T2.fieldb, T2.fieldc, T2.fieldd

You dont mention wether or not you have created idexes for the tables, but i guess you have.
If you haven't you should do so, and make certain that all fields in the were clause are represented in the index.

If you allways access a row in T2 when you access a row in T1(also elswhere, not just in this script) you could cluster the tables.

As a last resort you could make a third table with denormalized data. This requires daily (nightly)/ or whatever updates of the table, and would therefore not always represent actual data at runtime. Received on Sun Sep 01 1996 - 00:00:00 CEST