Row Values to Comma Separated Lists, an Overly Complicated Use Case Example

26 05 2011

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.


Actions

Information

4 responses

27 05 2011
Log Buffer #222, A Carnival of the Vanities for DBAs | The Pythian Blog

[...] Charles Hooper blogs about an Overly Complicated Use Case Example regarding Row Values to Comma Separated Lists. [...]

27 05 2011
Narendra

Charles,

Let me first clarify that I am not trying to argue against the use of LISTAGG function. I love it and I would myself use it whenever possible. It just simplifies many things.
However, I guess there are a couple of things I would like to point out
1) The PUSH_PRED hint is applicable to only join predicates (why didn’t they call it PUSH_JOIN_PRED to avoid confusion?) as mentioned here. So it won’t make any difference to your query.
2) When you use SYS_CONNECT_BY_PATH to aggregate the string, you are effectively using hierarchical query feature of Oracle. One of the “features” of hierarchical query
is oracle applies any WHERE predicates AFTER it builds the entire hierarchy (using the CONNECT BY…START WITH clauses). This, combined with merging (or not) of view
in the main query, causes the WHERE predicate to be applied quite late in the query execution.
Following is my test-case which explains (??) the above point (unless, of course, if I have missed something obvious)

SQL> select * from v$version ;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE	11.2.0.1.0	Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> set lines 171 pages 999 arrays 151
SQL> create table t3
   (
   txid   ,
   month  ,
   item_cd,
   qty
   ) nologging 
as
select
   floor(rownum/5),
   floor(rownum/20),
   floor(rownum/10),
   floor(rownum/5)
from
   dual
connect by
   level  set autotrace traceonly explain
SQL> select txid, month, item_cd, qty, Row_Number() Over (Partition By txid Order By 1) from t3 where item_cd = 0 ;

Execution Plan
----------------------------------------------------------
Plan hash value: 1976707564

---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	9 |   468 |    11  (10)| 00:00:01 |
|   1 |  WINDOW SORT	   |	  |	9 |   468 |    11  (10)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T3   |	9 |   468 |    10   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ITEM_CD"=0)

Note
-----
   - dynamic sampling used for this statement (level=2)

SQL> select txid, month, item_cd, qty, rn from (select txid, month, item_cd, qty, Row_Number() Over (Partition By txid Order By 1) rn from t3) where item_cd = 0 ;

Execution Plan
----------------------------------------------------------
Plan hash value: 3729804300

------------------------------------------------------------------------------------
| Id  | Operation	    | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time	   |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |	   | 10000 |   634K|	   |   144   (2)| 00:00:02 |
|*  1 |  VIEW		    |	   | 10000 |   634K|	   |   144   (2)| 00:00:02 |
|   2 |   WINDOW SORT	    |	   | 10000 |   507K|   640K|   144   (2)| 00:00:02 |
|   3 |    TABLE ACCESS FULL| T3   | 10000 |   507K|	   |	10   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ITEM_CD"=0)

Note
-----
   - dynamic sampling used for this statement (level=2)

SQL> select txid, month, item_cd, qty, rn from (select txid, month, item_cd, qty, Row_Number() Over (Partition By txid, item_cd Order By 1) rn from t3) where item_cd = 0 ;

Execution Plan
----------------------------------------------------------
Plan hash value: 3729804300

----------------------------------------------------------------------------
| Id  | Operation	    | Name | Rows  | Bytes | Cost (%CPU)| Time	   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |	   |	 9 |   585 |	11  (10)| 00:00:01 |
|   1 |  VIEW		    |	   |	 9 |   585 |	11  (10)| 00:00:01 |
|   2 |   WINDOW SORT	    |	   |	 9 |   468 |	11  (10)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| T3   |	 9 |   468 |	10   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("ITEM_CD"=0)

Note
-----
   - dynamic sampling used for this statement (level=2)

SQL> SELECT
  MAX(REPLACE(SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(QTY),','),2),',',CHR(13)||CHR(10))) LISTING
FROM
(select txid, month, item_cd, qty, Row_Number() Over (Partition By txid, item_cd Order By 1) rn from t3) V
WHERE item_cd = 0
CONNECT BY PRIOR
  RN = RN-1
