May 26, 2011
In a previous article I showed a simple method to convert values found in multiple table rows into a comma delimited list. The method works very well, until the situation in which the approach is to be used becomes a bit more complex.
Assume for a moment that the following table structure exists where the approach must be implemented:
In the above diagram, the PURCHASE_ORDER table is the parent table, the PURC_ORDER_LINE table is the child table (the line-level detail), and the PURC_LINE_DEL table is the grand-child table (the sub-line level detail). A fairly simple setup, and I suspect not a too terribly uncommon setup.
Now the complexity. Assume that an ERP package using the above tables generates a report using the tables’ data, and as the ERP package is flexible, it allows additional information to be added to the reports – essentially, it is possible to execute a query that retrieves a single row for every detail-level row in the report. Great, except that in this particular report, the detail-level row level is at the PURC_ORDER_LINE table. If there were 18 rows, for instance, in the PURC_LINE_DEL table (the schedule that indicates when the parts should be delivered) for the first order line in the PURC_ORDER_LINE table for a particular order ID that is found in the PURCHASE_ORDER table, then we have a bit of a problem. Fortunately, this ERP package’s report was constructed such that the DESIRED_RECV_DATE and USER_ORDER_QTY columns are represented in two report variables with CRLF (carriage return – line feed) delimited lists, making it very easy to list the delivery dates that the parts are due, and the quantities that are due for each of those dates. Fantastic, so where is the complexity? What if the purchase order must be revised after the supplier starts sending the parts on the agreed upon delivery dates, such that the first 9 of the 18 delivery schedules for line 1 of a purchase order have been satisfied, 1 of the 18 delivery schedule lines has been partially satisfied, and the remaining 8 delivery schedule lines have dates that are in the future? This might be helpful information to provide to the supplier when presenting the updated purchase order. Unfortunately, the ERP package’s report does not deliver the USER_RECEIVED_QTY column as a CRLF delimited list, as it does for the DESIRED_RECV_DATE and USER_ORDER_QTY columns.
A complex problem, or not? Well, the ERP package does permit a SQL statement returning a single row to be executed per detail row in the report (at the PURC_ORDER_LINE table level), so we have a possible solution. However, if the SQL statement is too complex (having inline views is one such complication), the complex portion of the SQL statement must be isolated in a statically defined view, and then that view should be referenced by the report. A complex problem, or not? Let’s try building a solution starting in SQL*Plus:
SELECT
PURC_ORDER_ID,
PURC_ORDER_LINE_NO,
USER_RECEIVED_QTY,
ROW_NUMBER() OVER (PARTITION BY PURC_ORDER_ID,PURC_ORDER_LINE_NO ORDER BY DEL_SCHED_LINE_NO) RN
FROM
PURC_LINE_DEL
WHERE
PURC_ORDER_ID='146825'
AND PURC_ORDER_LINE_NO=1
ORDER BY
DEL_SCHED_LINE_NO;
PURC_ORDER_ID PURC_ORDER_LINE_NO USER_RECEIVED_QTY RN
--------------- ------------------ ----------------- ----------
146825 1 1 1
146825 1 1 2
146825 1 2 3
146825 1 2 4
146825 1 2 5
146825 1 3 6
146825 1 3 7
146825 1 1 8
146825 1 3 9
146825 1 2 10
146825 1 0 11
146825 1 0 12
146825 1 0 13
146825 1 0 14
146825 1 0 15
146825 1 0 16
146825 1 0 17
146825 1 0 18
Simple so far, that line identified by RN 10 requests an order quantity of 3, but the supplier only delivered 2 of the 3 parts for that date. Now let’s generate a comma separated list using the SYS_CONNECT_BY_PATH function:
SELECT
SYS_CONNECT_BY_PATH(TO_CHAR(USER_RECEIVED_QTY),',') LISTING
FROM
(SELECT
PURC_ORDER_ID,
PURC_ORDER_LINE_NO,
USER_RECEIVED_QTY,
ROW_NUMBER() OVER (PARTITION BY PURC_ORDER_ID,PURC_ORDER_LINE_NO ORDER BY DEL_SCHED_LINE_NO) RN
FROM
PURC_LINE_DEL
WHERE
PURC_ORDER_ID='146825'
AND PURC_ORDER_LINE_NO=1
ORDER BY
DEL_SCHED_LINE_NO) V
CONNECT BY PRIOR
RN = RN-1
START WITH
RN = 1;
LISTING
------------------------------------
,1
,1,1
,1,1,2
,1,1,2,2
,1,1,2,2,2
,1,1,2,2,2,3
,1,1,2,2,2,3,3
,1,1,2,2,2,3,3,1
,1,1,2,2,2,3,3,1,3
,1,1,2,2,2,3,3,1,3,2
,1,1,2,2,2,3,3,1,3,2,0
,1,1,2,2,2,3,3,1,3,2,0,0
,1,1,2,2,2,3,3,1,3,2,0,0,0
,1,1,2,2,2,3,3,1,3,2,0,0,0,0
,1,1,2,2,2,3,3,1,3,2,0,0,0,0,0
,1,1,2,2,2,3,3,1,3,2,0,0,0,0,0,0
,1,1,2,2,2,3,3,1,3,2,0,0,0,0,0,0,0
,1,1,2,2,2,3,3,1,3,2,0,0,0,0,0,0,0,0
We only want the last row returned from the above result, and we need to change those commas to a CRLF character combination (after eliminating the initial comma):
SELECT
MAX(REPLACE(SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(USER_RECEIVED_QTY),','),2),',',CHR(13)||CHR(10))) LISTING
FROM
(SELECT
PURC_ORDER_ID,
PURC_ORDER_LINE_NO,
USER_RECEIVED_QTY,
ROW_NUMBER() OVER (PARTITION BY PURC_ORDER_ID,PURC_ORDER_LINE_NO ORDER BY DEL_SCHED_LINE_NO) RN
FROM
PURC_LINE_DEL
WHERE
PURC_ORDER_ID='146825'
AND PURC_ORDER_LINE_NO=1
ORDER BY
DEL_SCHED_LINE_NO) V
CONNECT BY PRIOR
RN = RN-1
START WITH
RN = 1;
LISTING
-------
1
1
2
2
2
3
3
1
3
2
0
0
0
0
0
0
0
0
Looks like mission accomplished, all of the previously received quantities are returned in a single query row… except now we have a complicated query that must be isolated in a statically defined view (silly rules…).
Before we head down the path of isolating the complex portion of the query, I thought that it might be entertaining to examine a couple of execution plan to see what happens when we isolate the complicated portion of the query in a statically defined view. First, we will take a look at the last of the above SQL statements:
SET LINESIZE 140
SET TRIMSPOOL ON
SET PAGESIZE 1000
SET AUTOTRACE TRACEONLY EXPLAIN
SELECT
MAX(REPLACE(SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(USER_RECEIVED_QTY),','),2),',',CHR(13)||CHR(10))) LISTING
FROM
(SELECT
PURC_ORDER_ID,
PURC_ORDER_LINE_NO,
USER_RECEIVED_QTY,
ROW_NUMBER() OVER (PARTITION BY PURC_ORDER_ID,PURC_ORDER_LINE_NO ORDER BY DEL_SCHED_LINE_NO) RN
FROM
PURC_LINE_DEL
WHERE
PURC_ORDER_ID='146825'
AND PURC_ORDER_LINE_NO=1
ORDER BY
DEL_SCHED_LINE_NO) V
CONNECT BY PRIOR
RN = RN-1
START WITH
RN = 1;
Plan hash value: 929864805
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 4 (25)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | CONNECT BY NO FILTERING WITH START-WITH| | | | | |
| 3 | VIEW | | 4 | 104 | 3 (0)| 00:00:01 |
| 4 | WINDOW NOSORT | | 4 | 76 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | PURC_LINE_DEL | 4 | 76 | 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | SYS_C0021790 | 4 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("RN"-1=PRIOR "RN")
filter("RN"=1)
6 - access("PURC_ORDER_ID"='146825' AND "PURC_ORDER_LINE_NO"=1)
The above looks simple, and the query really does execute quickly. For the above complex portion of the query to work in a statically defined view, we need to relocate the WHERE clause outside of the inline view, so that when the report runs, it is able to pass in the appropriate values for the PURC_ORDER_ID and PURC_ORDER_LINE_NO columns. Here is the execution plan (generated on Oracle Database 11.2.0.2) when the WHERE clause is relocated:
SELECT
MAX(REPLACE(SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(USER_RECEIVED_QTY),','),2),',',CHR(13)||CHR(10))) LISTING
FROM
(SELECT
PURC_ORDER_ID,
PURC_ORDER_LINE_NO,
USER_RECEIVED_QTY,
ROW_NUMBER() OVER (PARTITION BY PURC_ORDER_ID,PURC_ORDER_LINE_NO ORDER BY DEL_SCHED_LINE_NO) RN
FROM
PURC_LINE_DEL
ORDER BY
DEL_SCHED_LINE_NO) V
WHERE
PURC_ORDER_ID='146825'
AND PURC_ORDER_LINE_NO=1
CONNECT BY PRIOR
RN = RN-1
START WITH
RN = 1;
Plan hash value: 2107147172
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48 | | 1193 (50)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | 48 | | | |
|* 2 | FILTER | | | | | | |
|* 3 | CONNECT BY NO FILTERING WITH START-WITH| | | | | | |
| 4 | VIEW | | 53851 | 2524K| | 625 (3)| 00:00:03 |
| 5 | SORT ORDER BY | | 53851 | 999K| 1704K| 625 (3)| 00:00:03 |
| 6 | WINDOW SORT | | 53851 | 999K| 1704K| 625 (3)| 00:00:03 |
| 7 | TABLE ACCESS FULL | PURC_LINE_DEL | 53851 | 999K| | 57 (8)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PURC_ORDER_ID"='146825' AND "PURC_ORDER_LINE_NO"=1)
3 - access("RN"-1=PRIOR "RN")
filter("RN"=1)
The above looks to be reasonably sane, although the predicted amount of time jumped from about 1 second to about 5 seconds. The Predicate Information section of the execution plan indicates that the filter for the specified PURC_ORDER_ID and PURC_ORDER_LINE_NO was not pushed into the inline view as I had hoped. The actual execution time for this simple query is much longer than 5 seconds, spending 100% of its time burning CPU time (I killed the query after waiting several minutes). No problem, I will just use a hint to force the optimizer to push those predicates into the inline view:
SELECT /*+ PUSH_PRED(V) */
MAX(REPLACE(SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(USER_RECEIVED_QTY),','),2),',',CHR(13)||CHR(10))) LISTING
FROM
(SELECT
PURC_ORDER_ID,
PURC_ORDER_LINE_NO,
USER_RECEIVED_QTY,
ROW_NUMBER() OVER (PARTITION BY PURC_ORDER_ID,PURC_ORDER_LINE_NO ORDER BY DEL_SCHED_LINE_NO) RN
FROM
PURC_LINE_DEL
ORDER BY
DEL_SCHED_LINE_NO) V
WHERE
PURC_ORDER_ID='146825'
AND PURC_ORDER_LINE_NO=1
CONNECT BY PRIOR
RN = RN-1
START WITH
RN = 1;
Plan hash value: 2107147172
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48 | | 1193 (50)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | 48 | | | |
|* 2 | FILTER | | | | | | |
|* 3 | CONNECT BY NO FILTERING WITH START-WITH| | | | | | |
| 4 | VIEW | | 53851 | 2524K| | 625 (3)| 00:00:03 |
| 5 | SORT ORDER BY | | 53851 | 999K| 1704K| 625 (3)| 00:00:03 |
| 6 | WINDOW SORT | | 53851 | 999K| 1704K| 625 (3)| 00:00:03 |
| 7 | TABLE ACCESS FULL | PURC_LINE_DEL | 53851 | 999K| | 57 (8)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PURC_ORDER_ID"='146825' AND "PURC_ORDER_LINE_NO"=1)
3 - access("RN"-1=PRIOR "RN")
filter("RN"=1)
Well, the above is disappointing. I could dig through a 10053 trace to try to determine why the hint was “ignored“, or I could try another query that produces an equivalent result. I choose the latter. Let’s try using the COUNT analytic function to replace the ROW_NUMBER analytic function:
SELECT
MAX(REPLACE(SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(USER_RECEIVED_QTY),','),2),',',CHR(13)||CHR(10))) LISTING
FROM
(SELECT
PURC_ORDER_ID,
PURC_ORDER_LINE_NO,
DEL_SCHED_LINE_NO,
USER_RECEIVED_QTY,
COUNT(*) OVER (PARTITION BY PURC_ORDER_ID,PURC_ORDER_LINE_NO ORDER BY DEL_SCHED_LINE_NO) RN
FROM
PURC_LINE_DEL
WHERE
PURC_ORDER_ID='146825'
AND PURC_ORDER_LINE_NO=1
ORDER BY
DEL_SCHED_LINE_NO) V
CONNECT BY PRIOR
RN=RN-1
START WITH
RN=1;
Plan hash value: 617003335
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 4 (25)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | CONNECT BY NO FILTERING WITH START-WITH| | | | | |
| 3 | VIEW | | 4 | 104 | 3 (0)| 00:00:01 |
| 4 | WINDOW BUFFER | | 4 | 76 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | PURC_LINE_DEL | 4 | 76 | 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | SYS_C0021790 | 4 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("RN"-1=PRIOR "RN")
filter("RN"=1)
6 - access("PURC_ORDER_ID"='146825' AND "PURC_ORDER_LINE_NO"=1)
The execution plan appears to be reasonable – the predicates on the PURC_ORDER_ID and PURC_ORDER_LINE_NO columns are applied early, so the query executes quickly. Now to move the WHERE clause:
SELECT
MAX(REPLACE(SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(USER_RECEIVED_QTY),','),2),',',CHR(13)||CHR(10))) LISTING
FROM
(SELECT
PURC_ORDER_ID,
PURC_ORDER_LINE_NO,
DEL_SCHED_LINE_NO,
USER_RECEIVED_QTY,
COUNT(*) OVER (PARTITION BY PURC_ORDER_ID,PURC_ORDER_LINE_NO ORDER BY DEL_SCHED_LINE_NO) RN
FROM
PURC_LINE_DEL
ORDER BY
DEL_SCHED_LINE_NO) V
WHERE
PURC_ORDER_ID='146825'
AND PURC_ORDER_LINE_NO=1
CONNECT BY PRIOR
RN=RN-1
START WITH
RN=1;
Plan hash value: 2107147172
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48 | | 1193 (50)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | 48 | | | |
|* 2 | FILTER | | | | | | |
|* 3 | CONNECT BY NO FILTERING WITH START-WITH| | | | | | |
| 4 | VIEW | | 53851 | 2524K| | 625 (3)| 00:00:03 |
| 5 | SORT ORDER BY | | 53851 | 999K| 1704K| 625 (3)| 00:00:03 |
| 6 | WINDOW SORT | | 53851 | 999K| 1704K| 625 (3)| 00:00:03 |
| 7 | TABLE ACCESS FULL | PURC_LINE_DEL | 53851 | 999K| | 57 (8)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PURC_ORDER_ID"='146825' AND "PURC_ORDER_LINE_NO"=1)
3 - access("RN"-1=PRIOR "RN")
filter("RN"=1)
As had happened before, the predicates for the PURC_ORDER_ID and PURC_ORDER_LINE_NO columns were not pushed into the inline view, so the query executes extremely slow. Let’s try the PUSH_PRED hint again:
SELECT /*+ PUSH_PRED(V) */
MAX(REPLACE(SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(USER_RECEIVED_QTY),','),2),',',CHR(13)||CHR(10))) LISTING
FROM
(SELECT
PURC_ORDER_ID,
PURC_ORDER_LINE_NO,
DEL_SCHED_LINE_NO,
USER_RECEIVED_QTY,
COUNT(*) OVER (PARTITION BY PURC_ORDER_ID,PURC_ORDER_LINE_NO ORDER BY DEL_SCHED_LINE_NO) RN
FROM
PURC_LINE_DEL
ORDER BY
DEL_SCHED_LINE_NO) V
WHERE
PURC_ORDER_ID='146825'
AND PURC_ORDER_LINE_NO=1
CONNECT BY PRIOR
RN=RN-1
START WITH
RN=1;
Plan hash value: 2107147172
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48 | | 1193 (50)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | 48 | | | |
|* 2 | FILTER | | | | | | |
|* 3 | CONNECT BY NO FILTERING WITH START-WITH| | | | | | |
| 4 | VIEW | | 53851 | 2524K| | 625 (3)| 00:00:03 |
| 5 | SORT ORDER BY | | 53851 | 999K| 1704K| 625 (3)| 00:00:03 |
| 6 | WINDOW SORT | | 53851 | 999K| 1704K| 625 (3)| 00:00:03 |
| 7 | TABLE ACCESS FULL | PURC_LINE_DEL | 53851 | 999K| | 57 (8)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PURC_ORDER_ID"='146825' AND "PURC_ORDER_LINE_NO"=1)
3 - access("RN"-1=PRIOR "RN")
filter("RN"=1)
The execution plan is unchanged. Out of luck…
—
I remembered reading about a new analytic function introduced in Oracle Database 11.2.0.1, what was that new function… LISTAGG? I am not a fan of that function name, Oracle should have named the function __CREATE_A_DARN_COMMA_SEPARATED_LIST_QUICKLY (a double underscore function, making it a challenge to find the function). The LISTAGG function is described in the Oracle documentation library for 11.2. Let’s give the LISTAGG function a try (the SQL statement is also now formatting the USER_RECEIVED_QTY so that the column values consistently show 2 decimal places):
SELECT
LISTING
FROM
(SELECT
LISTAGG(TO_CHAR(USER_RECEIVED_QTY,'999,990.00'), CHR(13)||CHR(10)) WITHIN GROUP (ORDER BY DEL_SCHED_LINE_NO) LISTING
FROM
PURC_LINE_DEL
WHERE
PURC_ORDER_ID='146825'
AND PURC_ORDER_LINE_NO=1
GROUP BY
PURC_ORDER_ID,
PURC_ORDER_LINE_NO);
Plan hash value: 3148992471
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 3 (0)| 00:00:01 |
| 1 | SORT GROUP BY | | 1 | 19 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| PURC_LINE_DEL | 4 | 76 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SYS_C0021790 | 4 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PURC_ORDER_ID"='146825' AND "PURC_ORDER_LINE_NO"=1)
The calculated cost for the query dropped by 1 and the CPU component of that cost dropped to 0%, so that must be a good thing (the calculated cost can be misleading, just as we saw earlier that the estimated time can be misleading). Without AUTOTRACE TRACEONLY EXPLAIN enabled, the query executes very quickly and produces output that looks similar to this:
LISTING
-----------
1.00
1.00
2.00
2.00
2.00
3.00
3.00
1.00
3.00
2.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
OK, let’s re-enable AUTOTRACE TRACEONLY EXPLAIN and try moving the WHERE clause so that we are able to package the complex portion of this query into a statically defined view:
SELECT
LISTING
FROM
(SELECT
PURC_ORDER_ID,
PURC_ORDER_LINE_NO,
LISTAGG(TO_CHAR(USER_RECEIVED_QTY,'999,990.00'), CHR(13)||CHR(10)) WITHIN GROUP (ORDER BY DEL_SCHED_LINE_NO) LISTING
FROM
PURC_LINE_DEL
GROUP BY
PURC_ORDER_ID,
PURC_ORDER_LINE_NO) V
WHERE
PURC_ORDER_ID='146825'
AND PURC_ORDER_LINE_NO=1;
Plan hash value: 3148992471
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 3 (0)| 00:00:01 |
| 1 | SORT GROUP BY | | 1 | 19 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| PURC_LINE_DEL | 4 | 76 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SYS_C0021790 | 4 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PURC_ORDER_ID"='146825' AND "PURC_ORDER_LINE_NO"=1)
In the above, notice that the predicates for the PURC_ORDER_ID and PURC_ORDER_LINE_NO columns were pushed into the inline view, or more correctly, the inline view was completely eliminated by the query optimizer (notice that there is no line in the execution plan showing VIEW as we saw in the previous examples above).
OK, so Oracle’s query optimizer worked as I had hoped with the inline view, but what about a statically defined view – would it also execute the SQL statement efficiently? Let’s create the view:
CREATE VIEW TEST_LA AS
SELECT
PURC_ORDER_ID,
PURC_ORDER_LINE_NO,
LISTAGG(TO_CHAR(USER_RECEIVED_QTY,'999,990.00'), CHR(13)||CHR(10)) WITHIN GROUP (ORDER BY DEL_SCHED_LINE_NO) LISTING
FROM
PURC_LINE_DEL
GROUP BY
PURC_ORDER_ID,
PURC_ORDER_LINE_NO;
Now let’s try the query with AUTOTRACE TRACEONLY EXPLAIN enabled:
SELECT
LISTING
FROM
TEST_LA
WHERE
PURC_ORDER_ID='146825'
AND PURC_ORDER_LINE_NO=1;
Plan hash value: 3148992471
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 3 (0)| 00:00:01 |
| 1 | SORT GROUP BY | | 1 | 19 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| PURC_LINE_DEL | 4 | 76 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SYS_C0021790 | 4 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PURC_ORDER_ID"='146825' AND "PURC_ORDER_LINE_NO"=1)
The execution plan above shows the same cost (3) that we saw earlier, and once again the view was eliminated by Oracle’s query optimizer (as indicated by the absence of a VIEW line in the execution plan), allowing the query to execute quickly since the predicates would be applied early in the query execution.
—
So, Oracle Database 11.2.0.1 and above provide an efficient way to retrieve the information that we need in a comma (or CRLF) delimited list for the report. When the SQL statement is added to the report (defined as an “extended query” in the ERP package), the SQL statement looks like the following (the reporting package does not permit line breaks in the SQL statement):
SELECT (LISTAGG(TO_CHAR(USER_RECEIVED_QTY,'999,990.00'), CHR(13)||CHR(10)) WITHIN GROUP (ORDER BY DEL_SCHED_LINE_NO)) FROM PURC_LINE_DEL WHERE PURC_ORDER_ID= : PO_ID AND PURC_ORDER_LINE_NO= :LN_LINE_NO GROUP BY PURC_ORDER_ID , PURC_ORDER_LINE_NO
Oddly enough, by enclosing the LISTAGG analytic funtion in the SQL statement with ( ), the reporting package did not require me to use a statically defined view for the complex portion of the query. But even if the statically defined view was required, as shown above, the query would still have executed efficiently.
Charles Hooper's Oracle Notes 
Recent Comments