Maybe you can try to re-write the query like this:
SELECT t1.series_id, max(t1.data_ts), "PRICE", "EPS"
FROM sers_data t1, (SELECT series_id, max(val_qty) "PRICE" FROM
sers_data
WHERE data_typ_cd='S013' GROUP BY series_id) t2, (SELECT series_id,
max(val_qty) "EPS" FROM sers_data WHERE data_typ_cd='S047' GROUP BY
series_id) t3
WHERE t1.series_id=t2.series_id
AND t1.series_id=t3.series_id
AND orign_typ_cd='SDOA'
GROUP BY t1.series_id, "PRICE", "EPS";
Hope this helps.
Michael Serbanescu
Mark Graham wrote:
>
> 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 Sun Jul 20 1997 - 00:00:00 CDT
