December 9, 2009
A recent discussion in the OTN forums demonstrated a cardinality calculation problem caused by using ROWNUM as a predicate in the WHERE clause (this specific issue was corrected in 11.2.0.1):
http://forums.oracle.com/forums/thread.jspa?threadID=934895&start=0
The person who started the OTN thread obviously knew how to ask a question, provide relevant information, and analyze the problem (essentially a model of a good request for assistance):
I’m having a couple of issues with a query, and I can’t figure out the best way to reach a solution.
Platform Information
Windows Server 2003 R2
Oracle 10.2.0.4Optimizer Settings
SQL > show parameter optimizer NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_dynamic_sampling integer 2 optimizer_features_enable string 10.2.0.4 optimizer_index_caching integer 90 optimizer_index_cost_adj integer 30 optimizer_mode string ALL_ROWS optimizer_secure_view_merging boolean TRUEThe query below, is a simple “Top N” query, where the top result is returned. Here it is, with bind variables in the same location as the application code:
SELECT PRODUCT_DESC FROM ( SELECT PRODUCT_DESC , COUNT(*) AS CNT FROM USER_VISITS JOIN PRODUCT ON PRODUCT.PRODUCT_OID = USER_VISITS.PRODUCT_OID WHERE PRODUCT.PRODUCT_DESC != 'Home' AND VISIT_DATE BETWEEN ADD_MONTHS ( TRUNC ( TO_DATE ( :vCurrentYear , 'YYYY' ) , 'YEAR' ) , 3*(:vCurrentQuarter-1) ) AND ADD_MONTHS ( TRUNC ( TO_DATE ( :vCurrentYear , 'YYYY' ) , 'YEAR' ) , 3*:vCurrentQuarter ) - INTERVAL '1' DAY GROUP BY PRODUCT_DESC ORDER BY CNT DESC ) WHERE ROWNUM <= 1;The explain plan I receive when running the query above.
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------------------------------- |* 1 | COUNT STOPKEY | | 1 | | 1 |00:00:34.92 | 66343 | | | | | 2 | VIEW | | 1 | 1 | 1 |00:00:34.92 | 66343 | | | | |* 3 | FILTER | | 1 | | 1 |00:00:34.92 | 66343 | | | | | 4 | SORT ORDER BY | | 1 | 1 | 1 |00:00:34.92 | 66343 | 2048 | 2048 | 2048 (0)| | 5 | SORT GROUP BY NOSORT | | 1 | 1 | 27 |00:00:34.92 | 66343 | | | | | 6 | NESTED LOOPS | | 1 | 2 | 12711 |00:00:34.90 | 66343 | | | | | 7 | TABLE ACCESS BY INDEX ROWID| PRODUCT | 1 | 74 | 77 |00:00:00.01 | 44 | | | | |* 8 | INDEX FULL SCAN | PRODUCT_PRODDESCHAND_UNQ | 1 | 1 | 77 |00:00:00.01 | 1 | | | | |* 9 | INDEX FULL SCAN | USER_VISITS#PK | 77 | 2 | 12711 |00:00:34.88 | 66299 | | | | -------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=1) 3 - filter(ADD_MONTHS(TRUNC(TO_DATE(TO_CHAR(:VCURRENTYEAR),'YYYY'),'fmyear'),3*(:VCURRENTQUARTER-1))<=ADD_MONTHS(TRUNC(TO_DATE(TO_CHAR(:VCURR ENTYEAR),'YYYY'),'fmyear'),3*:VCURRENTQUARTER)-INTERVAL'+01 00:00:00' DAY(2) TO SECOND(0)) 8 - filter("PRODUCT"."PRODUCT_DESC"<>'Home') 9 - access("USER_VISITS"."VISIT_DATE">=ADD_MONTHS(TRUNC(TO_DATE(TO_CHAR(:VCURRENTYEAR),'YYYY'),'fmyear'),3*(:VCURRENTQUARTER-1)) AND "USER_VISITS"."PRODUCT_OID"="PRODUCT"."PRODUCT_OID" AND "USER_VISITS"."VISIT_DATE"<=ADD_MONTHS(TRUNC(TO_DATE(TO_CHAR(:VCURRENTYEAR),'YYYY') ,'fmyear'),3*:VCURRENTQUARTER)-INTERVAL'+01 00:00:00' DAY(2) TO SECOND(0)) filter(("USER_VISITS"."VISIT_DATE">=ADD_MONTHS(TRUNC(TO_DATE(TO_CHAR(:VCURRENTYEAR),'YYYY'),'fmyear'),3*(:VCURRENTQUARTER-1)) AND "USER_VISITS"."VISIT_DATE"<=ADD_MONTHS(TRUNC(TO_DATE(TO_CHAR(:VCURRENTYEAR),'YYYY'),'fmyear'),3*:VCURRENTQUARTER)-INTERVAL'+01 00:00:00' DAY(2) TO SECOND(0) AND "USER_VISITS"."PRODUCT_OID"="PRODUCT"."PRODUCT_OID"))TKPROF Row Source Generation
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.01 0 0 0 0 Fetch 2 35.10 35.13 0 66343 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 35.10 35.14 0 66343 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 62 Rows Row Source Operation ------- --------------------------------------------------- 1 COUNT STOPKEY (cr=66343 pr=0 pw=0 time=35132008 us) 1 VIEW (cr=66343 pr=0 pw=0 time=35131996 us) 1 FILTER (cr=66343 pr=0 pw=0 time=35131991 us) 1 SORT ORDER BY (cr=66343 pr=0 pw=0 time=35131936 us) 27 SORT GROUP BY NOSORT (cr=66343 pr=0 pw=0 time=14476309 us) 12711 NESTED LOOPS (cr=66343 pr=0 pw=0 time=22921810 us) 77 TABLE ACCESS BY INDEX ROWID PRODUCT (cr=44 pr=0 pw=0 time=3674 us) 77 INDEX FULL SCAN PRODUCT_PRODDESCHAND_UNQ (cr=1 pr=0 pw=0 time=827 us)(object id 52355) 12711 INDEX FULL SCAN USER_VISITS#PK (cr=66299 pr=0 pw=0 time=44083746 us)(object id 52949)However when I run the query with an ALL_ROWS hint I receive this explain plan (reasoning for this can be found here Jonathan’s Lewis’ response: http://www.freelists.org/post/oracle-l/ORDER-BY-and-first-rows-10-madness,4):
--------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 39 | 223 (25)| 00:00:03 | |* 1 | COUNT STOPKEY | | | | | | | 2 | VIEW | | 1 | 39 | 223 (25)| 00:00:03 | |* 3 | FILTER | | | | | | | 4 | SORT ORDER BY | | 1 | 49 | 223 (25)| 00:00:03 | | 5 | HASH GROUP BY | | 1 | 49 | 223 (25)| 00:00:03 | |* 6 | HASH JOIN | | 490 | 24010 | 222 (24)| 00:00:03 | |* 7 | TABLE ACCESS FULL | PRODUCT | 77 | 2849 | 2 (0)| 00:00:01 | |* 8 | INDEX FAST FULL SCAN| USER_VISITS#PK | 490 | 5880 | 219 (24)| 00:00:03 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=1) 3 - filter(ADD_MONTHS(TRUNC(TO_DATE(:VCURRENTYEAR,'YYYY'),'fmyear'),3*(TO_NUMBER(: VCURRENTQUARTER)-1))<=ADD_MONTHS(TRUNC(TO_DATE(:VCURRENTYEAR,'YYYY'),'fmyear'),3*TO_N UMBER(:VCURRENTQUARTER))-INTERVAL'+01 00:00:00' DAY(2) TO SECOND(0)) 6 - access("USER_VISITS"."PRODUCT_OID"="PRODUCT"."PRODUCT_OID") 7 - filter("PRODUCT"."PRODUCT_DESC"<>'Home') 8 - filter("USER_VISITS"."VISIT_DATE">=ADD_MONTHS(TRUNC(TO_DATE(:VCURRENTYEAR,'YYY Y'),'fmyear'),3*(TO_NUMBER(:VCURRENTQUARTER)-1)) AND "USER_VISITS"."VISIT_DATE"<=ADD_MONTHS(TRUNC(TO_DATE(:VCURRENTYEAR,'YYYY'),'fmyear'), 3*TO_NUMBER(:VCURRENTQUARTER))-INTERVAL'+01 00:00:00' DAY(2) TO SECOND(0))And the TKPROF Row Source Generation:
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 3 0.51 0.51 0 907 0 27 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 5 0.51 0.51 0 907 0 27 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 62 Rows Row Source Operation ------- --------------------------------------------------- 27 FILTER (cr=907 pr=0 pw=0 time=513472 us) 27 SORT ORDER BY (cr=907 pr=0 pw=0 time=513414 us) 27 HASH GROUP BY (cr=907 pr=0 pw=0 time=512919 us) 12711 HASH JOIN (cr=907 pr=0 pw=0 time=641130 us) 77 TABLE ACCESS FULL PRODUCT (cr=5 pr=0 pw=0 time=249 us) 22844 INDEX FAST FULL SCAN USER_VISITS#PK (cr=902 pr=0 pw=0 time=300356 us)(object id 52949)The query with the ALL_ROWS hint returns data instantly, while the other one takes about 70 times as long.
Interestingly enough BOTH queries generate plans with estimates that are WAY off. The first plan is estimating 2 rows, while the second plan is estimating 490 rows. However the real number of rows is correctly reported in the Row Source Generation as 12711 (after the join operation).
TABLE_NAME NUM_ROWS BLOCKS ------------------------------ ---------- ---------- USER_VISITS 196044 1049 INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR LAST_ANALYZED ------------------------------ ---------- ----------- ------------- ----------------- ------------------- USER_VISITS#PK 2 860 196002 57761 07/24/2009 13:17:59 COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS HISTOGRAM ------------------------------ ------------ -------------------- -------------------- -------------------------------- ---------- --------------- VISIT_DATE 195900 786809010E0910 786D0609111328 .0000051046452272 0 NONEI don’t know how the first one is estimating 2 rows, but I can compute the second’s cardinality estimates by assuming a 5% selectivity for the TO_DATE() functions:
SQL > SELECT ROUND(0.05*0.05*196044) FROM DUAL; ROUND(0.05*0.05*196044) ----------------------- 490However, removing the bind variables (and clearing the shared pool), does not change the cardinality estimates at all. I would like to avoid hinting this plan if possible and that is why I’m looking for advice. I also have a followup question.
Per one of Jonathan Lewis’ articles, adding a ROWNUM predicate in the WHERE clause causes the optimizer to switch to a FIRST ROWS n optimizer mode, as if a /*+ FIRST_ROWS(n) */ hint were provided for the query. To work around this issue, changing the query to use a ROW_NUMBER analytic function rather than the ROWNUM pseudo column could prevent the optimizer from switching to first rows optimization.
Another question you might ask is if bind peeking enabled? It is enabled by default on Oracle 10g (and 9i). Are there automatically generated histograms on any of the columns in the WHERE clause? Do any of the tables involved have out of date statistics (or cases where the statistics as of 10 PM do not match the statistics as of the current time)? Is it possible to examine 10053 trace files for the SQL statement?
Testing with either a CARDINALITY or OPT_ESTIMATE hint to see if the execution plan changes dramatically to improve performance is another possible area of investigation. The question then becomes whether this will be sufficient to over-rule the first rows optimizer so that it does not use an index access which will avoid a sort.
There may also be a problem with the accuracy of the EXPLAIN PLAN in SQL*Plus due to the bind variables in the SQL statement. If I recall correctly, explain plan does not peek at bind variables, as happens when the query is actually executed (but that may have been a 9i problem, where 10g uses DBMS_XPLAN for explain plans), and that SQL*Plus passes in numeric bind variables as if they are VARCHAR2. (I have an example in 11.1.0.7 which shows that this might not be the case).
The original poster provided a great test case to demonstrate what is happening:
DDL/DML
CREATE TABLE TEST_AGGR ( ID NUMBER , VAL DATE , PAD VARCHAR2(500) , ID2 NUMBER ); CREATE TABLE TEST_JOIN ( ID2 NUMBER , PAD VARCHAR2(5) , PROD VARCHAR2(15) ); CREATE SEQUENCE TEST_SEQUENCE; INSERT INTO TEST_AGGR SELECT TEST_SEQUENCE.NEXTVAL , SYSDATE + DBMS_RANDOM.VALUE*100 , RPAD('X',499,'0') , MOD(ROWNUM,78) FROM DUAL CONNECT BY ROWNUM <= 195000; INSERT INTO TEST_JOIN SELECT ID2 , (CASE WHEN ROWNUM IN (3,5) THEN 'Y' ELSE RPAD('X',4,'0') END) , (CASE WHEN ROWNUM = 1 THEN 'test_prod' WHEN ROWNUM BETWEEN 2 AND 3 THEN 'DUPLICATE A1' WHEN ROWNUM BETWEEN 4 AND 5 THEN 'A DUPLICATE A2' ELSE UPPER(DBMS_RANDOM.STRING('A',15)) END) FROM (SELECT DISTINCT ID2 FROM TEST_AGGR); CREATE INDEX TEST_AGGR_IDX ON TEST_AGGR(ID,VAL,ID2); CREATE UNIQUE INDEX TEST_JOIN_UIDX ON TEST_JOIN(PROD,PAD);Statistics Gathering
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'TEST_AGGR',cascade=>true); EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'TEST_JOIN',cascade=>true);Bind Variable Setup
var a VARCHAR2(25); var b VARCHAR2(25); exec :a := '08/01/2009'; exec :b := '08/27/2009';Non-hinted Query
SELECT /*repeatable_case_nohint*/ PROD FROM ( SELECT TEST_JOIN.PROD , COUNT(*) CNT FROM TEST_AGGR JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY') AND PROD 'TEST_PROD' GROUP BY TEST_JOIN.PROD ORDER BY CNT DESC ) WHERE ROWNUM <= 1Non-hinted XPLAN Output
SQL_ID 9smb486cd31b2, child number 0 ------------------------------------- SELECT /*repeatable_case_nohint*/ PROD FROM ( SELECT TEST_JOIN.PROD , COUNT(*) CNT FROM TEST_AGGR JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY') AND PROD <> 'TEST_PROD' GROUP BY TEST_JOIN.PROD ORDER BY CNT DESC ) WHERE ROWNUM <= 1 Plan hash value: 301623847 ----------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M | ----------------------------------------------------------------------------------------------------------------------------------------- |* 1 | COUNT STOPKEY | | 1 | | 1 |00:00:07.33 | 57334 | | | | | 2 | VIEW | | 1 | 2 | 1 |00:00:07.33 | 57334 | | | | |* 3 | FILTER | | 1 | | 1 |00:00:07.33 | 57334 | | | | | 4 | SORT ORDER BY | | 1 | 2 | 1 |00:00:07.33 | 57334 | 73728 | 73728 | | | 5 | SORT GROUP BY NOSORT | | 1 | 2 | 76 |00:00:07.33 | 57334 | | | | | 6 | NESTED LOOPS | | 1 | 2 | 50637 |00:00:07.24 | 57334 | | | | | 7 | TABLE ACCESS BY INDEX ROWID| TEST_JOIN | 1 | 77 | 78 |00:00:00.01 | 2 | | | | |* 8 | INDEX FULL SCAN | TEST_JOIN_UIDX | 1 | 1 | 78 |00:00:00.01 | 1 | | | | |* 9 | INDEX FULL SCAN | TEST_AGGR_IDX | 78 | 2 | 50637 |00:00:07.15 | 57332 | | | | ----------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=1) 3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY')) 8 - filter("TEST_JOIN"."PROD"<>'TEST_PROD') 9 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2" AND "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY')) filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"))Hinted Query
SELECT /*+ ALL_ROWS repeatable_case_hint*/ PROD FROM ( SELECT TEST_JOIN.PROD , COUNT(*) CNT FROM TEST_AGGR JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY') AND PROD 'TEST_PROD' GROUP BY TEST_JOIN.PROD ORDER BY CNT DESC ) WHERE ROWNUM <= 1Hinted XPLAN Output
SQL_ID 283wx8s0d04kn, child number 0 ------------------------------------- SELECT /*+ ALL_ROWS repeatable_case_hint*/ PROD FROM ( SELECT TEST_JOIN.PROD , COUNT(*) CNT FROM TEST_AGGR JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY') AND PROD <> 'TEST_PROD' GROUP BY TEST_JOIN.PROD ORDER BY CNT DESC ) WHERE ROWNUM <= 1 Plan hash value: 828673535 --------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M | --------------------------------------------------------------------------------------------------------------------------------- |* 1 | COUNT STOPKEY | | 1 | | 1 |00:00:00.34 | 772 | | | | | 2 | VIEW | | 1 | 75 | 1 |00:00:00.34 | 772 | | | | |* 3 | FILTER | | 1 | | 1 |00:00:00.34 | 772 | | | | | 4 | SORT ORDER BY | | 1 | 75 | 1 |00:00:00.34 | 772 | 73728 | 73728 | | | 5 | HASH GROUP BY | | 1 | 75 | 76 |00:00:00.34 | 772 | 808K| 808K| 1/0/0| |* 6 | HASH JOIN | | 1 | 50069 | 50637 |00:00:00.26 | 772 | 842K| 842K| 1/0/0| |* 7 | TABLE ACCESS FULL | TEST_JOIN | 1 | 77 | 78 |00:00:00.01 | 3 | | | | |* 8 | INDEX FAST FULL SCAN| TEST_AGGR_IDX | 1 | 50737 | 50637 |00:00:00.10 | 769 | | | | --------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=1) 3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY')) 6 - access("TEST_JOIN"."ID2"="TEST_AGGR"."ID2") 7 - filter("TEST_JOIN"."PROD"<>'TEST_PROD') 8 - filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY')))Observations
The cardinality estimates in the non-hinted plan are way, way off. However, in the hinted plan the cardinalities are nearly correct. In the non-hinted plan the cardinality estimates are incorrect at steps 8 and 9.
I tried to work out the cardinality estimate for the access of TEST_JOIN_UIDX by first calculating the “Effective Index Selectivity” based on the following data:
TABLE_NAME COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS ------------------------------ ------------------------------ ------------ ---------- ---------- TEST_JOIN PROD 76 .013157895 0Since in step 8 of the plan we are filtering based on a != condition we want ALL the rows except that one. Since the number of rows in this roughly equal the number of distinct values I calculated a rough effective index selectivity estimate of:
(1-0.13157895)*78 = 67.7368419
This is right in line with a logical understanding of the data.
Based on the following data:
TABLE_NAME COLUMN_NAME NUM_DISTINCT ------------------------------ ------------------------------ ------------ TEST_AGGR VAL 194588I would expect the following cardinality out of the TEST_AGGR_IDX index:
SQL > SELECT (TO_DATE(:b,'MM/DD/YYYY')-TO_DATE(:a,'MM/DD/YYYY'))/(MAX(VAL)-MIN(VAL))+2/194588 AS SELECTIVITY FROM TEST_AGGR; SELECTIVITY ----------- .260014672 SQL > SELECT .260014672*195128 AS CARDINALITY FROM DUAL; CARDINALITY ----------- 50736.1429This almost exactly matches the HINTED plan (error due to rounding).
Based on the estimates in the non-hinted plan I would have expected a 5% * 5% to occur but my calculations don’t match:
SQL > select .05*.05*(1/78)*195128 AS CARDINALITY from dual; CARDINALITY ----------- 6.25410256
After playing with the test case for a little while, I recalled seeing this behavior in the past with ROWNUM in the WHERE clause. What is happening is that the ROWNUM<=1 predicate is being pushed into the inline view. Take a look (at the E-Rows column):
SELECT /*+ gather_plan_statistics */ PROD FROM ( SELECT TEST_JOIN.PROD , COUNT(*) CNT FROM TEST_AGGR JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY') AND PROD <> 'TEST_PROD' GROUP BY TEST_JOIN.PROD ORDER BY CNT DESC ) WHERE ROWNUM <= 1; select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); ----------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------- |* 1 | COUNT STOPKEY | | 1 | | 1 |00:00:02.84 | 57178 | | | | | 2 | VIEW | | 1 | 2 | 1 |00:00:02.84 | 57178 | | | | |* 3 | FILTER | | 1 | | 1 |00:00:02.84 | 57178 | | | | | 4 | SORT ORDER BY | | 1 | 2 | 1 |00:00:02.84 | 57178 | 73728 | 73728 | | | 5 | SORT GROUP BY NOSORT | | 1 | 2 | 76 |00:00:02.80 | 57178 | | | | | 6 | NESTED LOOPS | | 1 | 2 | 50596 |00:00:03.04 | 57178 | | | | | 7 | TABLE ACCESS BY INDEX ROWID| TEST_JOIN | 1 | 77 | 78 |00:00:00.01 | 2 | | | | |* 8 | INDEX FULL SCAN | TEST_JOIN_UIDX | 1 | 1 | 78 |00:00:00.01 | 1 | | | | |* 9 | INDEX FULL SCAN | TEST_AGGR_IDX | 78 | 2 | 50596 |00:00:02.88 | 57176 | | | | ----------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=1) 3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY')) 8 - filter("TEST_JOIN"."PROD"<>'TEST_PROD') 9 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2" AND "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY')) filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"))
SELECT /*+ gather_plan_statistics */ PROD FROM ( SELECT TEST_JOIN.PROD , COUNT(*) CNT FROM TEST_AGGR JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY') AND PROD 'TEST_PROD' GROUP BY TEST_JOIN.PROD ORDER BY CNT DESC ) WHERE ROWNUM <= 2; select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); ----------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------- |* 1 | COUNT STOPKEY | | 1 | | 2 |00:00:02.70 | 57178 | | | | | 2 | VIEW | | 1 | 3 | 2 |00:00:02.70 | 57178 | | | | |* 3 | FILTER | | 1 | | 2 |00:00:02.70 | 57178 | | | | | 4 | SORT ORDER BY | | 1 | 3 | 2 |00:00:02.70 | 57178 | 9216 | 9216 | 8192 (0)| | 5 | SORT GROUP BY NOSORT | | 1 | 3 | 76 |00:00:02.73 | 57178 | | | | | 6 | NESTED LOOPS | | 1 | 3 | 50596 |00:00:02.88 | 57178 | | | | | 7 | TABLE ACCESS BY INDEX ROWID| TEST_JOIN | 1 | 77 | 78 |00:00:00.01 | 2 | | | | |* 8 | INDEX FULL SCAN | TEST_JOIN_UIDX | 1 | 1 | 78 |00:00:00.01 | 1 | | | | |* 9 | INDEX FULL SCAN | TEST_AGGR_IDX | 78 | 3 | 50596 |00:00:02.73 | 57176 | | | | ----------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=2) 3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY')) 8 - filter("TEST_JOIN"."PROD"<>'TEST_PROD') 9 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2" AND "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY')) filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"))
SELECT /*+ gather_plan_statistics */ PROD FROM ( SELECT TEST_JOIN.PROD , COUNT(*) CNT FROM TEST_AGGR JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY') AND PROD 'TEST_PROD' GROUP BY TEST_JOIN.PROD ORDER BY CNT DESC ) WHERE ROWNUM <= 3; select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); ----------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------- |* 1 | COUNT STOPKEY | | 1 | | 3 |00:00:02.71 | 57178 | | | | | 2 | VIEW | | 1 | 4 | 3 |00:00:02.71 | 57178 | | | | |* 3 | FILTER | | 1 | | 3 |00:00:02.71 | 57178 | | | | | 4 | SORT ORDER BY | | 1 | 4 | 3 |00:00:02.71 | 57178 | 9216 | 9216 | 8192 (0)| | 5 | SORT GROUP BY NOSORT | | 1 | 4 | 76 |00:00:02.67 | 57178 | | | | | 6 | NESTED LOOPS | | 1 | 4 | 50596 |00:00:02.93 | 57178 | | | | | 7 | TABLE ACCESS BY INDEX ROWID| TEST_JOIN | 1 | 77 | 78 |00:00:00.01 | 2 | | | | |* 8 | INDEX FULL SCAN | TEST_JOIN_UIDX | 1 | 1 | 78 |00:00:00.01 | 1 | | | | |* 9 | INDEX FULL SCAN | TEST_AGGR_IDX | 78 | 4 | 50596 |00:00:02.83 | 57176 | | | | ----------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=3) 3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY')) 8 - filter("TEST_JOIN"."PROD"<>'TEST_PROD') 9 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2" AND "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY')) filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"))
SELECT /*+ gather_plan_statistics */ PROD FROM ( SELECT TEST_JOIN.PROD , COUNT(*) CNT FROM TEST_AGGR JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY') AND PROD 'TEST_PROD' GROUP BY TEST_JOIN.PROD ORDER BY CNT DESC ) WHERE ROWNUM <= 50; select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); ----------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------- |* 1 | COUNT STOPKEY | | 1 | | 50 |00:00:02.72 | 57178 | | | | | 2 | VIEW | | 1 | 51 | 50 |00:00:02.72 | 57178 | | | | |* 3 | FILTER | | 1 | | 50 |00:00:02.72 | 57178 | | | | | 4 | SORT ORDER BY | | 1 | 51 | 50 |00:00:02.72 | 57178 | 9216 | 9216 | 8192 (0)| | 5 | SORT GROUP BY NOSORT | | 1 | 51 | 76 |00:00:02.65 | 57178 | | | | | 6 | NESTED LOOPS | | 1 | 51 | 50596 |00:00:02.88 | 57178 | | | | | 7 | TABLE ACCESS BY INDEX ROWID| TEST_JOIN | 1 | 77 | 78 |00:00:00.01 | 2 | | | | |* 8 | INDEX FULL SCAN | TEST_JOIN_UIDX | 1 | 1 | 78 |00:00:00.01 | 1 | | | | |* 9 | INDEX FULL SCAN | TEST_AGGR_IDX | 78 | 51 | 50596 |00:00:02.78 | 57176 | | | | ----------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=50) 3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY')) 8 - filter("TEST_JOIN"."PROD"<>'TEST_PROD') 9 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2" AND "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY')) filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"))
SELECT /*+ gather_plan_statistics */ PROD FROM ( SELECT TEST_JOIN.PROD , COUNT(*) CNT FROM TEST_AGGR JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY') AND PROD 'TEST_PROD' GROUP BY TEST_JOIN.PROD ORDER BY CNT DESC ) WHERE ROWNUM <= 74; select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); ----------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------- |* 1 | COUNT STOPKEY | | 1 | | 74 |00:00:02.71 | 57178 | | | | | 2 | VIEW | | 1 | 75 | 74 |00:00:02.71 | 57178 | | | | |* 3 | FILTER | | 1 | | 74 |00:00:02.71 | 57178 | | | | | 4 | SORT ORDER BY | | 1 | 75 | 74 |00:00:02.71 | 57178 | 9216 | 9216 | 8192 (0)| | 5 | SORT GROUP BY NOSORT | | 1 | 75 | 76 |00:00:02.66 | 57178 | | | | | 6 | NESTED LOOPS | | 1 | 75 |50596 |00:00:02.93 | 57178 | | | | | 7 | TABLE ACCESS BY INDEX ROWID| TEST_JOIN | 1 | 77 | 78 |00:00:00.01 | 2 | | | | |* 8 | INDEX FULL SCAN | TEST_JOIN_UIDX | 1 | 1 | 78 |00:00:00.01 | 1 | | | | |* 9 | INDEX FULL SCAN | TEST_AGGR_IDX | 78 | 75 |50596 |00:00:02.83 | 57176 | | | | ----------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=74) 3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY')) 8 - filter("TEST_JOIN"."PROD"<>'TEST_PROD') 9 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2" AND "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY')) filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"))
SELECT /*+ gather_plan_statistics */ PROD FROM ( SELECT TEST_JOIN.PROD , COUNT(*) CNT FROM TEST_AGGR JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY') AND PROD 'TEST_PROD' GROUP BY TEST_JOIN.PROD ORDER BY CNT DESC ) WHERE ROWNUM <= 75; select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); ------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------ |* 1 | COUNT STOPKEY | | 1 | | 75 |00:00:00.06 | 741 | | | | | 2 | VIEW | | 1 | 75 | 75 |00:00:00.06 | 741 | | | | |* 3 | FILTER | | 1 | | 75 |00:00:00.06 | 741 | | | | | 4 | SORT ORDER BY | | 1 | 75 | 75 |00:00:00.06 | 741 | 9216 | 9216 | 8192 (0)| | 5 | HASH GROUP BY | | 1 | 75 | 76 |00:00:00.06 | 741 | 963K| 963K| 2375K (0)| |* 6 | HASH JOIN | | 1 | 50074 | 50596 |00:00:00.30 | 741 | 1035K| 1035K| 1164K (0)| |* 7 | TABLE ACCESS FULL| TEST_JOIN | 1 | 77 | 78 |00:00:00.01 | 7 | | | | |* 8 | INDEX FULL SCAN | TEST_AGGR_IDX | 1 | 50742 | 50596 |00:00:00.10 | 734 | | | | ------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=75) 3 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY')) 6 - access("TEST_JOIN"."ID2"="TEST_AGGR"."ID2") 7 - filter("TEST_JOIN"."PROD"<>'TEST_PROD') 8 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY')) filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY')))
—
Compare the above output with what follows from Oracle 11.2.0.1, with the following parameters set:
ALTER SESSION SET optimizer_index_caching=0; ALTER SESSION SET optimizer_index_cost_adj=100; ALTER SESSION SET TRACEFILE_IDENTIFIER='optimizer_test100'; ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
SELECT /*+ gather_plan_statistics */ PROD FROM ( SELECT TEST_JOIN.PROD , COUNT(*) CNT FROM TEST_AGGR JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY') AND PROD 'TEST_PROD' GROUP BY TEST_JOIN.PROD ORDER BY CNT DESC ) WHERE ROWNUM <= 1; select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); --------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.03 | 747 | | | | |* 1 | COUNT STOPKEY | | 1 | | 1 |00:00:00.03 | 747 | | | | | 2 | VIEW | | 1 | 75 | 1 |00:00:00.03 | 747 | | | | |* 3 | SORT ORDER BY STOPKEY | | 1 | 75 | 1 |00:00:00.03 | 747 | 73728 | 73728 | | | 4 | HASH GROUP BY | | 1 | 75 | 76 |00:00:00.03 | 747 | 964K| 964K| 2491K (0)| |* 5 | FILTER | | 1 | | 44832 |00:00:00.06 | 747 | | | | |* 6 | HASH JOIN | | 1 | 44262 | 44832 |00:00:00.05 | 747 | 1035K| 1035K| 1265K (0)| |* 7 | TABLE ACCESS FULL | TEST_JOIN | 1 | 77 | 78 |00:00:00.01 | 3 | | | | |* 8 | INDEX FAST FULL SCAN| TEST_AGGR_IDX | 1 | 44852 | 44832 |00:00:00.02 | 744 | | | | --------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=1) 3 - filter(ROWNUM<=1) 5 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY')) 6 - access("TEST_JOIN"."ID2"="TEST_AGGR"."ID2") 7 - filter("TEST_JOIN"."PROD"<>'TEST_PROD') 8 - filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY')))
—
ALTER SESSION SET optimizer_index_caching=90; ALTER SESSION SET optimizer_index_cost_adj=30; ALTER SESSION SET TRACEFILE_IDENTIFIER='optimizer_test30';
SELECT /*+ gather_plan_statistics */ PROD FROM ( SELECT TEST_JOIN.PROD , COUNT(*) CNT FROM TEST_AGGR JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY') AND PROD 'TEST_PROD' GROUP BY TEST_JOIN.PROD ORDER BY CNT DESC ) WHERE ROWNUM <= 1; select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); --------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.03 | 747 | | | | |* 1 | COUNT STOPKEY | | 1 | | 1 |00:00:00.03 | 747 | | | | | 2 | VIEW | | 1 | 75 | 1 |00:00:00.03 | 747 | | | | |* 3 | SORT ORDER BY STOPKEY | | 1 | 75 | 1 |00:00:00.03 | 747 | 73728 | 73728 | | | 4 | HASH GROUP BY | | 1 | 75 | 76 |00:00:00.03 | 747 | 964K| 964K| 2501K (0)| |* 5 | FILTER | | 1 | | 44832 |00:00:00.06 | 747 | | | | |* 6 | HASH JOIN | | 1 | 44262 | 44832 |00:00:00.05 | 747 | 1035K| 1035K| 1286K (0)| |* 7 | TABLE ACCESS FULL | TEST_JOIN | 1 | 77 | 78 |00:00:00.01 | 3 | | | | |* 8 | INDEX FAST FULL SCAN| TEST_AGGR_IDX | 1 | 44852 | 44832 |00:00:00.02 | 744 | | | | --------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=1) 3 - filter(ROWNUM<=1) 5 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY')) 6 - access("TEST_JOIN"."ID2"="TEST_AGGR"."ID2") 7 - filter("TEST_JOIN"."PROD"<>'TEST_PROD') 8 - filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY')))
ALTER SESSION SET optimizer_index_caching=100; ALTER SESSION SET optimizer_index_cost_adj=1; ALTER SESSION SET TRACEFILE_IDENTIFIER='optimizer_test1';
SELECT /*+ gather_plan_statistics */ PROD FROM ( SELECT TEST_JOIN.PROD , COUNT(*) CNT FROM TEST_AGGR JOIN TEST_JOIN ON TEST_JOIN.ID2 = TEST_AGGR.ID2 WHERE VAL BETWEEN TO_DATE(:a,'MM/DD/YYYY') AND TO_DATE(:b,'MM/DD/YYYY') AND PROD 'TEST_PROD' GROUP BY TEST_JOIN.PROD ORDER BY CNT DESC ) WHERE ROWNUM <= 1; select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); ------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:01.16 | 57034 | | | | |* 1 | COUNT STOPKEY | | 1 | | 1 |00:00:01.16 | 57034 | | | | | 2 | VIEW | | 1 | 75 | 1 |00:00:01.16 | 57034 | | | | |* 3 | SORT ORDER BY STOPKEY| | 1 | 75 | 1 |00:00:01.16 | 57034 | 73728 | 73728 | | | 4 | HASH GROUP BY | | 1 | 75 | 76 |00:00:01.16 | 57034 | 964K| 964K| 2506K (0)| |* 5 | FILTER | | 1 | | 44832 |00:00:01.17 | 57034 | | | | | 6 | NESTED LOOPS | | 1 | 44262 | 44832 |00:00:01.16 | 57034 | | | | |* 7 | TABLE ACCESS FULL| TEST_JOIN | 1 | 77 | 78 |00:00:00.01 | 3 | | | | |* 8 | INDEX FULL SCAN | TEST_AGGR_IDX | 78 | 575 | 44832 |00:00:01.14 | 57031 | | | | ------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=1) 3 - filter(ROWNUM<=1) 5 - filter(TO_DATE(:A,'MM/DD/YYYY')<=TO_DATE(:B,'MM/DD/YYYY')) 7 - filter("TEST_JOIN"."PROD"<>'TEST_PROD') 8 - access("TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2" AND "TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY')) filter(("TEST_AGGR"."VAL"<=TO_DATE(:B,'MM/DD/YYYY') AND "TEST_AGGR"."VAL">=TO_DATE(:A,'MM/DD/YYYY') AND "TEST_JOIN"."ID2"="TEST_AGGR"."ID2"))
Recent Comments