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

On Feb 13, 10:56 am, glama...@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(DECO DE(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.

Charles Hooper schrieb:
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(DECO DE(STATUS,
> 3,1,NULL))) TOTAL_3
> FROM
> MYVIEW V1
> WHERE
> STATUS < 4
> GROUP BY
> BATCH;
>

> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
>

Charles, count(1) and count(batch) may yield different results.
I would suggest a minor modification

SELECT
BATCH,
COUNT(BATCH) TOTAL,
nullif(COUNT(DECODE(STATUS,1,batch,NULL)),0) TOTAL_1,
nullif(COUNT(DECODE(STATUS,2,batch,NULL)),0) TOTAL_2,
nullif(COUNT(DECODE(STATUS,3,batch,NULL)),0) TOTAL_3
FROM
MYVIEW V1
WHERE
STATUS < 4
GROUP BY
BATCH;


Best regards

Maxim

On Feb 13, 11:58 am, Maxim Demenko <mdeme...@arcor.de> wrote:
> Charles Hooper schrieb:
> 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(DECO DE(STATUS,
> > 3,1,NULL))) TOTAL_3
> > FROM
> > MYVIEW V1
> > WHERE
> > STATUS < 4
> > GROUP BY
> > BATCH;
>
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.
>
> Charles, count(1) and count(batch) may yield different results.
> I would suggest a minor modification
>
> SELECT
> BATCH,
> COUNT(BATCH) TOTAL,
> nullif(COUNT(DECODE(STATUS,1,batch,NULL)),0) TOTAL_1,
> nullif(COUNT(DECODE(STATUS,2,batch,NULL)),0) TOTAL_2,
> nullif(COUNT(DECODE(STATUS,3,batch,NULL)),0) TOTAL_3
> FROM
> MYVIEW V1
> WHERE
> STATUS < 4
> GROUP BY
> BATCH;
>
> Best regards
>
> Maxim- Hide quoted text -

Maxim, good catch. COUNT(BATCH) and COUNT(1) could be different
values if null values are permitted in the BATCH column.

If nulls are not permitted in the BATCH column, this should also work:
SELECT
BATCH,
SUM(1) TOTAL,
SUM(DECODE(STATUS,1,1,NULL)) TOTAL_1,
SUM(DECODE(STATUS,2,1,NULL)) TOTAL_2,
SUM(DECODE(STATUS,3,1,NULL)) TOTAL_3
FROM
MYVIEW V1
WHERE
STATUS < 4
GROUP BY
BATCH;

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

On Feb 13, 7:56 am, glama...@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

"Beat" is a relative term. Some things you might want to note:

Oracle is not MS SQL.

Timing done on one run may not reflect timing done on several runs.
Part of the reason for this is Oracle is biased towards giving
multiple
users their own views of the database, based on when transactions
start relative to one another. Another part of the reason is Oracle
has
algorythms to keep more-used data in memory. So while MS-SQL
may "beat" Oracle counting a few hundred pieces of data on a PC
off a SATA drive, the situation may be different when ten thousand
users want the same few hundred pieces of data.

Oracle may be able to count the data from an index. Do you have
an index? Would the index be smaller than the table? Would a
substantial fraction of the data be null? Oracle can also "skip-scan"
an index, which means ignore a leading field.

Oracle performance tuning uses explain plans, which tell how the
optimizer is getting at the data. You want to understand the various
ways to access the data and what influences the optimizer. The
basics are explained in the performance tuning guide, and there
are other resources available when this becomes deep. In general,
in this usenet hierarchy any performance question is likely to be
answered with a request to show plan (from a built-in procedure) or
trace (for serious tuning) output. You should run this for your code
and Charles', the results may be informative.

The biggest dependency for the optimizer (after the code, of course)
is statistics, please state how and when you have generated them.
The details also vary by version, so it is important to state the
exact
version of Oracle, as well as, in general, your platform/OS.

There is a context switch going from SQL to PL/SQL. Also, the
SQL engine is highly optimized for relational or set-based queries,
so a generally good rule of thumb is to do anything you can in SQL
rather than PL/SQL, if you can. This is why Charles' decode example
is likely to work well. Not for this example, but in other cases
UNION
is the way to go.

Oracle is also biased towards the same exact code being run over
and over, so that is the basis of statements like "bind variables
are good" and "you automatically have bind variables when..."

Oracle uses latching code to control who has access to what
data where, so if your code is actually going to do something
like keep header information in the first row of a table and then
have a lot of people fight over it, you may have some redesign
work to do.

Oracle has views, but it also has Materialized Views. That's
another way of saying you can pre-digest some data for some
purposes.

Some people here want your data and code to come with
actual create table and insert statements to put the test
data together, as well as complete stored procedure code.
(Personally, I don't care, that's just a statement of some
expectations, makes it easy for people to play around.
I know I am more likely to work on something in such a
situation).

jg
--
@home.com is bogus.
http://theinvisiblethings.blogspot.c...every-day.html

Charles,

That did it, brought it down to under a second. Thanks for the help.

Glamar