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 
[…] Charles Hooper blogs about an Overly Complicated Use Case Example regarding Row Values to Comma Separated Lists. […]
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)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
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';Awesome. The LISTAGG function really helped me.