START WITH
  RN = 1;
  2    3    4    5    6    7    8    9  
Execution Plan
----------------------------------------------------------
Plan hash value: 3401893796

--------------------------------------------------------------------------------------------------
| Id  | Operation				  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT			  |	 |     1 |    39 |    13  (24)| 00:00:01 |
|   1 |  SORT AGGREGATE 			  |	 |     1 |    39 |	      | 	 |
|*  2 |   FILTER				  |	 |	 |	 |	      | 	 |
|*  3 |    CONNECT BY NO FILTERING WITH START-WITH|	 |	 |	 |	      | 	 |
|   4 |     VIEW				  |	 | 10000 |   380K|    12  (17)| 00:00:01 |
|   5 |      WINDOW SORT			  |	 | 10000 |   380K|    12  (17)| 00:00:01 |
|   6 |       TABLE ACCESS FULL 		  | T3	 | 10000 |   380K|    10   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ITEM_CD"=0)
   3 - access("RN"-1=PRIOR "RN")
       filter("RN"=1)

Note
-----
   - dynamic sampling used for this statement (level=2)

SQL> SELECT
  MAX(REPLACE(SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(QTY),','),2),',',CHR(13)||CHR(10))) LISTING
FROM 
(SELECT /*+ NO_MERGE */ txid, month, item_cd, qty, rn
  FROM (select txid, month, item_cd, qty, Row_Number() Over (Partition By txid, item_cd Order By 1) rn from t3)
 WHERE item_cd = 0) V
CONNECT BY PRIOR
  RN = RN-1
START WITH
  RN = 1;
  2    3    4    5    6    7    8    9   10  
Execution Plan
----------------------------------------------------------
Plan hash value: 2047232111

-------------------------------------------------------------------------------------------------
| Id  | Operation				 | Name | Rows	| Bytes | Cost (%CPU)| Time	|
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT			 |	|     1 |    26 |    12  (17)| 00:00:01 |
|   1 |  SORT AGGREGATE 			 |	|     1 |    26 |	     |		|
|*  2 |   CONNECT BY NO FILTERING WITH START-WITH|	|	|	|	     |		|
|   3 |    VIEW 				 |	|     9 |   234 |    11  (10)| 00:00:01 |
|   4 |     VIEW				 |	|     9 |   351 |    11  (10)| 00:00:01 |
|   5 |      WINDOW SORT			 |	|     9 |   351 |    11  (10)| 00:00:01 |
|*  6 |       TABLE ACCESS FULL 		 | T3	|     9 |   351 |    10   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("RN"-1=PRIOR "RN")
       filter("RN"=1)
   6 - filter("ITEM_CD"=0)

Note
-----
   - dynamic sampling used for this statement (level=2)
27 05 2011
Charles Hooper

Narendra,

Interesting points. Let’s double-check the accurary of the statements:

http://jonathanlewis.wordpress.com/2008/02/17/pushing-predicates-2/

http://www.jlcomp.demon.co.uk/push_pred.html

From the above links, PUSH_PRED and NO_PUSH_PRED hints are associated with the “join predicate pushdown (JPPD)” section of a 10053 trace file. The predicates that I was expecting to see generated are actually generated in the filter “push-down (FPD)” section (see the test case 10053 output below).

http://download.oracle.com/docs/cd/E14072_01/server.112/e10592/queries003.htm

Oracle processes hierarchical queries as follows:
•A join, if present, is evaluated first, whether the join is specified in the FROM clause or with WHERE clause predicates.
•The CONNECT BY condition is evaluated.
•Any remaining WHERE clause predicates are evaluated.

Oracle then uses the information from these evaluations to form the hierarchy using the following steps:
1.Oracle selects the root row(s) of the hierarchy—those rows that satisfy the START WITH condition.
2.Oracle selects the child rows of each root row. Each child row must satisfy the condition of the CONNECT BY condition with respect to one of the root rows.
3.Oracle selects successive generations of child rows. Oracle first selects the children of the rows returned in step 2, and then the children of those children, and so on. Oracle always selects children by evaluating the CONNECT BY condition with respect to a current parent row.
4.If the query contains a WHERE clause without a join, then Oracle eliminates all rows from the hierarchy that do not satisfy the condition of the WHERE clause. Oracle evaluates this condition for each row individually, rather than removing all the children of a row that does not satisfy the condition.
5.Oracle returns the rows in the order shown in Figure 9-1. In the diagram, children appear below their parents. For an explanation of hierarchical trees, see Figure 2-1, “Hierarchical Tree”.

