Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Group and Count Performance Issue challenge
On Feb 13, 10:56 am, glama..._at_hotmail.com wrote:
> 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
>
> Glamar
>
> ----------------
> 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_3FROM
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Tue Feb 13 2007 - 10:36:43 CST