Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Group and Count Performance Issue challenge

Re: Group and Count Performance Issue challenge

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 13 Feb 2007 08:36:43 -0800
Message-ID: <1171384603.621577.65900@v45g2000cwv.googlegroups.com>


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_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.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Feb 13 2007 - 10:36:43 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US