It appears that your two points are valid. Thank you for the correction.

Test case script:

CREATE TABLE T1 AS
SELECT
  ROWNUM C1,
  DBMS_RANDOM.STRING('A',20) C2,
  SYSDATE+ROWNUM/100 C3
FROM
  DUAL
CONNECT BY
  LEVEL<=100000;

CREATE TABLE T2 AS
SELECT
  ROWNUM*2 C1,
  DBMS_RANDOM.STRING('A',20) C2,
  SYSDATE+ROWNUM/100 C3
FROM
  DUAL
CONNECT BY
  LEVEL<=50000;
ALTER SESSION SET TRACEFILE_IDENTIFIER='WATCH_TRANSFORM'; 
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

SELECT /*+ NO_MERGE(V) PUSH_PRED(V) */
  V.C1,
  V.C2
FROM
  (SELECT
     C1,
     C2,
     C3
  FROM
    T1) V
WHERE
  V.C1 BETWEEN 1 AND 10;

SELECT /*+ NO_MERGE(V) NO_PUSH_PRED(V) */
  V.C1,
  V.C2
FROM
  (SELECT
     C1,
     C2,
     C3
  FROM
    T1) V
WHERE
  V.C1 BETWEEN 1 AND 10;

SELECT /*+ NO_MERGE(V) PUSH_PRED(V) */
  V.C1,
  V.C2
FROM
  (SELECT
     C1,
     C2,
     C3
  FROM
    T1) V,
  T2
WHERE
  T2.C1 BETWEEN 1 AND 20
  AND T2.C1=V.C1;

SELECT /*+ NO_MERGE(V) NO_PUSH_PRED(V) */
  V.C1,
  V.C2
FROM
  (SELECT
     C1,
     C2,
     C3
  FROM
    T1) V,
  T2
WHERE
  T2.C1 BETWEEN 1 AND 20
  AND T2.C1=V.C1;

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

Sections of the generated 10053 trace file:

----- Current SQL Statement for this session (sql_id=fxa990xn82j8u) -----
SELECT /*+ NO_MERGE(V) PUSH_PRED(V) */
  V.C1,
  V.C2
FROM
  (SELECT
     C1,
     C2,
     C3
  FROM
    T1) V
WHERE
  V.C1 BETWEEN 1 AND 10

JPPD:     JPPD bypassed: View has unsupported constructs.

