This should be easy.....

From: Mark Graham <mlgraham_at_bellsouth.net>
Date: 1997/07/18
Message-ID: <01bc93a8$a004cff0$776a48a6@smiley>#1/1

Recently I was asked to help create an adhoc report for a system that was being converted from a DOS system to oracle 7.3 on NT. The requirements for the adhoc report was to perform at least equally as well as the DOS system. After my first attempt at duplicating the report I did not even come close to meeting the performance specs and I was hoping for some advice.

The developers who set up the DOS system where quite clever and managed to build a system which performed well using proprietary indexing and search algorithms. The elapsed time it takes to produce a typical report is around 5 seconds. The base table for the report is 10,000,000 rows.

The report is supposed to select all the latest stock values and transpose the table using one of the fields (ie. the use of decodes on the data_typ_cd column). The result of the report is 13,000 rows which have the series_id (stock number), the date of when the stock was last captured, the price and earnings information. The table and core report SQL that I was playing around with is below:

select series_id, max(data_ts), 
max(decode(DATA_TYP_CD),'S013',val_qty)) "PRICE",
max(decode(DATA_TYP_CD),'S047',val_qty)) "EPS",

from sers_data
where orign_typ_cd = 'SD0A'
group by series_id

The time to complete this query is about 10 minutes using a full table scan and I haven't been able to do anything with indexes to improve performance so far. I am considering putting a request in for faster disks and using parralel queries. Any suggestions would be appreciated.

Mark
mlgraham_at_bellsouth.net Received on Fri Jul 18 1997 - 00:00:00 CDT