Re: Oracle Performance

Bill Roney wrote:
>
> I'm having a problem with a 7.2.3 server. It is running on an NCR system with 8 P133s and 3GB of
> physical memory (sorry, I don't know the model number) running AT&T UNIX.
>
> I have 2 tables that form a master detail relationship, the general structure of the tables are as
> follows:
>
> 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)
>
> The sizes of these tables are 2.5 million rows in T1, and 6 million rows in T2.
>
> 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
>
> The result set returns 20 minutes later, with the sar report during the period reporting 0% idle
> time.
>
> I have a requirement that the result set be built < 5 minutes. Is this possible based upon anyone's
> experience? I have been told that the requirement is not possible.
>
> On a side note, selecting just the rows from T1 (no join) takes 3 minutes. This would imply that I
> may be able to perform the join on the client faster than the join being done by the RDBMS?!?
>
> Any help or insight would be greatly appreciated.
>
> Thanks in advance.
> BillBill, two suggestions:

1. Run your query through tkprof & explain plan to get an idea why you're doing a full table scan on the driving table, and
2. Use the TO_DATE function to convert your date strings to the DATE datatypes to avoid an implicit datatype conversion in the where constraint that would preclude the use of an index on the date columns (they are indexed, right?) -- Rich Woods Technical Field Support Specialist, Oracle Corporation, USA The above statements and opinions are my own and do not necessarily represent those of Oracle Corporation.