Optimizing SQL query

[CHUMPY]

Hello All,

This is an example of a query I am opening as ref_cursor in a stored procedure that runs on an Oracle 9i box.

the structure of the tables are;

readings (approx >100 million records, index spid,dateofread)
spid varchar2
dateofread date
value NUMBER
edval varchar2

met_driect (approx >100,000 records, index area)

area varchar2
spid varchar2

There is a one to many relationship between met_direct.spid=readings.spid.

I run the following query to reurn the last date when all the spids for a given area in met_direct have a record in the readings table.


SELECT MAX(dateofread) FROM (SELECT dateofread FROM (SELECT dateofread FROM READINGS
WHERE
SPID IN (SELECT SPID FROM met_direct WHERE area_id='54802' GROUP BY spid))
GROUP BY dateofread
HAVING COUNT(*)=
(SELECT COUNT(*) FROM met_direct WHERE area_id='54802'));

Is there a better way of constructing the above query to return this date?

Any help much appreciated.

Gareth

 

[LKBrwnDBA]

Don't know your tables, but you could try something like this:

SELECT MAX(M.dateofread)
FROM (SELECT SPID, dateofread, COUNT(*) CNT FROM READINGS
WHERE area_id='54802' GROUP BY spid, dateofread) R
, (SELECT SPID, dateofread, COUNT(*) CNT FROM met_direct
WHERE area_id='54802' GROUP BY spid, dateofread) M
WHERE R.SPID = M.SPID
AND R.dateofread = M.dateofread
AND R.CNT = M.CNT;

PS: Compare explain plans first!




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[sem]

Can you explain exactly what you need? Can you provide excution plan along with optimizer mode? The same query may produce different plans.

Regards, Dima

 

[CHUMPY]

Hi Dima,

Below is a copy of the execution plan and the optimizer mode is choose. The area_id will return several records from the met_direct table each with a unique spid, this field then realates to the readings table.

The query returns the last date when a reading for all spids listed in the met_direct for a given area_id table are present, if that makes sense?

The problem is the query at present scans all records over all time for a given spid and this will only get worse as time goes on! Any suggestions for improving the perfomance greatly recieved!


Regards

Gareth



Date: 13-Aug-03 00:00:00


SQL Statement:

SELECT max(dateofread)
FROM (SELECT dateofread
FROM (SELECT *
FROM rps_readings
WHERE spid IN (SELECT spid
FROM rps_met_direct
WHERE area_id = '54802'
GROUP BY spid))
GROUP BY dateofread
HAVING count(*) = (SELECT count(*)
FROM rps_met_direct
WHERE area_id = '54802'))


Optimizer Mode Used:

COST ALL ROWS (optimizer: CHOOSE)

Total Cost:

1,001

Execution Steps:

Step # Step Name
12 SELECT STATEMENT
11 SORT [AGGREGATE]
10 . VIEW
9 FILTER
6 SORT [GROUP BY]
5 NESTED LOOPS
3 . VIEW
2 SORT [GROUP BY]
1 PRFADMIN.INDX1_MET_DIRECT INDEX [RANGE SCAN]
4 PRFADMIN.INDX_READINGS INDEX [RANGE SCAN]
8 SORT [AGGREGATE]
7 PRFADMIN.INDX1_MET_DIRECT INDEX [RANGE SCAN]

Step # Description Est. Cost Est. Rows Returned Est. KBytes Returned
1 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index INDX1_MET_DIRECT. 2 2 0.031
2 This plan step accepts a set of rows from its child node, and sorts them into groups based on the columns specified in the query's GROUP BY clause. 2 2 0.031
3 This plan step represents the execution plan for the subquery defined by the view . 2 2 0.016
4 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index INDX_READINGS. 362 40,935 599.634
5 This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 728 81,870 1,838.877
6 This plan step accepts a set of rows from its child node, and sorts them into groups based on the columns specified in the query's GROUP BY clause. 1,001 819 18.396
7 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index INDX1_MET_DIRECT. 2 2 0.016
8 This plan step accepts a row set (its only child) and returns a single row by applying an aggregation function. -- 1 0.008
9 This plan step accepts multiple sets of rows. Rows from the first set are eliminated using the data found in the second through n sets.
10 This plan step represents the execution plan for the subquery defined by the view . 1,001 819 7.198
11 This plan step accepts a row set (its only child) and returns a single row by applying an aggregation function. -- 1 0.009
12 This plan step designates this statement as a SELECT statement. 1,001 -- --

 

[zephan]

Hi,
Would you try this SQL :

SELECT MAX(dateofread) FROM
(
SELECT dateofread, sum(decode(nvl(R.spid, 'NONE'), 'NONE', 0, 1)), COUNT(D.spid)
FROM met_direct D, readings R
WHERE R.spid (+) = D.spid AND D.area = '54802'
GROUP BY dateofread
HAVING sum(decode(nvl(R.spid, 'NONE'), 'NONE', 0, 1)) = COUNT(D.spid)
)

Could you feed back on the performance please ?