| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Group and Count Performance Issue challenge
I have been challenged to optimize a stored procedure on Oracle 9i
that will return a ref cursor with the counts of each status for each
batch from the tables defined below. I started with a View to join
the tables together on the OrderNo field. Then I wrote a query in the
stored procdure that grouped the Batch values together, and then did a
count for each Status value in each Batch like this:
SELECT Batch,
COUNT(Batch) Total,
(SELECT COUNT(Batch)
FROM MYVIEW
WHERE Status = 1
AND Batch = V1.Batch
(SELECT COUNT(Batch)
FROM MYVIEW
WHERE Status = 2
AND Batch = V1.Batch
(SELECT COUNT(Batch)
FROM MYVIEW
WHERE Status = 3
AND Batch = V1.Batch
GROUP BY Batch;
With 42 unique Batch values and 26,000 OrderNo values my dev server takes over 5 seconds. I built the query without the view and added the joins in the query and took the same amount of time. I can't change the table structures but the view is wide open. This is a conversion project from MS SQL and this same data returns from MS SQL in 0.09 seconds. I hope Oracle can beat MS
Glamar
TableOne Definition:
Batch - VarChar
OrderNo - VarChar
Various other Columns of Data...
TableTwo Definition:
OrderNo - VarChar
Status - Number
Various other Columns of Data...
View Definition:
Select o.Batch, o.OrderNo, t.Status, ...
FROM TableOne o Left Outer Join TableTwo t
on o.OrderNo = t.OrderNo;
Sample Data TableOne:
ABC1 123 ...
ABC1 234 ...
ABC1 345 ...
ABC1 456 ...
ABC2 567 ...
ABC2 678 ...
ABC2 789 ...
ABC2 321 ...
ABC3 432 ...
ABC4 543 ...
Sample Data TableTwo:
123 1 ...
234 1 ...
345 2 ...
456 3 ...
567 2 ...
678 1 ...
789 2 ...
321 2 ...
432 3 ...
543 3 ...
Expected Return Ref Cursor:
ABC1 4 2 1 3
ABC2 4 1 3 null
ABC3 1 null null 1
ABC4 1 null 1 null
Received on Tue Feb 13 2007 - 09:56:50 CST
 |
 |