December 4, 2009
(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)
Some time ago the following question appeared in a forum:
http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/7780f1fd94c03ced
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 FROM MYVIEW V1 WHERE Status < 4 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
Suporting Data and table structures.
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
Reduce it to a simple SQL statement using DECODE. If the STATUS is the expected value (1, 2, 3) for the column, return 1, otherwise return NULL. Then count the non-null return values. COUNT will only count non-null values:
SELECT BATCH, COUNT(BATCH) TOTAL, COUNT(DECODE(STATUS,1,1,NULL)) TOTAL_1, COUNT(DECODE(STATUS,2,1,NULL)) TOTAL_2, COUNT(DECODE(STATUS,3,1,NULL)) TOTAL_3 FROM MYVIEW V1 WHERE STATUS < 4 GROUP BY BATCH;
It appears that if COUNT returns 0, that you want NULL to be returned rather than 0. Once again, use DECODE to convert 0 to NULL and all other values to the original formula:
SELECT BATCH, COUNT(BATCH) TOTAL, DECODE(COUNT(DECODE(STATUS,1,1,NULL)),0,NULL,COUNT(DECODE(STATUS,1,1,NULL))) TOTAL_1, DECODE(COUNT(DECODE(STATUS,2,1,NULL)),0,NULL,COUNT(DECODE(STATUS,2,1,NULL))) TOTAL_2, DECODE(COUNT(DECODE(STATUS,3,1,NULL)),0,COUNT(DECODE(STATUS,3,1,NULL))) TOTAL_3 FROM MYVIEW V1 WHERE STATUS < 4 GROUP BY BATCH;
The inclusion of STATUS < 4 leads me to believe that you are attempting to reuse a view definition for a different task. Doing so may lead to performance issues. It may be a good idea to eliminate the view and directly reference the exact SQL statement of interest.
Recent Comments