**************************
Predicate Move-Around (PM)
**************************
PM:   Passed validity checks.
PM:   Pushed down predicate "T1"."C1">=1
 from query block SEL$1 (#0) to query block SEL$2 (#0)
PM:   Pushed down predicate "T1"."C1"<=10
 from query block SEL$1 (#0) to query block SEL$2 (#0)
PM:     PM bypassed: checking.
query block SEL$1 (#0) unchanged
FPD: Considering simple filter push in query block SEL$1 (#0)
 ?? 
FPD: Considering simple filter push in query block SEL$2 (#0)
"T1"."C1">=1 AND "T1"."C1"<=10
try to generate transitive predicate from check constraints for query block SEL$2 (#0)
finally: "T1"."C1">=1 AND "T1"."C1"<=10 AND 1<=10
FPD:   transitive predicates are generated in query block SEL$2 (#0)
"T1"."C1">=1 AND "T1"."C1"<=10 AND 1<=10
apadrv-start sqlid=17197321763885761818

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ PUSH_PRED ("V") NO_MERGE ("V") */ "V"."C1" "C1","V"."C2" "C2" FROM  
(SELECT "T1"."C1" "C1","T1"."C2" "C2" FROM "TESTUSER"."T1" "T1" WHERE "T1"."C1">=1 AND "T1"."C1"<=10 AND 1<=10) "V"

============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |         |       |       |    60 |           |
| 1   |  VIEW               |         |     6 |   12K |    60 |  00:00:01 |
| 2   |   TABLE ACCESS FULL | T1      |     6 |   12K |    60 |  00:00:01 |
--------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter(("C1">=1 AND "C1"<=10))

Dumping Hints
=============
  atom_hint=(@=00000000161B15E0 err=0 resol=1 used=1 token=895 org=1 lvl=3 txt=PUSH_PRED ("V") )
  atom_hint=(@=00000000161B1858 err=0 resol=1 used=1 token=876 org=1 lvl=3 txt=NO_MERGE ("V") )

The above seems to indicate that the PUSH_PRED and NO_MERGE hints were both used, and we can see by the modified WHERE clause that the predicates present outside the inline view were

transferred inside the inline view (caused by “simple filter push”).

SELECT /*+ NO_MERGE(V) NO_PUSH_PRED(V) */
  V.C1,
  V.C2
FROM
  (SELECT
     C1,
     C2,
     C3
  FROM
    T1) V
WHERE
  V.C1 BETWEEN 1 AND 10

JPPD:     JPPD bypassed: View has unsupported constructs.

**************************
Predicate Move-Around (PM)
**************************
PM:   Passed validity checks.
PM:   Pushed down predicate "T1"."C1">=1
 from query block SEL$1 (#0) to query block SEL$2 (#0)
PM:   Pushed down predicate "T1"."C1"<=10
 from query block SEL$1 (#0) to query block SEL$2 (#0)
PM:     PM bypassed: checking.
query block SEL$1 (#0) unchanged
FPD: Considering simple filter push in query block SEL$1 (#0)
 ?? 
FPD: Considering simple filter push in query block SEL$2 (#0)
"T1"."C1">=1 AND "T1"."C1"<=10
try to generate transitive predicate from check constraints for query block SEL$2 (#0)
finally: "T1"."C1">=1 AND "T1"."C1"<=10 AND 1<=10
FPD:   transitive predicates are generated in query block SEL$2 (#0)
"T1"."C1">=1 AND "T1"."C1"<=10 AND 1<=10
apadrv-start sqlid=3636309318118712829
 
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ NO_PUSH_PRED ("V") NO_MERGE ("V") */ "V"."C1" "C1","V"."C2" "C2" FROM  
(SELECT "T1"."C1" "C1","T1"."C2" "C2" FROM "TESTUSER"."T1" "T1" WHERE "T1"."C1">=1 AND "T1"."C1"<=10 AND 1<=10) "V"

============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |         |       |       |    60 |           |
| 1   |  VIEW               |         |     6 |   12K |    60 |  00:00:01 |
| 2   |   TABLE ACCESS FULL | T1      |     6 |   12K |    60 |  00:00:01 |
--------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter(("C1">=1 AND "C1"<=10))
 
Dumping Hints
=============
  atom_hint=(@=00000000161B15D8 err=0 resol=1 used=0 token=880 org=1 lvl=3 txt=NO_PUSH_PRED ("V") )
  atom_hint=(@=00000000161B1850 err=0 resol=1 used=1 token=876 org=1 lvl=3 txt=NO_MERGE ("V") )

The above seems to indicate that the NO_PUSH_PRED hint was NOT used and NO_MERGE hints was used, and we can see by the modified WHERE clause that the predicates present outside the inline

view were transferred inside the inline view.

SELECT /*+ NO_MERGE(V) PUSH_PRED(V) */
  V.C1,
  V.C2
FROM
  (SELECT
     C1,
     C2,
     C3
  FROM
    T1) V,
  T2
WHERE
  T2.C1 BETWEEN 1 AND 20
  AND T2.C1=V.C1

JPPD:     JPPD bypassed: View has unsupported constructs.

**************************
Predicate Move-Around (PM)
**************************
PM:   Passed validity checks.
PM: Added transitive pred "V"."C1">=1
 in query block SEL$1 (#0)
PM: Added transitive pred "V"."C1"<=20
 in query block SEL$1 (#0)
PM:   Pushed down predicate "T1"."C1">=1
 from query block SEL$1 (#0) to query block SEL$2 (#0)
PM:   Pushed down predicate "T1"."C1"<=20
 from query block SEL$1 (#0) to query block SEL$2 (#0)
PM:     PM bypassed: checking.
query block SEL$1 (#0) unchanged
FPD: Considering simple filter push in query block SEL$1 (#0)
"T2"."C1">=1 AND "T2"."C1"<=20 AND "T2"."C1"="V"."C1"
try to generate transitive predicate from check constraints for query block SEL$1 (#0)
finally: "T2"."C1">=1 AND "T2"."C1"<=20 AND "T2"."C1"="V"."C1" AND 1<=20 AND "V"."C1">=1 AND "V"."C1"<=20

FPD:   transitive predicates are generated in query block SEL$1 (#0)
"T2"."C1">=1 AND "T2"."C1"<=20 AND "T2"."C1"="V"."C1" AND 1<=20 AND "V"."C1">=1 AND "V"."C1"<=20
JPPD:     JPPD bypassed: View not on right-side of outer-join.         <======================
FPD:   Following are pushed to where clause of query block SEL$2 (#0)
1<=20 AND "T1"."C1">=1 AND "T1"."C1"<=20
FPD: Considering simple filter push in query block SEL$2 (#0)
"T1"."C1">=1 AND "T1"."C1"<=20 AND 1<=20 AND "T1"."C1">=1 AND "T1"."C1"<=20
try to generate transitive predicate from check constraints for query block SEL$2 (#0)
finally: "T1"."C1">=1 AND "T1"."C1"<=20 AND 1<=20 AND "T1"."C1">=1 AND "T1"."C1"<=20
 
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ PUSH_PRED ("V") NO_MERGE ("V") */ "V"."C1" "C1","V"."C2" "C2" FROM  
(SELECT "T1"."C1" "C1","T1"."C2" "C2" FROM "TESTUSER"."T1" "T1" WHERE "T1"."C1">=1 AND "T1"."C1"<=20 AND 1<=20 AND "T1"."C1">=1 AND "T1"."C1"<=20) "V",
"TESTUSER"."T2" "T2" WHERE "T2"."C1">=1 AND "T2"."C1"<=20 AND "T2"."C1"="V"."C1"

============
Plan Table
============
---------------------------------------+-----------------------------------+
| Id  | Operation            | Name    | Rows  | Bytes | Cost  | Time      |
---------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT     |         |       |       |    93 |           |
| 1   |  HASH JOIN           |         |     3 |  6084 |    93 |  00:00:01 |
| 2   |   TABLE ACCESS FULL  | T2      |     3 |    39 |    32 |  00:00:01 |
| 3   |   VIEW               |         |     6 |   12K |    60 |  00:00:01 |
| 4   |    TABLE ACCESS FULL | T1      |     6 |   12K |    60 |  00:00:01 |
---------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - access("T2"."C1"="V"."C1")
2 - filter(("T2"."C1">=1 AND "T2"."C1"<=20))
4 - filter(("C1">=1 AND "C1"<=20))
 
Dumping Hints
=============
  atom_hint=(@=00000000161B15C8 err=0 resol=1 used=1 token=895 org=1 lvl=3 txt=PUSH_PRED ("V") )
  atom_hint=(@=00000000161B1840 err=0 resol=1 used=1 token=876 org=1 lvl=3 txt=NO_MERGE ("V") )

The above seems to indicate that the PUSH_PRED and NO_MERGE hints were both used, and we can see by the modified WHERE clause that the predicates present outside the inline view were

transferred inside the inline view (twice!).

SELECT /*+ NO_MERGE(V) NO_PUSH_PRED(V) */
  V.C1,
  V.C2
FROM
  (SELECT
     C1,
     C2,
     C3
  FROM
    T1) V,
  T2
WHERE
  T2.C1 BETWEEN 1 AND 20
  AND T2.C1=V.C1

JPPD:     JPPD bypassed: View has unsupported constructs.

**************************
Predicate Move-Around (PM)
**************************
PM:   Passed validity checks.
PM: Added transitive pred "V"."C1">=1
 in query block SEL$1 (#0)
PM: Added transitive pred "V"."C1"<=20
 in query block SEL$1 (#0)
PM:   Pushed down predicate "T1"."C1">=1
 from query block SEL$1 (#0) to query block SEL$2 (#0)
PM:   Pushed down predicate "T1"."C1"<=20
 from query block SEL$1 (#0) to query block SEL$2 (#0)
PM:     PM bypassed: checking.
query block SEL$1 (#0) unchanged
FPD: Considering simple filter push in query block SEL$1 (#0)
"T2"."C1">=1 AND "T2"."C1"<=20 AND "T2"."C1"="V"."C1"
try to generate transitive predicate from check constraints for query block SEL$1 (#0)
finally: "T2"."C1">=1 AND "T2"."C1"<=20 AND "T2"."C1"="V"."C1" AND 1<=20 AND "V"."C1">=1 AND "V"."C1"<=20

FPD:   transitive predicates are generated in query block SEL$1 (#0)
"T2"."C1">=1 AND "T2"."C1"<=20 AND "T2"."C1"="V"."C1" AND 1<=20 AND "V"."C1">=1 AND "V"."C1"<=20
JPPD:     JPPD bypassed: View not on right-side of outer-join.
FPD:   Following are pushed to where clause of query block SEL$2 (#0)
1<=20 AND "T1"."C1">=1 AND "T1"."C1"<=20
FPD: Considering simple filter push in query block SEL$2 (#0)
"T1"."C1">=1 AND "T1"."C1"<=20 AND 1<=20 AND "T1"."C1">=1 AND "T1"."C1"<=20
try to generate transitive predicate from check constraints for query block SEL$2 (#0)
finally: "T1"."C1">=1 AND "T1"."C1"<=20 AND 1<=20 AND "T1"."C1">=1 AND "T1"."C1"<=20

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ NO_PUSH_PRED ("V") NO_MERGE ("V") */ "V"."C1" "C1","V"."C2" "C2" FROM  
(SELECT "T1"."C1" "C1","T1"."C2" "C2" FROM "TESTUSER"."T1" "T1" WHERE "T1"."C1">=1 AND "T1"."C1"<=20 AND 1<=20 AND "T1"."C1">=1 AND "T1"."C1"<=20) "V",
"TESTUSER"."T2" "T2" WHERE "T2"."C1">=1 AND "T2"."C1"<=20 AND "T2"."C1"="V"."C1"

============
Plan Table
============
---------------------------------------+-----------------------------------+
| Id  | Operation            | Name    | Rows  | Bytes | Cost  | Time      |
---------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT     |         |       |       |    93 |           |
| 1   |  HASH JOIN           |         |     3 |  6084 |    93 |  00:00:01 |
| 2   |   TABLE ACCESS FULL  | T2      |     3 |    39 |    32 |  00:00:01 |
| 3   |   VIEW               |         |     6 |   12K |    60 |  00:00:01 |
| 4   |    TABLE ACCESS FULL | T1      |     6 |   12K |    60 |  00:00:01 |
---------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - access("T2"."C1"="V"."C1")
2 - filter(("T2"."C1">=1 AND "T2"."C1"<=20))
4 - filter(("C1">=1 AND "C1"<=20))

Dumping Hints
=============
  atom_hint=(@=00000000161B15C0 err=0 resol=1 used=0 token=880 org=1 lvl=3 txt=NO_PUSH_PRED ("V") )
  atom_hint=(@=00000000161B1838 err=0 resol=1 used=1 token=876 org=1 lvl=3 txt=NO_MERGE ("V") )
ALTER SESSION SET TRACEFILE_IDENTIFIER='WATCH_TRANSFORM2'; 
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

SELECT /*+ NO_MERGE(V) PUSH_PRED(V) */
  V.C1,
  V.C2
FROM
  (SELECT
     C1,
     C2,
     C3
  FROM
    T1) V,
  T2
WHERE
  T2.C1 BETWEEN 1 AND 20
  AND T2.C1=V.C1(+);

SELECT /*+ NO_MERGE(V) NO_PUSH_PRED(V) */
  V.C1,
  V.C2
FROM
  (SELECT
     C1,
     C2,
     C3
  FROM
    T1) V,
  T2
WHERE
  T2.C1 BETWEEN 1 AND 20
  AND T2.C1=V.C1(+);

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';
8 08 2013
M.H.

Awesome. The LISTAGG function really helped me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 144 other followers

%d bloggers like this: