Fair enough. :)
Heath, April 19, 2002 - 5:03 pm UTC
I suspected that the reason for this behavior would be along the lines of changing the answer. Thanks for the good example that proved this.
Can you suggest another approach to accomplish what I am trying to do? The requirement is that every query against this table have its results processed through the analytic function logic. That is, use whatever predicate the user/application specifies to find a subset of the rows, run them through the test_func function to reduce them further and then, for the set of remaining rows, run them through the windowing logic and the analytic function to choose one of those rows (RNUM=1).
Is my problem simply not being able to formulate a correct SQL for this requirement? Or is this something that can not be done using views containing analytic functions?
I have to use a view because the Analytic function can not exist in a WHERE clause. Even if not for that reason, I would want the application to query a view to only "see" a subset of the columns in the table.
I know of other ways to address the problem, but they are not as easy on the database as the analytic function approach would be (assuming I can get it to work). :)
Thanks again for the help,
Heath
April 19, 2002 - 6:20 pm UTC
Your requirements cannot be accomplished with a view then.
The predicate will always be applied AFTER the analytics buried in the view, they must be. This is because:
select analytic
from t
where condition
is VERY and ALWAYS different from
select *
from ( select analytic
from t )
where condition
The problem is, in a nutshell, you cannot get there from here.
Your reason for using a view is backwards from your requirement. You CAN use an analytic result in a predicate (you DO -- rnum = 1)!!! You just have to do it AFTER, you cannot do it DURING.
It is just like an aggregate. You cannot say: where count(*) = 1, you have to say HAVING count(*) = 1. The predicate is done AFTER the result set is built. Same with analytics.
I believe you can use analytics (i know you can)
I also know you cannot use a view given your above list of "i must do this".
I have a quick question about partitioning
sam, December 04, 2002 - 10:46 am UTC
Tom,
Can you guide me to a white paper written on Table Partitioning & indexing , which is most useful & easy to understand.
how to make no_push_pred work
PINGU SAN, May 20, 2003 - 5:41 am UTC
Hi
I have following query
select ename, dname
from (select * from emp where deptno = 10) a,
(select * from dept where deptno = 10) b
where a.empno = 7934;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=21)
1 0 NESTED LOOPS (Cost=3 Card=1 Bytes=21)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=1 Bytes=10)
3 2 INDEX (UNIQUE SCAN) OF 'EMP_PK' (UNIQUE)
4 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=1 Bytes=11)
I think its pushing the predicate (where empno... ) into view, so I changed the query to
select /*+ NO_PUSH_PRED(a) */ ename, dname
from (select * from emp where deptno = 10) a,
(select * from dept where deptno = 10) b
where a.empno = 7934;
but got same execution plan when I was expecting two full table scan of emp and dept...
How does this hint work?
May 20, 2003 - 12:50 pm UTC
it works with join conditions. thats not a join. You can use no_merge to get an alternate (but junky) plan -- but basically, the CBO is going to be free to move that predicate around.
You can get the plan you seem to be after using a ROWNUM trick:
scott@ORA920> explain plan for
2 select ename, dname
3 from (select * from emp where deptno = 10 and rownum > 0 ) a,
4 (select * from dept where deptno = 10) b
5 where a.empno = 7934
6 /
Explained.
scott@ORA920> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | NESTED LOOPS | | | | |
|* 2 | TABLE ACCESS FULL | DEPT | | | |
|* 3 | VIEW | | | | |
| 4 | COUNT | | | | |
|* 5 | FILTER | | | | |
|* 6 | TABLE ACCESS FULL| EMP | | | |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DEPT"."DEPTNO"=10)
3 - filter("A"."EMPNO"=7934)
5 - filter(ROWNUM>0)
6 - filter("EMP"."DEPTNO"=10)
Note: rule based optimization
22 rows selected.
but the question would be "why" at that point.
How to make NO_PUSH_PRED work
AB, March 02, 2004 - 11:20 am UTC
Tom
I have a table with millions of "good" rows and a few junk rows. I am trying to filter the bad records on a VARCHAR2 representation of a date so I can TO_DATE the good records.
I was simply using :-
SELECT /*+ NO_PUSH_PRED (a) */
some_columns
FROM (
SELECT some_columns
FROM some_table
WHERE SUBSTR(char_date_column,1,1) BETWEEN '0' AND '9'
) a
, some_other_table b
WHERE some_join_condition
AND TO_DATE(a.char_date_column, 'YYYY-MM-DD') < TRUNC(SYSDATE, 'YEAR')
However, the predicate is being pushed into the "a" in-line view, because I am getting
TO_DATE errors against the few bad records. If I bring the SUBSTR outside the in-line view and use
ORDERED_PREDICATES, then it seems happy. Any suggestions ?
Regards
AB
March 02, 2004 - 6:45 pm UTC
use CASE
where some_join_condition
and case when substr( char_date_column,1,1) between '0' and '9'
then to_date( .... )
else null
end < trunc( .... );
Follow-up to original question.
Kashif, April 25, 2004 - 10:02 am UTC
Hi Tom,
I realize you answered the original question more than two years ago, but I was curious to know if you remembered why the predicate "group_id = 'TRACE'" (which was the one that used the index) was pushed into the inline view. Does it have anything to do with the fact that the group_id was involved in the analytics performed in the view? Wouldn't the results have differed if the inline view was first materialized and then had the "group_id = 'TRACE'" predicate applied? I'm itching to test this out but unfortunately don't have access to a database right now. Thanks for your time.
Kashif
April 26, 2004 - 6:07 am UTC
yes, since we PARTIONED by group id -- predicates against group id are "safe" to push into the query.
PUSH_PRED and OUTER JOIN
A reader, August 26, 2004 - 8:58 pm UTC
Hi Tom,
Can a predicate be pushed into a subquery joined with LEFT OUTER JOIN?
Like:
SELECT /*+ PUSH_PRED */
FROM SMALL
LEFT JOIN BIG ON SMALL.ID = BIG.ID
WHERE SMALL.ID = 9
Will the predicate "ID = 9" be pushed into the subquery?
August 27, 2004 - 7:29 am UTC
if possible, yes.
How to force Oracle to push an outer predicate
dharma, October 29, 2004 - 6:25 pm UTC
Hi Tom,
I have this big SQL statement that uses a With clause and more inline views. and has where clauses using the parameter I pass in the procedure. I thought of moving the Query to View and then filter the view. But now arises my problem, I thought the CBO will filter the rows from the inline view but unfortunately it is querying all the rows from the view I created and then applying the filter. I tried using Push_pred to no avail.
Also I tried a query using the emp, DEPT TABLE with statistics set to the rows I have in my regular tables to find that the views rows are filtered at the tables level rather at the view level.
scott@krypton.8NBP931> CREATE OR REPLACE VIEW
emp_dept_vw
AS
SELECT A.empno, A.ename, A.dname, A.deptno,A.sal, A.rnk
FROM ( WITH q1 AS (SELECT e.*, RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) rnk FROM EMP e),
q2 AS (SELECT * FROM DEPT)
SELECT q1.empno, q1.ename, q2.dname, q1.deptno, q1.sal, q1.rnk
FROM (SELECT * FROM q1 WHERE rnk<4) q1, q2
WHERE q2.deptno = q1.deptno ) A
left outer join (SELECT * FROM EMP ORDER BY deptno,sal DESC) EMP ON (A.empno = EMP.empno)
scott@krypton.8NBP931> EXPLAIN PLAN FOR
2 SELECT * FROM emp_dept_vw WHERE deptno=10;
Explained.
scott@krypton.8NBP931> @UTLXPLS
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 26G| 2028G| | 25737 |
|* 1 | HASH JOIN OUTER | | 26G| 2028G| 27M| 25737 |
| 2 | VIEW | | 366K| 23M| | 2474 |
| 3 | MERGE JOIN CARTESIAN | | 366K| 25M| | 2474 |
|* 4 | TABLE ACCESS FULL | DEPT | 1 | 13 | | 2 |
| 5 | BUFFER SORT | | 366K| 20M| | 2472 |
|* 6 | VIEW | | 366K| 20M| | |
|* 7 | WINDOW SORT PUSHED RANK| | 366K| 6445K| 22M| 2472 |
|* 8 | TABLE ACCESS FULL | EMP | 366K| 6445K| | 2 |
| 9 | VIEW | | 1100K| 13M| | 21755 |
| 10 | SORT ORDER BY | | 1100K| 38M| 126M| 21755 |
| 11 | TABLE ACCESS FULL | EMP | 1100K| 38M| | 2 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."EMPNO"="EMP"."EMPNO"(+))
4 - filter("DEPT"."DEPTNO"=10)
6 - filter("Q1"."RNK"<4)
7 - filter(RANK() OVER ( PARTITION BY "E"."DEPTNO" ORDER BY "E"."SAL" DESC )<4)
8 - filter("E"."DEPTNO"=10)
Note: cpu costing is off
28 rows selected.
I would be willing to post the actual query and plan (since its quite big) if you need them.
Is there any way how I can push the predicates inside the tables and not at the view level.
Thanks,
-dharma
October 29, 2004 - 6:35 pm UTC
with analytics there is a HUGE BIG DIFFERENCE between:
select ..., analytic_function
from t
where <condition>
and
select *
from ( select ...., analytic_function
from t
)
where <condition>
analytics are applied AFTER the where clause -- pushing the where "down" changes the results, changes the answer
you cannot expect it to push the predicate here expect on things that we partition by (as that would not change the answer)
but here - in this example, it certainly did push the predicate down (deptno=10)
so, what was the question again?
dharma, October 29, 2004 - 8:17 pm UTC
The question is, I have a query with a where clause inside the inline view(with clause), which is substituted by a parameter. Now I want it as a view so the parameters become filters for the view.
something like
proc p(p1 varchar2, p2 parameter)
is
cursor c is
(SELECT *
FROM (SELECT e.*, d.dname
FROM (SELECT *
FROM EMP WHERE comm=P1) e,
DEPT d
WHERE d.dname=P2
AND e.deptno=d.deptno)
)
declare
..
end;
to be changed into a view.
create view vw as
select * from EMP E, DEPT D
where E.DEPTNO=D.DEPTNO
and procedure becomes
procedure p(p1 varchar2, p2 varchar2)
cursor c is
(select * from vw
where comm = p1
and dname = p1)
begin
end;
Also this vw is used in multiple places.
I have posted the actual query and plan below.
Thanks,
dharma
Actual query and Plan
A reader, October 29, 2004 - 8:52 pm UTC
The query plan for the actual query has filters in the inline queries..
The predicate information in 16,17,27,28, 31 show how it filters. But when i change it to a view I remove the where clause that has the parameters, and then filter it from the view. But the plan is not identical.
The views plan has all the filters in 1. and filters the aagy_code in 12,15,16,28,29.
How to push the ecycle_code inside.the view takes more than an hour where as the original query takes just 3 minutes
WITH
priority_rnk AS
(SELECT app_reqs.aagy_code, app_reqs.aent_sys_id, app_reqs.priority,
app_reqs.eletp_code, app_reqs.alictp_code, app_reqs.qqlfmth_code,
RANK () OVER (PARTITION BY app_reqs.aagy_code, app_reqs.aent_sys_id, app_reqs.qqlfmth_code, app_reqs.alictp_code
ORDER BY app_reqs.priority) rnk
FROM app_appn_reqs app_reqs,
(SELECT DISTINCT aagy_code, alictp_code, eletp_code
FROM exm_lic_exam_cycles
WHERE ecycle_code = '04-286') elec ------------------>first parameter
WHERE app_reqs.status = 'P' --Status is "Pending"
AND app_reqs.eletp_code IS NOT NULL --Requirement type is EXAM
AND elec.aagy_code = app_reqs.aagy_code
AND elec.alictp_code = app_reqs.alictp_code
AND elec.eletp_code = app_reqs.eletp_code),
aar AS
(SELECT aar.ROWID aar_row_id,ala.ROWID ala_row_id, aar.aagy_code, aar.sys_id,
aar.aent_sys_id, aar.qqlfmth_code,
aar.qqlmtrq_seq_num, aar.alictp_code, aar.qrqm_code, aar.sub_type,
aar.priority, aar.status, aar.eletp_code,aar.qsrctp_code,
aar.valid_for, aar.valid_for_unit_measure,
aar.qualification_date, aar.start_clock_code,
aar.aconstr_seq_constrained_by, aar.constraint_fail_action,
aar.complete_within_period, aar.complete_period_unit_measure,
aar.expiration_date, aar.apkg_code,aar.asdoctp_code, aar.cfee_code,
aar.fee_paid_ind,aar.aittp_code,aar.verification_module,
aar.fee_paid_from_conversion_ind,ala.aliccl_code ala_aliccl_code,
COUNT(DECODE(aar.priority,
1,
DECODE(aar.status,'P',1) --Requirement is "Pending"
)
) OVER
(PARTITION BY aar.aagy_code,
aar.aent_sys_id,
aar.qqlfmth_code,
aar.alictp_code
) pending_req_cnt
FROM app_lic_applications ala, app_appn_reqs aar
WHERE --Applicant Status Type "Approved to sit" - A, "Pending" - P
ala.astatp_code IN ('A','P')
AND ala.aagy_code = aar.aagy_code
AND ala.aent_sys_id = aar.aent_sys_id
AND ala.alictp_code = aar.alictp_code
AND ala.qqlfmth_code = aar.qqlfmth_code)
--Start Main Query
SELECT *
FROM
--Get only rank =1 to pick only least priority with pending status.
(SELECT DISTINCT aagy_code, aent_sys_id, qqlfmth_code,
alictp_code, eletp_code, priority
FROM priority_rnk
WHERE priority_rnk.rnk = 1) priority_rnk,
--To get only aent_sys_ids who have fulfilled all priority=1 requirements.
(SELECT aar.*
FROM aar
WHERE pending_req_cnt = 0) aar,
--Used in app_chk_fee_paid_p
(SELECT aagy_code, aent_sys_id, effective_date,
ROW_NUMBER() OVER
(PARTITION BY aagy_code,aent_sys_id
ORDER BY seq_num DESC) row_num
FROM app_admin_holds
WHERE effective_date IS NOT NULL
AND stop_date IS NULL) aah,
(SELECT ele.aagy_code, ele.alictp_code, ele.eletp_code, ele.exam_date, ele.ecycle_code
FROM exm_lic_exam_cycles ele,
exm_exam_cycles ec
WHERE ele.aagy_code = ec.aagy_code
AND ele.ecycle_code = ec.code
AND ec.status = 'O' --exam status is OPEN
) exm
WHERE priority_rnk.aagy_code = '3300' --------------second parameter
--AND r.aent_sys_id=145143 mary
AND priority_rnk.aagy_code = aar.aagy_code
AND priority_rnk.aent_sys_id = aar.aent_sys_id
AND priority_rnk.qqlfmth_code = aar.qqlfmth_code
AND priority_rnk.alictp_code = aar.alictp_code
AND ( aar.priority < priority_rnk.priority
OR ( aar.status = 'P' --Requirement Pending
AND priority_rnk.priority = aar.priority
AND priority_rnk.eletp_code = aar.eletp_code
)
)
AND aah.row_num(+) = 1
AND aah.aagy_code(+) = aar.aagy_code
AND aah.aent_sys_id(+) = aar.aent_sys_id
AND exm.aagy_code(+) = aar.aagy_code
AND exm.alictp_code(+) = aar.alictp_code
AND exm.eletp_code(+) = aar.eletp_code
AND exm.ecycle_code(+) = '04-286'; --first parameter again
atprod@ATS.titanium> @utlxpls
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | WINDOW BUFFER | | 1 |
| 2 | WINDOW BUFFER | | 1 |
| 3 | WINDOW SORT | | 1 |
|* 4 | HASH JOIN OUTER | | 1 |
|* 5 | HASH JOIN OUTER | | 1 |
|* 6 | HASH JOIN | | 1 |
| 7 | VIEW | | 302 |
| 8 | SORT UNIQUE | | 302 |
|* 9 | VIEW | | 302 |
|* 10 | WINDOW SORT PUSHED RANK | | 302 |
| 11 | VIEW | | |
|* 12 | WINDOW CHILD PUSHED RANK| | 302 |
| 13 | VIEW | | 302 |
| 14 | SORT UNIQUE | | 302 |
| 15 | NESTED LOOPS | | 302 |
|* 16 | TABLE ACCESS FULL | APP_APPN_REQS | 16023 |
|* 17 | INDEX UNIQUE SCAN | ELEC_PK_PRIM | 1 |
|* 18 | VIEW | | 1083 |
| 19 | WINDOW SORT | | 1083 |
|* 20 | HASH JOIN | | 1083 |
|* 21 | TABLE ACCESS FULL | APP_LIC_APPLICATIONS | 40660 |
|* 22 | TABLE ACCESS FULL | APP_APPN_REQS | 2265K|
| 23 | VIEW | | 9 |
| 24 | MERGE JOIN CARTESIAN | | 9 |
|* 25 | TABLE ACCESS BY INDEX ROWID | EXM_EXAM_CYCLES | 1 |
|* 26 | INDEX UNIQUE SCAN | ECYCLE_PK_PRIM | 2667 |
| 27 | TABLE ACCESS BY INDEX ROWID | EXM_LIC_EXAM_CYCLES | 9 |
|* 28 | INDEX RANGE SCAN | ELEC_PK_PRIM | 9 |
|* 29 | VIEW | | 3918 |
|* 30 | WINDOW SORT PUSHED RANK | | 3918 |
|* 31 | TABLE ACCESS FULL | APP_ADMIN_HOLDS | 3918 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("AAH"."AENT_SYS_ID"(+)="AAR"."AENT_SYS_ID")
5 - access("EXM"."ALICTP_CODE"(+)="AAR"."ALICTP_CODE" AND
"EXM"."ELETP_CODE"(+)="AAR"."ELETP_CODE")
6 - access("PRIORITY_RNK"."AENT_SYS_ID"="AAR"."AENT_SYS_ID" AND
"PRIORITY_RNK"."QQLFMTH_CODE"="AAR"."QQLFMTH_CODE" AND
"PRIORITY_RNK"."ALICTP_CODE"="AAR"."ALICTP_CODE")
filter("AAR"."PRIORITY"<"PRIORITY_RNK"."PRIORITY" OR "AAR"."STATUS"='P' AND
"PRIORITY_RNK"."PRIORITY"="AAR"."PRIORITY" AND
"PRIORITY_RNK"."ELETP_CODE"="AAR"."ELETP_CODE")
9 - filter("PRIORITY_RNK"."RNK"=1)
10 - filter(RANK() OVER ( PARTITION BY "$vm_view"."AAGY_CODE","$vm_view"."AENT_SYS_ID","$vm
_view"."QQLFMTH_CODE","$vm_view"."ALICTP_CODE" ORDER BY "$vm_view"."PRIORITY")<=1)
12 - filter(RANK() OVER ( PARTITION BY "$vm_view"."AAGY_CODE","$vm_view"."AENT_SYS_ID","$vm
_view"."QQLFMTH_CODE","$vm_view"."ALICTP_CODE" ORDER BY "$vm_view"."PRIORITY")<=1)
16 - filter("APP_REQS"."STATUS"='P' AND "APP_REQS"."ELETP_CODE" IS NOT NULL AND
"APP_REQS"."AAGY_CODE"='3300')
17 - access("EXM_LIC_EXAM_CYCLES"."AAGY_CODE"='3300' AND
"EXM_LIC_EXAM_CYCLES"."ECYCLE_CODE"='04-286' AND
"EXM_LIC_EXAM_CYCLES"."ALICTP_CODE"="APP_REQS"."ALICTP_CODE" AND
"EXM_LIC_EXAM_CYCLES"."ELETP_CODE"="APP_REQS"."ELETP_CODE")
18 - filter("AAR"."PENDING_REQ_CNT"=0)
20 - access("ALA"."AENT_SYS_ID"="AAR"."AENT_SYS_ID" AND
"ALA"."ALICTP_CODE"="AAR"."ALICTP_CODE" AND "ALA"."QQLFMTH_CODE"="AAR"."QQLFMTH_CODE")
21 - filter(("ALA"."ASTATP_CODE"='A' OR "ALA"."ASTATP_CODE"='P') AND
"ALA"."AAGY_CODE"='3300' AND "ALA"."QQLFMTH_CODE" IS NOT NULL)
22 - filter("AAR"."AAGY_CODE"='3300')
25 - filter("EC"."STATUS"='O')
26 - access("EC"."AAGY_CODE"='3300' AND "EC"."CODE"='04-286')
28 - access("ELE"."AAGY_CODE"='3300' AND "ELE"."ECYCLE_CODE"='04-286')
29 - filter("AAH"."ROW_NUM"(+)=1)
30 - filter(ROW_NUMBER() OVER ( PARTITION BY
"APP_ADMIN_HOLDS"."AAGY_CODE","APP_ADMIN_HOLDS"."AENT_SYS_ID" ORDER BY
"APP_ADMIN_HOLDS"."SEQ_NUM" DESC )<=1)
31 - filter("APP_ADMIN_HOLDS"."STOP_DATE" IS NULL AND "APP_ADMIN_HOLDS"."AAGY_CODE"='3300')
The views plan:
atprod@ATS.titanium> EXPLAIN PLAN FOR
2 SELECT * FROM app_eligible_cycle_v V
3 WHERE pr_aagy_code = '3300'
4 AND aagy_code ='3300'
5 AND pr_ecycle_code = '04-286'
6 AND ecycle_code(+) = '04-286';
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 288 |
|* 1 | VIEW | APP_ELIGIBLE_CYCLE_V | 1 | 288 |
| 2 | WINDOW BUFFER | | 1 | 282 |
| 3 | WINDOW BUFFER | | 1 | 282 |
| 4 | WINDOW SORT | | 1 | 282 |
|* 5 | HASH JOIN | | 1 | 282 |
|* 6 | HASH JOIN OUTER | | 1083 | 266K|
|* 7 | HASH JOIN OUTER | | 1083 | 233K|
|* 8 | VIEW | | 1083 | 200K|
| 9 | WINDOW SORT | | 1083 | 125K|
|* 10 | HASH JOIN | | 1083 | 125K|
|* 11 | TABLE ACCESS FULL | APP_LIC_APPLICATIONS | 40660 | 1230K|
|* 12 | TABLE ACCESS FULL | APP_APPN_REQS | 2265K| 190M|
| 13 | VIEW | | 3144 | 97464 |
|* 14 | HASH JOIN | | 3144 | 128K|
|* 15 | TABLE ACCESS FULL | EXM_EXAM_CYCLES | 365 | 5110 |
|* 16 | TABLE ACCESS FULL | EXM_LIC_EXAM_CYCLES | 19494 | 533K|
|* 17 | VIEW | | 3918 | 118K|
|* 18 | WINDOW SORT PUSHED RANK | | 3918 | 107K|
|* 19 | TABLE ACCESS FULL | APP_ADMIN_HOLDS | 3918 | 107K|
| 20 | VIEW | | 682K| 19M|
| 21 | SORT UNIQUE | | 682K| 29M|
|* 22 | VIEW | | 682K| 29M|
|* 23 | WINDOW SORT PUSHED RANK | | 682K| 41M|
| 24 | VIEW | | | |
|* 25 | WINDOW CHILD PUSHED RANK| | 682K| 41M|
| 26 | VIEW | | 682K| 41M|
|* 27 | HASH JOIN | | 682K| 35M|
|* 28 | INDEX FAST FULL SCAN | ELEC_PK_PRIM | 19494 | 380K|
|* 29 | TABLE ACCESS FULL | APP_APPN_REQS | 16023 | 547K|
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("V"."PR_AAGY_CODE"='3300' AND "V"."PR_ECYCLE_CODE"='04-286' AND
"V"."ECYCLE_CODE"='04-286')
5 - access("PRIORITY_RNK"."AENT_SYS_ID"="AAR"."AENT_SYS_ID" AND
"PRIORITY_RNK"."QQLFMTH_CODE"="AAR"."QQLFMTH_CODE" AND
"PRIORITY_RNK"."ALICTP_CODE"="AAR"."ALICTP_CODE")
filter("AAR"."PRIORITY"<"PRIORITY_RNK"."PRIORITY" OR "AAR"."STATUS"='P' AND
"PRIORITY_RNK"."PRIORITY"="AAR"."PRIORITY" AND "PRIORITY_RNK"."ELETP_CODE"="AAR"."ELETP_CODE")
6 - access("AAH"."AENT_SYS_ID"(+)="AAR"."AENT_SYS_ID")
7 - access("EXM"."ALICTP_CODE"(+)="AAR"."ALICTP_CODE" AND
"EXM"."ELETP_CODE"(+)="AAR"."ELETP_CODE")
8 - filter("AAR"."PENDING_REQ_CNT"=0)
10 - access("ALA"."AENT_SYS_ID"="AAR"."AENT_SYS_ID" AND "ALA"."ALICTP_CODE"="AAR"."ALICTP_CODE"
AND "ALA"."QQLFMTH_CODE"="AAR"."QQLFMTH_CODE")
11 - filter(("ALA"."ASTATP_CODE"='A' OR "ALA"."ASTATP_CODE"='P') AND "ALA"."AAGY_CODE"='3300' AND
"ALA"."QQLFMTH_CODE" IS NOT NULL)
12 - filter("AAR"."AAGY_CODE"='3300')
14 - access("ELE"."ECYCLE_CODE"="EC"."CODE")
15 - filter("EC"."AAGY_CODE"='3300' AND "EC"."STATUS"='O')
16 - filter("ELE"."AAGY_CODE"='3300')
17 - filter("AAH"."ROW_NUM"(+)=1)
18 - filter(ROW_NUMBER() OVER ( PARTITION BY "APP_ADMIN_HOLDS"."AAGY_CODE","APP_ADMIN_HOLDS"."AENT
_SYS_ID" ORDER BY "APP_ADMIN_HOLDS"."SEQ_NUM" DESC )<=1)
19 - filter("APP_ADMIN_HOLDS"."STOP_DATE" IS NULL AND "APP_ADMIN_HOLDS"."AAGY_CODE"='3300')
22 - filter("PRIORITY_RNK"."RNK"=1)
23 - filter(RANK() OVER ( PARTITION BY "$vm_view"."AAGY_CODE","$vm_view"."AENT_SYS_ID","$vm_view".
"QQLFMTH_CODE","$vm_view"."ALICTP_CODE" ORDER BY "$vm_view"."PRIORITY")<=1)
25 - filter(RANK() OVER ( PARTITION BY "$vm_view"."AAGY_CODE","$vm_view"."AENT_SYS_ID","$vm_view".
"QQLFMTH_CODE","$vm_view"."ALICTP_CODE" ORDER BY "$vm_view"."PRIORITY")<=1)
27 - access("EXM_LIC_EXAM_CYCLES"."ALICTP_CODE"="APP_REQS"."ALICTP_CODE" AND
"EXM_LIC_EXAM_CYCLES"."ELETP_CODE"="APP_REQS"."ELETP_CODE")
28 - filter("EXM_LIC_EXAM_CYCLES"."AAGY_CODE"='3300')
29 - filter("APP_REQS"."STATUS"='P' AND "APP_REQS"."ELETP_CODE" IS NOT NULL AND
"APP_REQS"."AAGY_CODE"='3300')
Note: cpu costing is off
October 30, 2004 - 2:07 pm UTC
Again, I say to you:
<b>analytics are applied AFTER the where clause -- pushing the where "down"
changes the results, changes the answer</b>
<b>a where on a view, is not the same as a VIEW with a WHERE!!!</b>
ops$tkyte@ORA9IR2> create table emp as select * from scott.emp;
Table created.
ops$tkyte@ORA9IR2> create index job_idx on emp(job);
Index created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace view v
2 as
3 select ename, sal, job,
4 sum(sal) over (partition by job) sal_by_job,
5 sum(sal) over (partition by deptno) sal_by_deptno
6 from emp
7 /
View created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace on explain
ops$tkyte@ORA9IR2> select *
2 from v
3 where job = 'CLERK'
4 /
ENAME SAL JOB SAL_BY_JOB SAL_BY_DEPTNO
---------- ---------- --------- ---------- -------------
MILLER 1300 CLERK 4150 8750
JAMES 950 CLERK 4150 9400
SMITH 800 CLERK 4150 10875
ADAMS 1100 CLERK 4150 10875
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 VIEW OF 'V'
2 1 WINDOW (SORT)
3 2 WINDOW (SORT)
4 3 TABLE ACCESS (FULL) OF 'EMP'
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select ename, sal, job,
2 sum(sal) over (partition by job) sal_by_job,
3 sum(sal) over (partition by deptno) sal_by_deptno
4 from emp
5 where job = 'CLERK'
6 /
ENAME SAL JOB SAL_BY_JOB SAL_BY_DEPTNO
---------- ---------- --------- ---------- -------------
MILLER 1300 CLERK 4150 1300
SMITH 800 CLERK 4150 1900
ADAMS 1100 CLERK 4150 1900
JAMES 950 CLERK 4150 950
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 WINDOW (SORT)
2 1 WINDOW (SORT)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
4 3 INDEX (RANGE SCAN) OF 'JOB_IDX' (NON-UNIQUE)
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select ename, sal, sal_by_job
2 from v
3 where job = 'CLERK'
4 /
ENAME SAL SAL_BY_JOB
---------- ---------- ----------
SMITH 800 4150
ADAMS 1100 4150
JAMES 950 4150
MILLER 1300 4150
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 VIEW OF 'V'
2 1 WINDOW (BUFFER)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
4 3 INDEX (RANGE SCAN) OF 'JOB_IDX' (NON-UNIQUE)
<b>by moving the placement of the predicate, you have changed the question here with analytics. I'm not going to look at 15 pages of queries and query plans to tell you where -- but "where in a view" != "where OF of view"</b>
decide which one does the "right thing for you" and use that one. Only one of them give the correct answer in all probability (and I'm always confused when you have "a fast query that works" and a "slow query that works".... aren't you "done" at that point? you have "a fast query that works")
Pushing predicates Oracle 9i
Christo Kutrovsky, November 10, 2004 - 3:59 pm UTC
Tom,
My specific case is a little complicated, so I will just simplify it here.
I have:
select distinct col1, col2
from big_fact_table
where dimension_key = (select id from dim where...)
and fact_value not in ('val1', 'val2', 'val3')
...
The resulting plan is a hash join of DIM with FACT executed in PARALLEL.
The sorting is in parallel too.
This is very fast, executes in 37 seconds and the operation is purelly CPU limited. The fact table is 47 million rows.
The dimension_key predicate alone results in eliminating 94% of the data. The table is compressed, and can fit in the buffer cache. table is 1.5 gb, buffer cache is 3 gb. The box has 4 CPUs.
The bitmap index on the dimension key is 160 Mb.
I've been trying all kinds of plans, partitioning and access path, and the fastest result is FULL scanning the FACT table in parallel (table has cache=Y), hash joining to the DIM table and then eliminating the "not in" values.
The only way I was able to achieve this execution plan is by using rownum:
select * from (
select distinct col1, col2
from big_fact_table
where dimension_key = (select id from dim where...)
and rownum > 0
)
where fact_value not in ('val1', 'val2', 'val3')
The problem with that execution plan is rownum, which causes everything after it to be done serailly. Both the not in filtering and the sort unique.
It appears that Oracle can do the HASH JOIN in a very CPU efficient way, eliminating rows much faster the the not in values.
I tried all combinations I could think off with NO_MERGE, NO_PUSH_SUBQ and ORDERED_PREDICATES without success.
Any suggestions ?
November 10, 2004 - 8:29 pm UTC
confused -- is it the cpu of doing the "not in" that you are trying to reduce here?
seems strange that it would hash join to what it knows is a single row -- or is that a correlated subquery there?
Ooops
Christo Kutrovsky, November 11, 2004 - 12:36 pm UTC
Sorry Tom, my bad.
where dimension_key = (select ..
is wrong, it 's "in"
where dimension_key in (select ..
and the subquery returns ~20 000 rows
select * from (
select distinct col1, col2
from big_fact_table
where dimension_key in (select id from dim where...)
and rownum > 0
)
where fact_value not in ('val1', 'val2', 'val3')
>is it the cpu of doing the "not in" that you are trying to >reduce here?
Yes.
November 11, 2004 - 3:02 pm UTC
have you tried NO_MERGE instead of rownum?
NO_MERGE
Christo Kutrovsky, November 11, 2004 - 3:29 pm UTC
Yes I have.
An extra row is added to the explain plan "VIEW" (same as with rownum), but the predicates are still shown down at the full table scan.
And the timings are the same as the original query.
SELECT STATEMENT, GOAL = CHOOSE
SORT AGGREGATE
SORT AGGREGATE Other tag=PARALLEL_TO_SERIAL
VIEW Other tag=PARALLEL_COMBINED_WITH_PARENT
HASH JOIN Other tag=PARALLEL_COMBINED_WITH_PARENT Access predicates="B"."DEMO_SUBTYPE1_ID"="K"."DEMO_SUBTYPE1_ID"
TABLE ACCESS FULL Other tag=PARALLEL_FROM_SERIAL Filter predicates="K"."COUNTRY_NUM"='105'
TABLE ACCESS FULL Other tag=PARALLEL_COMBINED_WITH_PARENT Filter predicates="B"."DEMO_SUBTYPE1_ID" IS NOT NULL AND "B"."COL1"<>'val1' AND "B"."COL1"<>'val2'
vs the one with rownum:
SELECT STATEMENT, GOAL = CHOOSE
SORT AGGREGATE
VIEW Filter predicates="T"."COL1"<>'val1' AND "T"."COL1"<>'val2'
COUNT
FILTER Filter predicates=ROWNUM>0
HASH JOIN Other tag=PARALLEL_TO_SERIAL Access predicates="B"."DEMO_SUBTYPE1_ID"="K"."DEMO_SUBTYPE1_ID"
TABLE ACCESS FULL Other tag=PARALLEL_FROM_SERIAL Filter predicates="K"."COUNTRY_NUM"='105'
TABLE ACCESS FULL Other tag=PARALLEL_COMBINED_WITH_PARENT Filter predicates="B"."DEMO_SUBTYPE1_ID" IS NOT NULL
November 11, 2004 - 4:23 pm UTC
with the inline view? show me the query...
that is showing the view is built and then the filter is applied? seems to be what you asked for?
rownum>=0 trick
A reader, December 07, 2004 - 10:58 pm UTC
I have a query which performs satisfactorily.
I add a predicate to it
and col1 not like '%something%'
and the query plan changes completely, performance tanks!
If I add your 'rownum>=0' to the existing query and add my new predicate on top of that, everything works great. The rownum forces the inner (satisfactory) query to get materialized and the filter is applied on the resulting rows.
Question: Is it wise to add this 'rownum>=0' predicate on the view itself? If this view is joined to something else, I guess it would interfere with the CBO ability to generate a optimal plan since it is forced to materialize the view?
Any other alternative that does the same thing as this 'rownum>=0' trick?
Thanks
December 08, 2004 - 10:19 am UTC
it prevents view merging and predicate pushing from happening.
there are hints that can prevent merging as well.
GROUP BY
A reader, December 08, 2004 - 4:08 pm UTC
How about a view/query having a group by?
When I do
select * from (
select col1,col2,sum(col3) group by col1,col2 having count(*)>5
) where col1='foo'
I find that the col1='foo' predicate is pushed into the inner query.
Shouldnt the 'having' clause or even just the group by prevent the predicate from being pushed?
Thanks
December 09, 2004 - 12:48 pm UTC
no, not in this case since you are grouping by col1 - whereing on col1 is "safe"
Safe?
A reader, December 09, 2004 - 1:37 pm UTC
Not sure I understand. Are you saying the predicate on col1 will be pushed into the view or it will not be?
December 09, 2004 - 3:25 pm UTC
it can and will be pushed (you said that already?)
and I said "it is safe to do it" because you group by it. it is actually totally "merged" -- no view step at all:
ops$tkyte@ORA10GR1> create table t ( c1 int, c2 int, c3 int );
Table created.
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> delete from plan_table;
3 rows deleted.
ops$tkyte@ORA10GR1> explain plan for
2 select *
3 from (
4 select c1, c2, sum(c3) from t
5 group by c1, c2
6 )
7 where c1 = 5;
Explained.
ops$tkyte@ORA10GR1> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 1028120241
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 3 (34)| 00:00:01 |
| 1 | SORT GROUP BY | | 1 | 39 | 3 (34)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T | 1 | 39 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C1"=5)
Note
-----
- dynamic sampling used for this statement
18 rows selected.
oramag´s article
A reader, March 22, 2005 - 7:26 pm UTC
Hi
The last issue of Oracle Magazines you explained about view merging and predicate pushing. I have however a question about view merging. In one of your examples (I believe the third one) you had this view definition
select * from whatever
order by whatever
you said that because of order by the view cannot be merged. How so? What is the difference between
select * from view where col = 'X'
and
select *
from (select * from whatever where col = 'X' order by whatever )
Also what do you mean by "SEMANTICS" changes when rownum is involved (for predicate pushing). What is Semantics? The result set?
March 23, 2005 - 1:10 am UTC
we cannot merge:
select *
from t1, (select * from t2 order by x) t2
where t1.y = t2.y
and t2.z = 5;
into:
select *
from t1, t2
where t1.y = t2.y
and t2.z = 5
order by t2.x;
but we could predicate push it into:
select *
from t1, (select * from t2 where z = 5 order by x) t2
where t1.y = t2.y;
that is what I meant (think "more complex, lots of tables, not just one or two but in general)
semantics is "the meaning", "the answer". it would change the answer to the question... which means "it would be wrong"
they would not be semantically equivalent.
why they cannot be merged
A reader, March 23, 2005 - 3:17 am UTC
Hi
I dont get it, why these the first query cannot be merged
select *
from t1, (select * from t2 order by x) t2
where t1.y = t2.y
and t2.z = 5;
into:
select *
from t1, t2
where t1.y = t2.y
and t2.z = 5
order by t2.x;
Say we have these two tables:
t2
x y z
-- -- --
3 2 5
1 2 5
2 3 4
4 1 8
t1
y w
-- --
2 5
Are they semantically wrong?
March 23, 2005 - 8:47 am UTC
because of the order by -- the order by "by definition" prevents merging.
order by and view merging
A reader, March 25, 2005 - 8:45 am UTC
Hi
From 9iR2 documentation (performance tuning guide) it states this
The optimizer can merge a view into a referencing query block when the view has one or more base tables, provided the view does not contain any of the following:
Set operators (UNION, UNION ALL, INTERSECT, MINUS)
A CONNECT BY clause
A ROWNUM pseudocolumn
Aggregate functions (AVG, COUNT, MAX, MIN, SUM) in the select list
Doesnt say an order by clause makes a view nonmergeable :-?
March 25, 2005 - 6:18 pm UTC
so the list is apparently "non-exhaustive"
doesn't say analytics either
new 1: explain plan for select * from (select empno, count(*) over (partition by deptno) cnt from scott.emp)
Explained.
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | VIEW | | | | |
| 2 | WINDOW SORT | | | | |
| 3 | TABLE ACCESS FULL | EMP | | | |
--------------------------------------------------------------------
Note: rule based optimization
sometimes is can predicate push, sometimes it cannot:
new 1: explain plan for select * from (select empno, deptno, count(*) over (partition by deptno) cnt from scott.emp) where empno = 42
Explained.
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
|* 1 | VIEW | | | | |
| 2 | WINDOW SORT | | | | |
| 3 | TABLE ACCESS FULL | EMP | | | |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_001"."EMPNO"=42)
Note: rule based optimization
16 rows selected.
No push possible...
4 rows deleted.
old 1: explain plan for &1
new 1: explain plan for select * from (select empno, deptno, count(*) over (partition by deptno) cnt from scott.emp) where deptno = 42
Explained.
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | VIEW | | | | |
| 2 | WINDOW SORT | | | | |
|* 3 | TABLE ACCESS FULL | EMP | | | |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("EMP"."DEPTNO"=42)
Note: rule based optimization
16 rows selected.
pushed by not merged...
I'm sure if I thought long enough, there would be more..........
10g documentation
A reader, March 26, 2005 - 8:06 am UTC
I was trying to find out more informations about view merging and predicate pushing in 10g documentation. I noticed that in 9i the tuning guide explained how Oracle transform queries like IN, View merge, predicate pushing etc but they all disappeared in 10g documentation. How so?
March 26, 2005 - 9:21 am UTC
it still talks about view merging, predicate pushing, subquery unnesting -- but I suppose in the vein of "make the docs easier to digest and more readable", they took out some of the details that were not really necessary to explain in order to understand the concepts.
Predicate pushing
A reader, April 14, 2005 - 12:27 am UTC
Is it always true that pushing a predicate down to an inline view, wherever possible, provides a performance improvement.That is, an evaluation of a predicate as early as possible should be made. I've heard of "pushing down" predicates, never heard of anything like "pushing up".
April 14, 2005 - 7:36 am UTC
In general, evaluating the predicate as soon as possible is something that should be done.
Is it ALWAYS better? No, there are rare cases when it is best to "do the inline view" and then "apply the predicate"
select f(x), y
from (select x, y from t where y > 5)
where f(x) > 0;
I might not want that "merged" (merge includes push) to be
select f(x), y from t where y > 5 and f(x) > 0;
if f(x) is expensive to perform, but is assumed to be more selective for example.
But in most all cases, predicate pushing is desirable. And many times when it doesn't happen, we start calling views "evil things" (but it is a false label because what I find is that when the predicate isn't pushed, it is mostly because pushing it changes the answer. So the view query and the non-view query people compare-- many times are not the same query...)
Thanks - cleared a question I had for quite some time ...
A reader, April 15, 2005 - 2:15 am UTC
push predicate question
Rumburak, May 09, 2005 - 11:55 am UTC
Hi Tom,
I have this Select:
SELECT t.*
FROM test_jk t,
( SELECT gesft_bzgn_vtr_id,
mandt_id,
MAX( dgbdat )
FROM ( SELECT gesft_bzgn_vtr_id,
mandt_id,
dgbdat
FROM t_gesft_bzgn_vtr_kvs
UNION ALL
SELECT gesft_bzgn_vtr_id,
mandt_id,
dgbdat
FROM h_gesft_bzgn_vtr_kvs
)
GROUP BY
gesft_bzgn_vtr_id,
mandt_id
) v
WHERE v.gesft_bzgn_vtr_id = t.gesft_bzgn_vtr_id
AND v.mandt_id = t.mandt_id
The Table test_jk contains 1 row, the other tables a lot. They were indexes on the gesft_bzgn_vtr_id, mandt_id combination on the big tables. When I start the query above I got a full table scan on the table in the v subquery:
But when I use a bind variable in the query:
SELECT t.*
FROM test_jk t,
( SELECT gesft_bzgn_vtr_id,
mandt_id,
MAX( dgbdat )
FROM ( SELECT gesft_bzgn_vtr_id,
mandt_id,
dgbdat
FROM t_gesft_bzgn_vtr_kvs
UNION ALL
SELECT gesft_bzgn_vtr_id,
mandt_id,
dgbdat
FROM h_gesft_bzgn_vtr_kvs
)
GROUP BY
gesft_bzgn_vtr_id,
mandt_id
) v
WHERE v.gesft_bzgn_vtr_id = t.gesft_bzgn_vtr_id
AND v.mandt_id = t.mandt_id
AND v.gesft_bzgn_vtr_id = :a
AND v.mandt_id = :b
I could use the index on the big tables. Why can't the optimizer uses the index in the first query?
Bye,
Rumbu
May 09, 2005 - 12:25 pm UTC
are you using the cbo.
Test Case
Rumburak, May 09, 2005 - 2:59 pm UTC
Tom,
I use the CBO. I built a test case for you on my home pc:
SQL*Plus: Release 10.1.0.3.0 - Production on Mon May 9 16:38:59 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> @c:\temp\1
SQL>
SQL> drop table t_gesft_bzgn_vtr_kvs;
Table dropped.
SQL> drop table h_gesft_bzgn_vtr_kvs;
Table dropped.
SQL> drop table test_jk;
Table dropped.
SQL>
SQL> create table t_gesft_bzgn_vtr_kvs
2 ( gesft_bzgn_vtr_id number,
3 mandt_id number,
4 dgbdat date,
5 constraint pk_t_gesft_bzgn_vtr_id primary key ( gesft_bzgn_vtr_id, mandt_id )
6 )
7 ;
Table created.
SQL>
SQL> create table h_gesft_bzgn_vtr_kvs
2 ( gesft_bzgn_vtr_id number,
3 mandt_id number,
4 dhln number,
5 dgbdat date,
6 constraint pk_h_gesft_bzgn_vtr_id primary key ( gesft_bzgn_vtr_id, mandt_id )
7 )
8 ;
Table created.
SQL>
SQL> insert into t_gesft_bzgn_vtr_kvs select object_id, 1, created from all_objects;
59985 rows created.
SQL>
SQL> insert into h_gesft_bzgn_vtr_kvs select object_id, 1, 1, created from all_objects;
59985 rows created.
SQL>
SQL> create table test_jk as select gesft_bzgn_vtr_id, mandt_id from t_gesft_bzgn_vtr_kvs where rownum < 2;
Table created.
SQL>
SQL> analyze table t_gesft_bzgn_vtr_kvs compute statistics for table for all indexes for all columns;
Table analyzed.
SQL>
SQL> analyze table h_gesft_bzgn_vtr_kvs compute statistics for table for all indexes for all columns;
Table analyzed.
SQL>
SQL> analyze table test_jk compute statistics for table for all indexes for all columns;
Table analyzed.
SQL>
SQL> SET AUTOTRACE ON EXPLAIN
SQL>
SQL> SELECT t.*
2 FROM test_jk t,
3 ( SELECT gesft_bzgn_vtr_id,
4 mandt_id,
5 MAX( dgbdat )
6 FROM ( SELECT gesft_bzgn_vtr_id,
7 mandt_id,
8 dgbdat
9 FROM t_gesft_bzgn_vtr_kvs
10 UNION ALL
11 SELECT gesft_bzgn_vtr_id,
12 mandt_id,
13 dgbdat
14 FROM h_gesft_bzgn_vtr_kvs
15 )
16 GROUP BY
17 gesft_bzgn_vtr_id,
18 mandt_id
19 ) v
20 WHERE v.gesft_bzgn_vtr_id = t.gesft_bzgn_vtr_id
21 AND v.mandt_id = t.mandt_id
22 ;
GESFT_BZGN_VTR_ID MANDT_ID
----------------- ----------
407 1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=111 Card=119970 By
tes=3719070)
1 0 MERGE JOIN (Cost=111 Card=119970 Bytes=3719070)
2 1 VIEW (Cost=107 Card=119970 Bytes=3119220)
3 2 SORT (GROUP BY) (Cost=107 Card=119970 Bytes=4198950)
4 3 VIEW (Cost=98 Card=119970 Bytes=4198950)
5 4 UNION-ALL
6 5 TABLE ACCESS (FULL) OF 'T_GESFT_BZGN_VTR_KVS' (T
ABLE) (Cost=42 Card=59985 Bytes=779805)
7 5 TABLE ACCESS (FULL) OF 'H_GESFT_BZGN_VTR_KVS' (T
ABLE) (Cost=56 Card=59985 Bytes=779805)
8 1 SORT (JOIN) (Cost=4 Card=1 Bytes=5)
9 8 TABLE ACCESS (FULL) OF 'TEST_JK' (TABLE) (Cost=3 Card=
1 Bytes=5)
SQL>
SQL>
SQL> SELECT t.*
2 FROM test_jk t,
3 ( SELECT gesft_bzgn_vtr_id,
4 mandt_id,
5 MAX( dgbdat )
6 FROM ( SELECT gesft_bzgn_vtr_id,
7 mandt_id,
8 dgbdat
9 FROM t_gesft_bzgn_vtr_kvs
10 UNION ALL
11 SELECT gesft_bzgn_vtr_id,
12 mandt_id,
13 dgbdat
14 FROM h_gesft_bzgn_vtr_kvs
15 )
16 GROUP BY
17 gesft_bzgn_vtr_id,
18 mandt_id
19 ) v
20 WHERE v.gesft_bzgn_vtr_id = t.gesft_bzgn_vtr_id
21 AND v.mandt_id = t.mandt_id
22 AND v.gesft_bzgn_vtr_id = 407
23 AND v.mandt_id = 1
24 ;
GESFT_BZGN_VTR_ID MANDT_ID
----------------- ----------
407 1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=2 Bytes=62)
1 0 MERGE JOIN (CARTESIAN) (Cost=7 Card=2 Bytes=62)
2 1 TABLE ACCESS (FULL) OF 'TEST_JK' (TABLE) (Cost=3 Card=1
Bytes=5)
3 1 BUFFER (SORT) (Cost=4 Card=2 Bytes=52)
4 3 VIEW (Cost=4 Card=2 Bytes=52)
5 4 SORT (GROUP BY) (Cost=4 Card=2 Bytes=70)
6 5 VIEW (Cost=4 Card=2 Bytes=70)
7 6 UNION-ALL
8 7 TABLE ACCESS (BY INDEX ROWID) OF 'T_GESFT_BZGN
_VTR_KVS' (TABLE) (Cost=2 Card=1 Bytes=13)
9 8 INDEX (UNIQUE SCAN) OF 'PK_T_GESFT_BZGN_VTR_
ID' (INDEX (UNIQUE)) (Cost=1 Card=1)
10 7 TABLE ACCESS (BY INDEX ROWID) OF 'H_GESFT_BZGN
_VTR_KVS' (TABLE) (Cost=2 Card=1 Bytes=13)
11 10 INDEX (UNIQUE SCAN) OF 'PK_H_GESFT_BZGN_VTR_
ID' (INDEX (UNIQUE)) (Cost=1 Card=1)
GROUP BY
Rumburak, May 11, 2005 - 7:45 am UTC
Hi Tom,
I tested it without the group by. And then the optimizer uses the indexes in the inner select.
Can you explain this behaviour? Why does'nt it work with the pure join? I would have thought that the optimizer mechanism would be the same in both cases.
Bye,
rumbu
May 11, 2005 - 7:59 am UTC
there is a big difference between
'where col1 = 1 and col2 = 2'
and
where col1 = x.col1 and col2 = x.col2
in one case, the optimizer knows "precisely one value"
in the other, the optimizer knows "could be zero, one, two, thousands, ...." of values.
Ok, but...
Rumburak, May 11, 2005 - 8:13 am UTC
...from the statistics the CBO should know, that the joined table contains only one row. In my understanding the processing must be the same than with the variables. And it is the same when I do it without the group by.
In production we have a similiar view with this union select and a group by select around this. We can query this view in a loop. But we can not use it in a join. That's our problem.
May 11, 2005 - 8:53 am UTC
can guess -- but does not know, it could be zero, one or more.
it would be more of an "in" situation and if that is what you want, I would probably be best to do that -- use IN in the two queries before grouping.
a where on a view, is not the same as a VIEW with a WHERE!!!
VA, October 13, 2005 - 8:54 am UTC
I have
create view v as
select
col1,col2,col3,
the_date,max(the_date) over (col1) latest_date
from ....
Then I do
select * from v
where the_date=latest_date;
Would this do what I would like it to do? i.e. Show me all the records where the date equals the latest date for col1.
As per the discussion on this page, would it make a difference if I did this in a view versus directly the query? Am I right to include the analytic function in the view here?
Thanks
October 13, 2005 - 10:51 am UTC
that is absolutely correct.
that has always been true.
your syntax doens't compute, so I cannot answer -- max(the_date) over (col1) ???
I assume you mean:
max(the_date) over (partition by col1)
then, you can use your view V to get the current record(s) for each col1 value.
In order to do this in a query, you would have to code:
select *
from ( select ..., max(the_date) over (partition by col1) max_dt
from t ... )
where the_date = max_dt;
you'd have an INLINE view!
Whats the difference?
A reader, October 13, 2005 - 1:14 pm UTC
[Yes, I meant "max(the_date) over (partition by col1)", sorry about that]
I dont understand. Whats the difference between
select *
from ( select ..., max(the_date) over (partition by col1) max_dt
from t ... )
where the_date = max_dt;
and
create view v as select ...,max(the_date) over (partition by col1) max_dt;
select ... from v where the_date=max_dt;
Wouldnt the predicate get pushed inside in both cases?
Thanks
October 13, 2005 - 4:16 pm UTC
the predicate in this case cannot get pushed. you cannot "where" on an analytic function directly.
those are the "same"
Quotation Mark Fallacy
VA, October 16, 2005 - 1:41 pm UTC
Just finished reading Crimes Against Logic and I'm afraid you are guilty of commiting the Quotation Mark Fallacy!
[Sorry, couldnt resist!]
October 16, 2005 - 4:23 pm UTC
Oh, I know - wrote about that myself, I "abuse" the air quotes heavily ;)
Why not?
VA, October 16, 2005 - 9:28 pm UTC
"the predicate in this case cannot get pushed"
1. Why not?
2. So does this mean that the entire view is materialized and then my predicate is applied? Wont that be terribly slow?
3. I assume that the only predicates that will NOT be pushed are those that involve the analytic columns, predicates involving other columns will be pushed, right?
Thanks
October 17, 2005 - 6:52 am UTC
1) I said why "you cannot "where" on an analytic function directly.". Question was:
.....
select *
from ( select ..., max(the_date) over (partition by col1) max_dt
from t ... )
where the_date = max_dt;
and
create view v as select ...,max(the_date) over (partition by col1) max_dt;
select ... from v where the_date=max_dt;
Wouldnt the predicate get pushed inside in both cases?
..........
for the predicate to get pushed, it would have to be (using the first query)
select ..., max(the_date) over (partition by col1) max_dt
from t
where max(the_date) over (partition by col1) = the_date
and that just "doesn't work"
2) not necessarily - if the query against the view was:
select * from v where the_date = max_date AND COL1 = 42;
then, the "COL1=42" can get pushed. the "the_date = max_date" cannot. So, some things CAN be pushed (when then are permitted OR when they do not change the answer)
Other things CANNOT be pushed because they would change the answer! That, that is not allowable.
3) false. Consider:
ops$tkyte@ORA10GR1> create table emp as select * from scott.emp;
Table created.
ops$tkyte@ORA10GR1> create index job_idx on emp(job);
Index created.
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> create or replace view v
2 as
3 select ename, sal, job,
4 sum(sal) over (partition by job) sal_by_job,
5 sum(sal) over (partition by deptno) sal_by_deptno
6 from emp
7 /
View created.
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> set autotrace on explain
ops$tkyte@ORA10GR1> select *
2 from v
3 where job = 'CLERK'
4 order by ename
5 /
ENAME SAL JOB SAL_BY_JOB SAL_BY_DEPTNO
---------- ---------- --------- ---------- -------------
ADAMS 1100 CLERK 4150 10875
JAMES 950 CLERK 4150 9400
MILLER 1300 CLERK 4150 8750
SMITH 800 CLERK 4150 10875
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=6 Card=14 Bytes=728)
1 0 SORT (ORDER BY) (Cost=6 Card=14 Bytes=728)
2 1 VIEW OF 'V' (VIEW) (Cost=5 Card=14 Bytes=728)
3 2 WINDOW (SORT) (Cost=5 Card=14 Bytes=546)
4 3 WINDOW (SORT) (Cost=5 Card=14 Bytes=546)
5 4 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=546)
ops$tkyte@ORA10GR1> select ename, sal, job,
2 sum(sal) over (partition by job) sal_by_job,
3 sum(sal) over (partition by deptno) sal_by_deptno
4 from emp
5 where job = 'CLERK' order by ename;
ENAME SAL JOB SAL_BY_JOB SAL_BY_DEPTNO
---------- ---------- --------- ---------- -------------
ADAMS 1100 CLERK 4150 1900
JAMES 950 CLERK 4150 950
MILLER 1300 CLERK 4150 1300
SMITH 800 CLERK 4150 1900
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=4 Card=4 Bytes=156)
1 0 SORT (ORDER BY) (Cost=4 Card=4 Bytes=156)
2 1 WINDOW (SORT) (Cost=4 Card=4 Bytes=156)
3 2 WINDOW (BUFFER) (Cost=4 Card=4 Bytes=156)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=4 Bytes=156)
5 4 INDEX (RANGE SCAN) OF 'JOB_IDX' (INDEX) (Cost=1 Card=4)
ops$tkyte@ORA10GR1> select ename, sal, sal_by_job
2 from v
3 where job = 'CLERK'
4 order by ename
5 /
ENAME SAL SAL_BY_JOB
---------- ---------- ----------
ADAMS 1100 4150
JAMES 950 4150
MILLER 1300 4150
SMITH 800 4150
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=3 Card=4 Bytes=140)
1 0 SORT (ORDER BY) (Cost=3 Card=4 Bytes=140)
2 1 VIEW OF 'V' (VIEW) (Cost=2 Card=4 Bytes=140)
3 2 WINDOW (BUFFER) (Cost=2 Card=4 Bytes=104)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=4 Bytes=104)
5 4 INDEX (RANGE SCAN) OF 'JOB_IDX' (INDEX) (Cost=1 Card=4)
ops$tkyte@ORA10GR1> set autotrace off
<b>You see, when pushing of the predicate would change the answer (when we selected both analytics - pushing the "where job = clerk" manually CHANGED the answer!) we cannot push, you wouldn't like it if we did</b>
Bottom line
VA, October 17, 2005 - 8:57 am UTC
Ok your examples showed that Oracle does The Right Thing. It pushes the predicate only when doing so will not change the answer (vs. not pushing it). The Oracle Knows!
Great, but what are we lowly developers to take away from all this stuff? Are there any ROT to glean from all this? Or is everything on a case-by-case basis?
Lets say I create some views that use analytic functions. I broadcast their availability to the user/developer community. What should I write in the "release notes" for these views? "Joe Schmoe says that the view returns a different answer when there is a full moon outside"
Or are you saying that you dont recommend putting analytic functions in views at all?
Thanks
October 17, 2005 - 9:00 am UTC
The "ROT" to glean:
Understand SQL
Understand set based processing
Let go of your procedural mindset for a moment, visualize the data, understand the QUERY.
put analytics in views just as you would unions, rownum, intersect, group by, minus, etc ......
The are not much different. pushing and merging only happens when it could happen - and analytics are not the first sql feature to prevent them.
Some more ROT would be useful!
Rod West, November 27, 2005 - 12:34 pm UTC
We need to lookup up a name in a table and then use the key to lookup data in a number of other tables. We do this by joining to a union all query block that lookups up data in the other tables, as shown in the example below. Most of the time the predicates are pushed into the union all and an efficient query plan is generated. However, if any aggregate function is introduced into the union all query block then this approach does not work.
Is there any way this approach can work? The group by id should allow the predicate to be pushed?
What is the difference between a UNION-ALL and a UNION-ALL PARTITION?
SQL> create table rw1 (id, name) as
2 select rownum id, 'U'||to_char(rownum, '00009') name
3 from (select null from dual connect by level < 10000)
4 /
Table created.
SQL> create unique index rw1i1 on rw1(name)
2 /
Index created.
SQL> create unique index rw1pk on rw1(id)
2 /
Index created.
SQL> create table rw2 (id, dname) as
2 select rownum id, 'D'||to_char(rownum, '00009') name
3 from (select null from dual connect by level < 10000)
4 /
Table created.
SQL> create unique index rw2i1 on rw2(id)
2 /
Index created.
SQL>
SQL> exec dbms_stats.gather_table_stats('RWEST','RW1')
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('RWEST','RW2')
PL/SQL procedure successfully completed.
SQL>
SQL> explain plan for
2 select rw1.name, uqb.dname
3 from rw1,
4 (select id, dname from rw2
5 union all
6 select id, dname from rw2) uqb
7 where rw1.id = uqb.id and rw1.name = '00042'
8 /
Explained.
SQL> select PLAN_TABLE_OUTPUT from table(dbms_xplan.display())
2 /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2021551261
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 46 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 46 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | RW1 | 1 | 11 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | RW1I1 | 1 | | 1 (0)| 00:00:01 |
| 4 | VIEW | | 2 | 24 | 1 (0)| 00:00:01 |
| 5 | UNION-ALL PARTITION | | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| RW2 | 1 | 11 | 2 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | RW2I1 | 1 | | 1 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| RW2 | 1 | 11 | 2 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | RW2I1 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("RW1"."NAME"='00042')
7 - access("ID"="RW1"."ID")
9 - access("ID"="RW1"."ID")
23 rows selected.
SQL> explain plan
2 for
3 select rw1.name, uqb.dname
4 from rw1,
5 (select id, dname from rw2
6 union all
7 select id, max(dname) from rw2 group by id) uqb
8 where rw1.id = uqb.id
9 and rw1.name = '00042'
10 /
Explained.
SQL> select PLAN_TABLE_OUTPUT from table(dbms_xplan.display())
2 /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1481267953
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 58 | | 67 (3)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 58 | | 67 (3)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| RW1 | 1 | 11 | | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | RW1I1 | 1 | | | 1 (0)| 00:00:01 |
|* 4 | VIEW | | 2 | 36 | | 65 (4)| 00:00:01 |
| 5 | UNION-ALL | | | | | | |
| 6 | TABLE ACCESS FULL | RW2 | 9999 | 107K| | 8 (0)| 00:00:01 |
| 7 | SORT GROUP BY | | 9999 | 107K| 408K| 57 (4)| 00:00:01 |
| 8 | TABLE ACCESS FULL | RW2 | 9999 | 107K| | 8 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("RW1"."NAME"='00042')
4 - filter("RW1"."ID"="UQB"."ID")
21 rows selected.
November 27, 2005 - 2:09 pm UTC
the group by set operation will prevent a "join predicate push down". In 10gr2, you can see this in a the 10053 trace pretty easily:
**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in SEL$1 (#0).
PM: Checking validity of predicate move-around in SEL$1 (#0).
JPPD: JPPD bypassed: View not on right-side of outer join
JPPD: JPPD bypassed: View contains a group by.
CBQT: Validity checks failed for 1d1jpnq0g782k.
CVM: Considering view merge in query block SEL$1 (#0)
CVM: Checking validity of merging SET$1 (#0)
CVM: Considering view merge in query block SET$1 (#0)
CVM: Considering view merge in query block SEL$2 (#0)
CVM: Considering view merge in query block SEL$3 (#0)
CVM: CVM bypassed: SET (union, intersect, etc.) operation
Query block (0xb710a6b4) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT "RW1"."NAME" "NAME","UQB"."DNAME" "DNAME" FROM "OPS$TKYTE"."RW1" "RW1", ( (SELECT "RW2"."ID" "ID","RW2"."DNAME" "DNAME" FROM "OPS$TKYTE"."RW2" "RW2") UNION ALL (SELECT "RW2"."ID" "ID",MAX("RW2"."DNAME") "MAX(DNAME)" FROM "OPS$TKYTE"."RW2" "RW2" GROUP BY "RW2"."ID")) "UQB" WHERE "RW1"."ID"="UQB"."ID" AND "RW1"."NAME"='00042'
Query block (0xb710a6b4) unchanged
JPPD: JPPD bypassed: View not on right-side of outer join
JPPD: JPPD bypassed: View contains a group by.
CBQT: Validity checks failed for 1d1jpnq0g782k.
***************
Subquery Unnest
***************
SU: Considering subquery unnesting in query block SEL$1 (#0)
*************************
Set-Join Conversion (SJC)
*************************
SJC: Considering set-join conversion in SEL$1 (#0).
*************************
Set-Join Conversion (SJC)
*************************
SJC: Considering set-join conversion in SET$1 (#0).
SJC: Checking validity of SJC on SET$1 (#0).
SJC: SJC bypassed: Not enabled by hint/parameter.
SJC: Considering set-join conversion in SEL$3 (#0).
SJC: Considering set-join conversion in SEL$2 (#0).
**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in SEL$1 (#0).
PM: Checking validity of predicate move-around in SEL$1 (#0).
PM: PM bypassed: Query block is a set operator pseudo-query block.
PM: Passed validity checks.
FPD: Considering simple filter push in SEL$1 (#0)
FPD: Current where clause predicates in SEL$1 (#0) :
"RW1"."ID"="UQB"."ID" AND "RW1"."NAME"='00042'
kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#0)
predicates with check contraints: "RW1"."ID"="UQB"."ID" AND "RW1"."NAME"='00042'
after transitive predicate generation: "RW1"."ID"="UQB"."ID" AND "RW1"."NAME"='00042'
finally: "RW1"."ID"="UQB"."ID" AND "RW1"."NAME"='00042'
JPPD: JPPD bypassed: View contains a group by.
JPPD: JPPD bypassed: View not on right-side of outer join
FPD: Considering simple filter push in SEL$3 (#0)
FPD: Current where clause predicates in SEL$3 (#0) :
FPD: Considering simple filter push in SEL$2 (#0)
FPD: Current where clause predicates in SEL$2 (#0) :
apadrv-start: call(in-use=772, alloc=16360), compile(in-use=42344, alloc=46248)
kkoqbc-start
: call(in-use=776, alloc=16360), compile(in-use=44708, alloc=46248)
******************************************
Current SQL statement for this session:
select rw1.name, uqb.dname
from rw1,
(select id, dname from rw2
union all
select id, max(dname) from rw2 group by id) uqb
where rw1.id = uqb.id
and rw1.name = '00042'
*******************************************
Legend
The following abbreviations are used by optimizer trace.
CBQT - cost-based query transformation
JPPD - join predicate push-down
FPD - filter push-down
PM - predicate move-around
CVM - complex view merging
SPJ - select-project-join
SJC - set join conversion
SU - subquery unnesting
OBYE - order by elimination
ST - star transformation
....
Can I write this better?
A reader, January 20, 2006 - 3:12 pm UTC
The reason is that I am hitting the view twice!
thanks.
select equipment_total, equipment_kind,
action, out_id, hazards,
damages, voided_date, id, area
from BUS_view
where action <> 'OUT'
and ((voided_date > '19-JAN-2006 16:48:13')
and (voided_date < '20-JAN-2006 09:41:09'))
January 20, 2006 - 4:30 pm UTC
well, you seem to be comparing a string to a date - which is a horrible thing to do, so you appear to be missing a to_date() with an explicit format to convert your string into a valid date.
But beyond that - I don't know why you say "I am hitting the view twice"?
View Merging/Predicate Pushing Example
Su Baba, January 24, 2006 - 4:13 pm UTC
Tom,
I read your article titled "On Rollups, Merges, and Moves" in the March/April 2005 issue of Oracle magazine. There you explained view merging and predicate pushing. I tried to reproduce the example you have there in our Oracle environment, but I'm not getting the same result for the predicate pushing example. Please see the explain plan below for pushable_view (toward the bottom). It does not have the keyword "view". Does that mean the view has been merged?
Also for the explain plan for nonmergeable_view (the second one below), the step with ID = 2 has a "COUNT" keyword. What does that indicate?
thanks
SQL> CREATE TABLE t1 AS
2 SELECT * FROM all_users;
Table created.
SQL>
SQL> CREATE TABLE t2 AS
2 SELECT * FROM all_users;
Table created.
SQL> CREATE OR REPLACE view mergeable_view
2 AS
3 SELECT t1.user_id, t2.username
4 FROM t1, t2
5 WHERE t1.user_id = t2.user_id;
View created.
SQL>
SQL>
SQL> explain plan for
2 SELECT t1.user_id, t2.username
3 FROM t1, t2
4 WHERE t1.user_id = t2.user_id AND
5 t2.username = 'APPS';
Explained.
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
------------------------------------------
Plan hash value: 2959412835
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 43 | 7 (15)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T2 | 1 | 30 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 291 | 3783 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."USER_ID"="T2"."USER_ID")
2 - filter("T2"."USERNAME"='APPS')
Note
-----
- dynamic sampling used for this statement
20 rows selected.
SQL> CREATE OR REPLACE view nonmergeable_view
2 AS
3 SELECT t1.user_id, t2.username, rownum r
4 FROM t1, t2
5 WHERE t1.user_id = t2.user_id;
View created.
SQL>
SQL>
SQL> explain plan for
2 SELECT * FROM nonmergeable_view
3 WHERE username = 'APPS';
Explained.
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
------------------------------------------
Plan hash value: 1301440073
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 291 | 12513 | 7 (15)| 00:00:01 |
|* 1 | VIEW | NONMERGEABLE_VIEW | 291 | 12513 | 7 (15)| 00:00:01 |
| 2 | COUNT | | | | | |
|* 3 | HASH JOIN | | 291 | 12513 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T1 | 291 | 3783 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| T2 | 291 | 8730 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("USERNAME"='APPS')
3 - access("T1"."USER_ID"="T2"."USER_ID")
Note
-----
- dynamic sampling used for this statement
22 rows selected.
SQL> CREATE OR REPLACE view pushable_view
2 AS
3 SELECT t1.user_id, t2.username
4 FROM t1, t2
5 WHERE t1.user_id = t2.user_id
6 ORDER BY t1.user_id, t2.username;
View created.
SQL>
SQL> explain plan for
2 SELECT * FROM pushable_view
3 WHERE username = 'APPS';
Explained.
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
------------------------------------------
Plan hash value: 2097371403
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 8 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 43 | 8 (25)| 00:00:01 |
|* 2 | HASH JOIN | | 1 | 43 | 7 (15)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 30 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T1 | 291 | 3783 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."USER_ID"="T2"."USER_ID")
3 - filter("T2"."USERNAME"='APPS')
Note
-----
- dynamic sampling used for this statement
21 rows selected.
SQL>
SQL>
SQL>
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
January 24, 2006 - 9:04 pm UTC
10gr2 has a new "order by" optimization in it.
"things change"....
complex view merging with UNION ALL
A reader, March 05, 2007 - 8:03 am UTC
Hi Tom
I am aware we can use MERGE and NO_MERGE to force complex view merging.
I have a view which is like this
create or replace view test1
....
as
select ...
from a, c
where a....
union all
select ...
from a, b
where a.....
Since it contains union all I forced complex view merging by using MERGE
select /*+ MERGE(v1) */
*
from test1
where test1.xxx = '?'
I was wondering instead of using hints outside the view, is there a possible way to hint the view so all queries run against the view can use complex view merging?
March 05, 2007 - 2:00 pm UTC
"I am aware we can use MERGE and NO_MERGE to force complex view merging." when it is possible
but as long as merging defaults to true (database parameter), you don't need the hint.
In general you do not need hints.
it'll push the predicate down when it can
Trying to get predicate pushing
Ross Campbell, May 14, 2007 - 4:32 pm UTC
Tom,
I am trying to understand why predicate pushing is not occurring in a particular query, or so it appears to me. Oracle version is 9.2.0.8 on HP-UX 11.11.
Firstly, the q220 script, below, demonstrates the query running for 10 queued ids (10 rows in the queue table), takes about 55 seconds. The q221 script shows the query running for one particular id, takes 0.15 seconds. The q222 script shows the query executed in a pl/sql block with an outer loop going through the queue rows and an inner loop that is essentially the same as q221 and this only takes 0.17 seconds.
It appears to me that q221 demonstrates predicate pushing into the v_do_total view in lines 31-34 of the plan because indexes are used and cardinalities are small. However, the corresponding lines (25-26) in the plan for q220 show full table scans and cardinalities of 300,000.
I certainly would like to accomplish this query in one (set-based) SQL statement rather than the pl/sql approach of q222. The speed at which q222 runs seems to imply that there should be a way to get that speed (or better) with a single SQL statement. How can I make that happen?
Thanks,
Ross
ross@PRV3.ECAT.MANTECHWVA.COM> @q220
ross@PRV3.ECAT.MANTECHWVA.COM> set timing on
ross@PRV3.ECAT.MANTECHWVA.COM> set linesize 130
ross@PRV3.ECAT.MANTECHWVA.COM> set autotrace traceonly
ross@PRV3.ECAT.MANTECHWVA.COM> select * from (
2 with cp as (
3 select cp1.cart_products_id, cp1.quantity, cp1.part_number, cp1.product_name,
4 cp1.requisition_number,
5 round(cp1.crr_percentage * (cp1.unit_price + cpo.option_price), 2) unit_price
6 from cart_products cp1
7 left outer join (
8 select cart_products_id, sum(nvl(option_price,0)) option_price
9 from cart_product_options
10 group by cart_products_id
11 ) cpo
12 on cpo.cart_products_id=cp1.cart_products_id
13 ),
14 dol as (
15 select dol12.delivery_order_lines_id, dol12.delivery_orders_id,
16 cart_products_id, dol12.status_code, dol12.quantity_filled, dol12.line_number,
17 cp.quantity, cp.part_number, cp.product_name, cp.requisition_number,
18 cp.unit_price,
19 nvl(dol12.quantity_filled, cp.quantity)*cp.unit_price ext_price
20 from delivery_order_lines dol12
21 inner join cp using(cart_products_id)
22 ),
23 v_do_total as (
24 select dol22.delivery_orders_id,
25 sum(ext_price) total
26 from (
27 select nvl(dol13.quantity_filled, vw.quantity)*round(crr_percentage*unit_price, 2) ext_price
28 ,dol13.delivery_orders_id
29 from (select cart_products_id, quantity, crr_percentage
30 , cp.unit_price+nvl(cpo.options_price, 0) unit_price
31 from cart_products cp
32 left outer join (
33 select cart_products_id, sum(nvl(option_price, 0)) options_price
34 from cart_product_options
35 group by cart_products_id
36 ) cpo
37 using (cart_products_id)
38 ) vw
39 inner join delivery_order_lines dol13
40 using (cart_products_id)
41 where dol13.status_code in ('AC','AR','VS')
42 ) dol22
43 group by dol22.delivery_orders_id
44 ),
45 dos as(
46 select delivery_orders_id, dos_tab.carts_id, dos_tab.contracts_id,
47 dos_tab.summary_requisition_number, dos_tab.status_code, total, fy.month, fy.year
48 from delivery_orders dos_tab
49 inner join v_do_total
50 using(delivery_orders_id)
51 left outer join(
52 select delivery_orders_id,
53 to_number(to_char(max(timestamp) over(partition by delivery_orders_id), 'mm')) month,
54 to_number(to_char(max(timestamp) over(partition by delivery_orders_id), 'yy')) year
55 from delivery_order_events
56 where events_id=30 --delivery order accepted by supplier
57 ) fy
58 using(delivery_orders_id)
59 )
60 select dos.delivery_orders_id id,
61 dos.status_code do_status_code,
62 dos.summary_requisition_number,
63 dos.total,
64 sup.supplier_name,
65 case
66 when pinfo.payment_methods_id=1 --credit card
67 then 'C'
68 else 'L'
69 end payment_type,
70 sc.wrair_apc,
71 dol.part_number,
72 dol.product_name,
73 dol.quantity_filled,
74 dol.unit_price,
75 case
76 when dol.status_code='IR'
77 then dol.unit_price*dol.quantity
78 else dol.ext_price
79 end ext_price,
80 to_char(
81 mod(case
82 when dos.month<=9
83 then dos.year
84 else dos.year+1
85 end,
86 100),
87 'fm09') fiscal_year,
88 dol.requisition_number,
89 case
90 when dol.status_code='IR'
91 then 'K'
92 else 'F'
93 end status_code,
94 c.financial_transaction_number,
95 dol.line_number,
96 decode(dol.line_number,1,1,2) level_num
97 from
98 (
99 (
100 (
101 (
102 (
103 (
104 (
105 (
106 (/*delivery_orders*/ dos
107 inner join dol
108 on dol.delivery_orders_id=dos.delivery_orders_id
109 )
110 )
111 )
112 inner join carts c
113 on c.carts_id=dos.carts_id
114 )
115 inner join payment_information pinfo
116 on pinfo.payment_information_id=c.payment_information_id
117 )
118 inner join contracts con
119 on con.contracts_id=dos.contracts_id
120 )
121 inner join suppliers sup
122 on con.suppliers_id=sup.suppliers_id
123 )
124 )
125 ),
126 system_configuration sc
127 ) v_data
128 inner join queue q
129 on q.queue_id=v_data.id
130 and q.queue_name='APCReport'
131 ;
60 rows selected.
Elapsed: 00:00:54.98
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=18857 Card=60 Bytes=13620)
1 0 HASH JOIN (Cost=18857 Card=60 Bytes=13620)
2 1 TABLE ACCESS (FULL) OF 'QUEUE' (Cost=2 Card=10 Bytes=220)
3 1 VIEW (Cost=18772 Card=300000 Bytes=61500000)
4 3 HASH JOIN (Cost=18772 Card=300000 Bytes=68700000)
5 4 TABLE ACCESS (FULL) OF 'SUPPLIERS' (Cost=2 Card=2 Bytes=28)
6 4 HASH JOIN (Cost=18683 Card=300000 Bytes=64500000)
7 6 TABLE ACCESS (FULL) OF 'PAYMENT_INFORMATION' (Cost=2 Card=2 Bytes=12)
8 6 HASH JOIN (OUTER) (Cost=18596 Card=300000 Bytes=62700000)
9 8 HASH JOIN (Cost=16224 Card=300000 Bytes=54900000)
10 9 TABLE ACCESS (FULL) OF 'CART_PRODUCTS' (Cost=231 Card=300000 Bytes
=14100000)
11 9 HASH JOIN (Cost=13480 Card=300000 Bytes=40800000)
12 11 HASH JOIN (OUTER) (Cost=12971 Card=50000 Bytes=5850000)
13 12 HASH JOIN (Cost=12709 Card=50000 Bytes=3900000)
14 13 TABLE ACCESS (FULL) OF 'CARTS' (Cost=19 Card=50000 Bytes=600
000)
15 13 HASH JOIN (Cost=12483 Card=50000 Bytes=3300000)
16 15 TABLE ACCESS (FULL) OF 'CONTRACTS' (Cost=2 Card=2 Bytes=12
)
17 15 HASH JOIN (Cost=12476 Card=50000 Bytes=3000000)
18 17 MERGE JOIN (CARTESIAN) (Cost=12161 Card=50000 Bytes=1550
000)
19 18 TABLE ACCESS (FULL) OF 'SYSTEM_CONFIGURATION' (Cost=2
Card=1 Bytes=5)
20 18 BUFFER (SORT) (Cost=12159 Card=50000 Bytes=1300000)
21 20 VIEW (Cost=12159 Card=50000 Bytes=1300000)
22 21 SORT (GROUP BY) (Cost=12159 Card=50000 Bytes=29500
00)
23 22 HASH JOIN (OUTER) (Cost=1597 Card=300000 Bytes=1
7700000)
24 23 HASH JOIN (Cost=1046 Card=300000 Bytes=9900000
)
25 24 TABLE ACCESS (FULL) OF 'DELIVERY_ORDER_LINES
' (Cost=122 Card=300000 Bytes=4800000)
26 24 TABLE ACCESS (FULL) OF 'CART_PRODUCTS' (Cost
=231 Card=300000 Bytes=5100000)
27 23 VIEW (Cost=4 Card=1 Bytes=26)
28 27 SORT (GROUP BY) (Cost=4 Card=1 Bytes=26)
29 28 TABLE ACCESS (FULL) OF 'CART_PRODUCT_OPTIO
NS' (Cost=2 Card=1 Bytes=26)
30 17 TABLE ACCESS (FULL) OF 'DELIVERY_ORDERS' (Cost=61 Card=1
00000 Bytes=2900000)
31 12 VIEW (Cost=26 Card=100000 Bytes=3900000)
32 31 WINDOW (BUFFER) (Cost=26 Card=100000 Bytes=1600000)
33 32 INDEX (FULL SCAN) OF 'DELIVERY_ORDER_EVENTS_IDX' (NON-UNIQ
UE) (Cost=26 Card=100000 Bytes=1600000)
34 11 TABLE ACCESS (FULL) OF 'DELIVERY_ORDER_LINES' (Cost=122 Card=300
000 Bytes=5700000)
35 8 VIEW (Cost=4 Card=1 Bytes=26)
36 35 SORT (GROUP BY) (Cost=4 Card=1 Bytes=26)
37 36 TABLE ACCESS (FULL) OF 'CART_PRODUCT_OPTIONS' (Cost=2 Card=1 Byt
es=26)
Statistics
----------------------------------------------------------
0 recursive calls
87 db block gets
14700 consistent gets
71338 physical reads
0 redo size
3436 bytes sent via SQL*Net to client
2169 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
2 sorts (memory)
3 sorts (disk)
60 rows processed
ross@PRV3.ECAT.MANTECHWVA.COM> @q221
ross@PRV3.ECAT.MANTECHWVA.COM> set timing on
ross@PRV3.ECAT.MANTECHWVA.COM> set linesize 130
ross@PRV3.ECAT.MANTECHWVA.COM>
ross@PRV3.ECAT.MANTECHWVA.COM> var x number
ross@PRV3.ECAT.MANTECHWVA.COM> begin
2 select queue_id into :x
3 from queue
4 where queue_name='APCReport'
5 and rownum=1;
6 end;
7 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
ross@PRV3.ECAT.MANTECHWVA.COM>
ross@PRV3.ECAT.MANTECHWVA.COM> set autotrace traceonly
ross@PRV3.ECAT.MANTECHWVA.COM> select * from (
2 with cp as (
3 select cp1.cart_products_id, cp1.quantity, cp1.part_number, cp1.product_name,
4 cp1.requisition_number,
5 round(cp1.crr_percentage * (cp1.unit_price + cpo.option_price), 2) unit_price
6 from cart_products cp1
7 left outer join (
8 select cart_products_id, sum(nvl(option_price,0)) option_price
9 from cart_product_options
10 group by cart_products_id
11 ) cpo
12 on cpo.cart_products_id=cp1.cart_products_id
13 ),
14 dol as (
15 select dol12.delivery_order_lines_id, dol12.delivery_orders_id,
16 cart_products_id, dol12.status_code, dol12.quantity_filled, dol12.line_number,
17 cp.quantity, cp.part_number, cp.product_name, cp.requisition_number,
18 cp.unit_price,
19 nvl(dol12.quantity_filled, cp.quantity)*cp.unit_price ext_price
20 from delivery_order_lines dol12
21 inner join cp using(cart_products_id)
22 ),
23 v_do_total as (
24 select dol22.delivery_orders_id,
25 sum(ext_price) total
26 from (
27 select nvl(dol13.quantity_filled, vw.quantity)*round(crr_percentage*unit_price, 2) ext_price
28 ,dol13.delivery_orders_id
29 from (select cart_products_id, quantity, crr_percentage
30 , cp.unit_price+nvl(cpo.options_price, 0) unit_price
31 from cart_products cp
32 left outer join (
33 select cart_products_id, sum(nvl(option_price, 0)) options_price
34 from cart_product_options
35 group by cart_products_id
36 ) cpo
37 using (cart_products_id)
38 ) vw
39 inner join delivery_order_lines dol13
40 using (cart_products_id)
41 where dol13.status_code in ('AC','AR','VS')
42 ) dol22
43 group by dol22.delivery_orders_id
44 ),
45 dos as(
46 select delivery_orders_id, dos_tab.carts_id, dos_tab.contracts_id,
47 dos_tab.summary_requisition_number, dos_tab.status_code, total, fy.month, fy.year
48 from delivery_orders dos_tab
49 inner join v_do_total
50 using(delivery_orders_id)
51 left outer join(
52 select delivery_orders_id,
53 to_number(to_char(max(timestamp) over(partition by delivery_orders_id), 'mm')) month,
54 to_number(to_char(max(timestamp) over(partition by delivery_orders_id), 'yy')) year
55 from delivery_order_events
56 where events_id=30 --delivery order accepted by supplier
57 ) fy
58 using(delivery_orders_id)
59 )
60 select dos.delivery_orders_id id,
61 dos.status_code do_status_code,
62 dos.summary_requisition_number,
63 dos.total,
64 sup.supplier_name,
65 case
66 when pinfo.payment_methods_id=1 --credit card
67 then 'C'
68 else 'L'
69 end payment_type,
70 sc.wrair_apc,
71 dol.part_number,
72 dol.product_name,
73 dol.quantity_filled,
74 dol.unit_price,
75 case
76 when dol.status_code='IR'
77 then dol.unit_price*dol.quantity
78 else dol.ext_price
79 end ext_price,
80 to_char(
81 mod(case
82 when dos.month<=9
83 then dos.year
84 else dos.year+1
85 end,
86 100),
87 'fm09') fiscal_year,
88 dol.requisition_number,
89 case
90 when dol.status_code='IR'
91 then 'K'
92 else 'F'
93 end status_code,
94 c.financial_transaction_number,
95 dol.line_number,
96 decode(dol.line_number,1,1,2) level_num
97 from
98 (
99 (
100 (
101 (
102 (
103 (
104 (
105 (
106 (/*delivery_orders*/ dos
107 inner join dol
108 on dol.delivery_orders_id=dos.delivery_orders_id
109 )
110 )
111 )
112 inner join carts c
113 on c.carts_id=dos.carts_id
114 )
115 inner join payment_information pinfo
116 on pinfo.payment_information_id=c.payment_information_id
117 )
118 inner join contracts con
119 on con.contracts_id=dos.contracts_id
120 )
121 inner join suppliers sup
122 on con.suppliers_id=sup.suppliers_id
123 )
124 )
125 ),
126 system_configuration sc
127 ) v_data
128 inner join queue q
129 on q.queue_id=v_data.id
130 and q.queue_id=:x
131 and q.queue_name='APCReport'
132 ;
6 rows selected.
Elapsed: 00:00:00.15
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=6 Bytes=1362)
1 0 NESTED LOOPS (Cost=38 Card=6 Bytes=1362)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'QUEUE' (Cost=2 Card=1 Bytes=22)
3 2 INDEX (UNIQUE SCAN) OF 'QUEUE_PK' (UNIQUE) (Cost=1 Card=1)
4 1 VIEW (Cost=36 Card=6 Bytes=1230)
5 4 HASH JOIN (OUTER) (Cost=36 Card=6 Bytes=1374)
6 5 NESTED LOOPS (Cost=32 Card=6 Bytes=1218)
7 6 MERGE JOIN (CARTESIAN) (Cost=26 Card=6 Bytes=936)
8 7 HASH JOIN (OUTER) (Cost=24 Card=1 Bytes=137)
9 8 MERGE JOIN (CARTESIAN) (Cost=21 Card=1 Bytes=98)
10 9 NESTED LOOPS (Cost=8 Card=1 Bytes=72)
11 10 NESTED LOOPS (Cost=6 Card=1 Bytes=67)
12 11 NESTED LOOPS (Cost=5 Card=1 Bytes=61)
13 12 NESTED LOOPS (Cost=4 Card=1 Bytes=47)
14 13 NESTED LOOPS (Cost=3 Card=1 Bytes=35)
15 14 TABLE ACCESS (BY INDEX ROWID) OF 'DELIVERY_ORDERS' (Co
st=2 Card=1 Bytes=29)
16 15 INDEX (UNIQUE SCAN) OF 'DELIVERY_ORDERS_PK' (UNIQUE)
(Cost=1 Card=1)
17 14 TABLE ACCESS (BY INDEX ROWID) OF 'CONTRACTS' (Cost=1 C
ard=1 Bytes=6)
18 17 INDEX (UNIQUE SCAN) OF 'CONTRACTS_PK' (UNIQUE)
19 13 TABLE ACCESS (BY INDEX ROWID) OF 'CARTS' (Cost=1 Card=1
Bytes=12)
20 19 INDEX (UNIQUE SCAN) OF 'CARTS_PK' (UNIQUE)
21 12 TABLE ACCESS (BY INDEX ROWID) OF 'SUPPLIERS' (Cost=1 Card=
1 Bytes=14)
22 21 INDEX (UNIQUE SCAN) OF 'XPKSUPPLIERS' (UNIQUE)
23 11 TABLE ACCESS (BY INDEX ROWID) OF 'PAYMENT_INFORMATION' (Cost
=1 Card=1 Bytes=6)
24 23 INDEX (UNIQUE SCAN) OF 'PAYMENT_INFORMATION_PK' (UNIQUE)
25 10 TABLE ACCESS (FULL) OF 'SYSTEM_CONFIGURATION' (Cost=2 Card=1 B
ytes=5)
26 9 BUFFER (SORT) (Cost=19 Card=1 Bytes=26)
27 26 VIEW (Cost=13 Card=1 Bytes=26)
28 27 SORT (GROUP BY) (Cost=13 Card=1 Bytes=59)
29 28 HASH JOIN (OUTER) (Cost=13 Card=6 Bytes=354)
30 29 NESTED LOOPS (Cost=8 Card=6 Bytes=198)
31 30 TABLE ACCESS (BY INDEX ROWID) OF 'DELIVERY_ORDER_LINES
' (Cost=2 Card=6 Bytes=96)
32 31 INDEX (RANGE SCAN) OF 'DELIVERY_ORDER_LINES_IDX' (NO
N-UNIQUE) (Cost=1 Card=6)
33 30 TABLE ACCESS (BY INDEX ROWID) OF 'CART_PRODUCTS' (Cost
=1 Card=1 Bytes=17)
34 33 INDEX (UNIQUE SCAN) OF 'CART_PRODUCTS_PK' (UNIQUE)
35 29 VIEW (Cost=4 Card=1 Bytes=26)
36 35 SORT (GROUP BY) (Cost=4 Card=1 Bytes=26)
37 36 TABLE ACCESS (FULL) OF 'CART_PRODUCT_OPTIONS' (Cost=
2 Card=1 Bytes=26)
38 8 VIEW (Cost=2 Card=1 Bytes=39)
39 38 WINDOW (BUFFER) (Cost=2 Card=1 Bytes=16)
40 39 INDEX (RANGE SCAN) OF 'DELIVERY_ORDER_EVENTS_IDX' (NON-UNIQUE)
(Cost=2 Card=1 Bytes=16)
41 7 BUFFER (SORT) (Cost=26 Card=6 Bytes=114)
42 41 TABLE ACCESS (BY INDEX ROWID) OF 'DELIVERY_ORDER_LINES' (Cost=2 Ca
rd=6 Bytes=114)
43 42 INDEX (RANGE SCAN) OF 'DELIVERY_ORDER_LINES_IDX' (NON-UNIQUE) (C
ost=1 Card=6)
44 6 TABLE ACCESS (BY INDEX ROWID) OF 'CART_PRODUCTS' (Cost=1 Card=1 Bytes=
47)
45 44 INDEX (UNIQUE SCAN) OF 'CART_PRODUCTS_PK' (UNIQUE)
46 5 VIEW (Cost=4 Card=1 Bytes=26)
47 46 SORT (GROUP BY) (Cost=4 Card=1 Bytes=26)
48 47 TABLE ACCESS (FULL) OF 'CART_PRODUCT_OPTIONS' (Cost=2 Card=1 Bytes=2
6)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
66 consistent gets
6 physical reads
0 redo size
1117 bytes sent via SQL*Net to client
2187 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
6 rows processed
ross@PRV3.ECAT.MANTECHWVA.COM> @q222
ross@PRV3.ECAT.MANTECHWVA.COM> set timing on
ross@PRV3.ECAT.MANTECHWVA.COM> set linesize 130
ross@PRV3.ECAT.MANTECHWVA.COM> set autotrace traceonly
ross@PRV3.ECAT.MANTECHWVA.COM>
ross@PRV3.ECAT.MANTECHWVA.COM> declare
2 cursor v_data_cursor(p_id in number) is
3 select * from (
4 with cp as (
5 select cp1.cart_products_id, cp1.quantity, cp1.part_number, cp1.product_name,
6 cp1.requisition_number,
7 round(cp1.crr_percentage * (cp1.unit_price + cpo.option_price), 2) unit_price
8 from cart_products cp1
9 left outer join (
10 select cart_products_id, sum(nvl(option_price,0)) option_price
11 from cart_product_options
12 group by cart_products_id
13 ) cpo
14 on cpo.cart_products_id=cp1.cart_products_id
15 ),
16 dol as (
17 select dol12.delivery_order_lines_id, dol12.delivery_orders_id,
18 cart_products_id, dol12.status_code, dol12.quantity_filled, dol12.line_number,
19 cp.quantity, cp.part_number, cp.product_name, cp.requisition_number,
20 cp.unit_price,
21 nvl(dol12.quantity_filled, cp.quantity)*cp.unit_price ext_price
22 from delivery_order_lines dol12
23 inner join cp using(cart_products_id)
24 ),
25 v_do_total as (
26 select dol22.delivery_orders_id,
27 sum(ext_price) total
28 from (
29 select nvl(dol13.quantity_filled, vw.quantity)*round(crr_percentage*unit_price, 2) ext_price
30 ,dol13.delivery_orders_id
31 from (select cart_products_id, quantity, crr_percentage
32 , cp.unit_price+nvl(cpo.options_price, 0) unit_price
33 from cart_products cp
34 left outer join (
35 select cart_products_id, sum(nvl(option_price, 0)) options_price
36 from cart_product_options
37 group by cart_products_id
38 ) cpo
39 using (cart_products_id)
40 ) vw
41 inner join delivery_order_lines dol13
42 using (cart_products_id)
43 where dol13.status_code in ('AC','AR','VS')
44 ) dol22
45 group by dol22.delivery_orders_id
46 ),
47 dos as(
48 select delivery_orders_id, dos_tab.carts_id, dos_tab.contracts_id,
49 dos_tab.summary_requisition_number, dos_tab.status_code, total, fy.month, fy.year
50 from delivery_orders dos_tab
51 inner join v_do_total
52 using(delivery_orders_id)
53 left outer join(
54 select delivery_orders_id,
55 to_number(to_char(max(timestamp) over(partition by delivery_orders_id), 'mm')) month,
56 to_number(to_char(max(timestamp) over(partition by delivery_orders_id), 'yy')) year
57 from delivery_order_events
58 where events_id=30 --delivery order accepted by supplier
59 ) fy
60 using(delivery_orders_id)
61 )
62 select dos.delivery_orders_id id,
63 dos.status_code do_status_code,
64 dos.summary_requisition_number,
65 dos.total,
66 sup.supplier_name,
67 case
68 when pinfo.payment_methods_id=1 --credit card
69 then 'C'
70 else 'L'
71 end payment_type,
72 sc.wrair_apc,
73 dol.part_number,
74 dol.product_name,
75 dol.quantity_filled,
76 dol.unit_price,
77 case
78 when dol.status_code='IR'
79 then dol.unit_price*dol.quantity
80 else dol.ext_price
81 end ext_price,
82 to_char(
83 mod(case
84 when dos.month<=9
85 then dos.year
86 else dos.year+1
87 end,
88 100),
89 'fm09') fiscal_year,
90 dol.requisition_number,
91 case
92 when dol.status_code='IR'
93 then 'K'
94 else 'F'
95 end status_code,
96 c.financial_transaction_number,
97 dol.line_number,
98 decode(dol.line_number,1,1,2) level_num
99 from
100 (
101 (
102 (
103 (
104 (
105 (
106 (
107 (
108 (/*delivery_orders*/ dos
109 inner join dol
110 on dol.delivery_orders_id=dos.delivery_orders_id
111 )
112 )
113 )
114 inner join carts c
115 on c.carts_id=dos.carts_id
116 )
117 inner join payment_information pinfo
118 on pinfo.payment_information_id=c.payment_information_id
119 )
120 inner join contracts con
121 on con.contracts_id=dos.contracts_id
122 )
123 inner join suppliers sup
124 on con.suppliers_id=sup.suppliers_id
125 )
126 )
127 ),
128 system_configuration sc
129 )
130 where id = p_id;
131
132 begin
133 for q in (select queue_id from queue where queue_name='APCReport') loop
134 for rec in v_data_cursor(q.queue_id) loop
135 dbms_output.put_line(rec.id||' '||rec.total);
136 end loop;
137 end loop;
138 end;
139 /
541 109.84
541 109.84
541 109.84
541 109.84
541 109.84
541 109.84
542 124.24
542 124.24
542 124.24
542 124.24
542 124.24
542 124.24
543 132.83
543 132.83
543 132.83
543 132.83
543 132.83
543 132.83
544 95.41
544 95.41
544 95.41
544 95.41
544 95.41
544 95.41
545 87.93
545 87.93
545 87.93
545 87.93
545 87.93
545 87.93
546 111.24
546 111.24
546 111.24
546 111.24
546 111.24
546 111.24
547 84.8
547 84.8
547 84.8
547 84.8
547 84.8
547 84.8
548 100.77
548 100.77
548 100.77
548 100.77
548 100.77
548 100.77
549 116.13
549 116.13
549 116.13
549 116.13
549 116.13
549 116.13
550 101
550 101
550 101
550 101
550 101
550 101
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.17
reader
A reader, September 17, 2007 - 12:44 pm UTC
I have a view (v1) that has a join of 5 tables (t1, t2, t3, t4, t5). This is proprietory code.
I am writing a query that selects from this view with
a where clause that refers to the columns from the view
The performance is very bad. It is not using a index which would optimize performance
What are the approach and ways to supply hint in the query
to make the query use a particular index. i am looking for a syntax to use hint in the query, for referring to the table (within the view, ex: t1) and index (on the table within the view, ex: t1_ix)
Could you please give an example
September 18, 2007 - 2:34 pm UTC
entirely insufficient data provided to help you. I cannot divulge a proprietary setting that would help you.
(that a query is "proprietary" is, well, sort of funny to me. Anyone that uses your product can see it)
It is highly likely that your VIEW precludes the use of the index - but without actually seeing what you are doing - well - that is all we can say.
Analytic function - not use index
A reader, October 31, 2007 - 8:02 am UTC
Hi Tom,
There is a one example I run on EE 9.2.0.3 and got same results also on 10.2.0.
spool test.log
drop table test_af;
create table test_af as select * from all_objects;
alter table test_af add primary key (object_id);
create index i_test_af_owner on test_af (owner);
analyze table test_af compute statistics for table for all columns for all indexes;
var owner varchar2(25);
exec :owner := 'SYS';
set timing on;
set autotrace traceonly;
rem 1. without analytic function use index
select * from (
select
test_af.*
from test_af
)
where owner = :owner;
rem 2. with analytic function full table scan
select * from (
select
row_number() over (partition by object_type order by created) as rn,
test_af.*
from test_af
)
where owner = :owner;
rem 3.with analytic function different partition by - use index
select * from (
select
row_number() over (partition by owner, object_type order by created) as rn,
test_af.*
from test_af
)
where owner = :owner;
spool off;
I can't get that index is used in 2. except that have different partition as used in 3.
November 01, 2007 - 4:15 pm UTC
if you
a) compute analytic and then
b) apply where clause
that is often different from
a) apply where clause
b) compute analytic
select * from (
select
row_number() over (partition by object_type order by created) as rn,
test_af.*
from test_af
)
where owner = :owner;that query says
a) please assign row_number to each row after breaking up by OBJECT_TYPE and ordering by created.
b) and then return the rows only where owner = :owner
Every single row in TEST_AF must be used to get "A" together. It must assign the row_number PRIOR to filtering by OWNER (else the row_number would be - well - WRONG). Suppose you had data:
OBJECT_TYPE CREATED OWNER
----------- -------- ---------
X 10 A
X 26 B
Now, after step (a) you would have:
OBJECT_TYPE CREATED OWNER ROW_NUMBER
----------- -------- --------- ------------
X 10 A 1
X 26 B 2
And now, if OWNER = 'B' was used, the answer would be:
OBJECT_TYPE CREATED OWNER ROW_NUMBER
----------- -------- --------- ------------
X 26 B 2
If you used the index to just find OWNER='B' rows however and then computed row number - you would get an entirely different answer.
When you added OWNER to the front of the partition - the optimizer was smart enough to recognize that it could of course push the predicate in, find just the rows for that owner, partition them by object_type and then assign the row number after sorting by created.
Think about this one:
select *
from ( select object_type, owner, object_id,
min(object_id) over (partition by object_type),
max(object_id) over (partition by object_type)
from t )
where owner = 'B'that says "find the min/max object_id by object_type (over ALL rows for that object type) and assign that to each row.
and then, just return owner = 'B'.
You cannot just look at "B" in the inner query, you have to look at EVERY row.
ops$tkyte%ORA10GR2> select *
2 from ( select object_type, owner, object_id,
3 min(object_id) over (partition by object_type) minid,
4 max(object_id) over (partition by object_type) maxid
5 from all_objects )
6 where owner = 'SCOTT'
7 order by object_type, object_id
8 /
OBJECT_TYPE OWNER OBJECT_ID MINID MAXID
------------------- ----- ---------- ---------- ----------
FUNCTION SCOTT 144886 4229 147300
PACKAGE SCOTT 63291 887 146066
PACKAGE BODY SCOTT 63292 888 146068
PROCEDURE SCOTT 63285 3992 147301
PROCEDURE SCOTT 63301 3992 147301
TABLE SCOTT 57960 4 147634
TABLE SCOTT 57961 4 147634
TABLE SCOTT 57962 4 147634
TABLE SCOTT 57963 4 147634
TABLE SCOTT 57964 4 147634
TABLE SCOTT 63306 4 147634
TABLE SCOTT 127838 4 147634
TABLE SCOTT 142288 4 147634
VIEW SCOTT 63293 900 147106
VIEW SCOTT 132189 900 147106
15 rows selected.
ops$tkyte%ORA10GR2> select object_type, owner, object_id,
2 min(object_id) over (partition by object_type) minid,
3 max(object_id) over (partition by object_type) maxid
4 from all_objects
5 where owner = 'SCOTT'
6 order by object_type, object_id
7 /
OBJECT_TYPE OWNER OBJECT_ID MINID MAXID
------------------- ----- ---------- ---------- ----------
FUNCTION SCOTT 144886 144886 144886
PACKAGE SCOTT 63291 63291 63291
PACKAGE BODY SCOTT 63292 63292 63292
PROCEDURE SCOTT 63285 63285 63301
PROCEDURE SCOTT 63301 63285 63301
TABLE SCOTT 57960 57960 142288
TABLE SCOTT 57961 57960 142288
TABLE SCOTT 57962 57960 142288
TABLE SCOTT 57963 57960 142288
TABLE SCOTT 57964 57960 142288
TABLE SCOTT 63306 57960 142288
TABLE SCOTT 127838 57960 142288
TABLE SCOTT 142288 57960 142288
VIEW SCOTT 63293 63293 132189
VIEW SCOTT 132189 63293 132189
15 rows selected.
your index is not useful for that one query, it would return the wrong result.
Analytic function - not use index
Sasa, October 31, 2007 - 8:08 am UTC
Hi Tom,
There is a one example I run on EE 9.2.0.3 and got same results also on 10.2.0.
spool test.log
drop table test_af;
create table test_af as select * from all_objects;
alter table test_af add primary key (object_id);
create index i_test_af_owner on test_af (owner);
analyze table test_af compute statistics for table for all columns for all indexes;
var owner varchar2(25);
exec :owner := 'SYS';
set timing on;
set autotrace traceonly;
rem 1. without analytic function use index
select * from (
select
test_af.*
from test_af
)
where owner = :owner;
rem 2. with analytic function full table scan
select * from (
select
row_number() over (partition by object_type order by created) as rn,
test_af.*
from test_af
)
where owner = :owner;
rem 3.with analytic function different partition by - use index
select * from (
select
row_number() over (partition by owner, object_type order by created) as rn,
test_af.*
from test_af
)
where owner = :owner;
spool off;
I can't get that index is used in 2. except that have different partition as used in 3.
Any explanation for this?
Thanks
More on Pushing Predicates
Paul, February 27, 2008 - 3:00 pm UTC
Tom,
I¿d like to return if possible to the original subject, which was on pushing predicates to ask a question on the underlying issue.
I can understand your explanation for why it will not work with a rownum or other analytic function, as it affects the result set.
But I recently encountered a problem with a very poorly performing view. I believe it is related to the same issue, somehow, but I do not see why the predicate can not be pushed. To me, the pushing of the predicate would not affect the result set. The plans look the same, but the trace shows that something very different is actually going on.
I think it's related to the filter step and has to do with the INTERNAL_FUNCTION, somehow. Why is this? Is there some way around it?
Thanks, as always,
Paul
Given this original SQL:
select trunc( date_col, 'mon'),
count(*)
from t1
where date_col between to_date( '2008-01-01', 'YYYY-MM-DD' )
and to_date( '2008-02-01', 'YYYY-MM-DD' )
and string_col in ( '1', '2', '3' )
group by trunc( date_col, 'mon' )
order by 1;
Create it as a view:
create or replace view v1
as
select trunc( date_col, 'mon'),
count(*)
from t1
group by trunc( date_col, 'mon' )
order by 1;
When I run the original SQL, it uses the index and returns in seconds:
1 rows selected.
Elapsed: 00:00:20.35
Execution Plan
-----------------------------------------------
| Id | Operation |
-----------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | SORT ORDER BY |
| 2 | HASH GROUP BY |
| 3 | INLIST ITERATOR |
|* 4 | TABLE ACCESS BY GLOBAL INDEX ROWID|
|* 5 | INDEX RANGE SCAN |
-----------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("DATE_COL">=TO_DATE('2008-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"DATE_COL"<=TO_DATE('2008-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
5 - access("STRING_COL"='1' OR "STRING_COL"='2' OR "STRING_COL"='3')
Statistics
----------------------------------------------------------
1248 recursive calls
0 db block gets
34070 consistent gets
9 physical reads
116 redo size
816 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
26 sorts (memory)
0 sorts (disk)
1 rows processed
BUT, When I run the view:
select * from v1
where date_col between to_date( d1, 'yyyymmdd' )
and to_date( d1, 'yyyymmdd' ) ;
I get:
1 rows selected.
Elapsed: 00:26:00.14
Execution Plan
------------------------------------------------
| Id | Operation |
------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | VIEW |
| 2 | SORT ORDER BY |
| 3 | HASH GROUP BY |
| 4 | INLIST ITERATOR |
|* 5 | TABLE ACCESS BY GLOBAL INDEX ROWID|
|* 6 | INDEX RANGE SCAN |
------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(TRUNC(INTERNAL_FUNCTION("DATE_COL",'fmmon')>=TO_DATE('2008-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
TRUNC(INTERNAL_FUNCTION("DATE_COL",'fmmon')<=TO_DATE('2008-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
6 - access("STRING_COL"='1' OR "STRING_COL"='2' OR "STRING_COL"='3')
Statistics
----------------------------------------------------------
178 recursive calls
0 db block gets
369416 consistent gets
160721 physical reads
1128 redo size
831 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
1 rows processed
It still seems to be using the index, but MUCH slower...
Why prdicate is not pushed in 9i?
Chinni, February 26, 2009 - 2:53 am UTC
Hi Tom,
Following query is using predicate pushing in 10g environment but not in 9i. Tables data/structure are similar and stats are up to date(gathered similarly)
div is a view
SELECT *
FROM dcsw_user.div a, dcsw_user.sec b
WHERE b.sec_id = a.div_sec_wpk
AND a.div_sit_id = 'TE'
AND b.sec_sit_id = a.div_sit_id
AND a.div_ex_date = TO_DATE (' 01/25/2008', 'MM/DD/YYYY')
AND b.sec_isin = 'DE0007236101'
AND ( a.div_taxa_ind NOT IN ('1', '4', '8', '9', 'Z', '7')
OR ( a.div_taxa_ind = '7'
AND (a.div_fund_taxable_tax > 0 OR a.div_fund_taxable_hev > 0)
)
);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 637 | 8789 |
|* 1 | HASH JOIN | | 1 | 637 | 8789 |
| 2 | TABLE ACCESS BY INDEX ROWID | SEC | 4 | 1908 | 3 |
|* 3 | INDEX RANGE SCAN | SEC_NU01 | 4 | | 3 |
| 4 | VIEW | DIV | 6 | 960 | 8785 |
| 5 | UNION-ALL | | | | |
|* 6 | TABLE ACCESS BY INDEX ROWID | DVM | 3 | 456 | 8248 |
|* 7 | INDEX RANGE SCAN | DVM_NU02 | 6270 | | 13031 |
|* 8 | FILTER | | | | |
|* 9 | TABLE ACCESS BY INDEX ROWID | DVM | 1 | 150 | 444 |
|* 10 | INDEX RANGE SCAN | DVM_NU02 | 624 | | 543 |
|* 11 | FILTER | | | | |
| 12 | INLIST ITERATOR | | | | |
|* 13 | TABLE ACCESS BY INDEX ROWID| DVM | 1 | 150 | 68 |
|* 14 | INDEX RANGE SCAN | DVM_NU02 | 93 | | 84 |
|* 15 | FILTER | | | | |
|* 16 | TABLE ACCESS BY INDEX ROWID | DVM | 1 | 135 | 25 |
|* 17 | INDEX RANGE SCAN | DVM_NU02 | 32 | | 31 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."SEC_ID"="A"."DIV_SEC_WPK")
3 - access("B"."SEC_SIT_ID"='TE' AND "B"."SEC_ISIN"='DE0007236101')
6 - filter("DVM"."DVM_DIVIDEND_STATUS"<>'1C' AND
"DVM"."DVM_DIVIDEND_STATUS"<>'2C' AND "DVM"."DVM_DIVIDEND_STATUS"<>'7C' AND
"DVM"."DVM_DIVIDEND_STATUS"<>'8C' AND "DVM"."DVM_DIVIDEND_STATUS"<>'9C' AND
"DVM"."DVM_DIVIDEND_STATUS"<>'NC' AND "DVM"."DVM_DIVIDEND_STATUS"<>'PC' AND
("DVM"."DVM_TAXABILITY_IND"<>'1' AND "DVM"."DVM_TAXABILITY_IND"<>'4' AND
"DVM"."DVM_TAXABILITY_IND"<>'8' AND "DVM"."DVM_TAXABILITY_IND"<>'9' AND
"DVM"."DVM_TAXABILITY_IND"<>'Z' AND "DVM"."DVM_TAXABILITY_IND"<>'7' OR
"DVM"."DVM_TAXABILITY_IND"='7' AND ("DVM"."DVM_KOEST_AMT">0 OR
"DVM"."DVM_ED073_AMT">0)))
7 - access("DVM"."DVM_SIT_ID"='TE' AND "DVM"."DVM_ACTIVE"='1')
filter("DVM"."DVM_ACTIVE"='1' AND TRUNC("DVM"."DVM_EX_DATE")=TO_DATE('
2008-01-25 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
8 - filter('TD'='TE')
9 - filter(("DVM"."DVM_DIVIDEND_STATUS"='P' OR "DVM"."DVM_DIVIDEND_STATUS"
IS NULL AND "DVM"."DVM_DIVIDEND_TYPE"='I') AND ("DVM"."DVM_TEXT"='01' OR
"DVM"."DVM_TEXT"='09' OR "DVM"."DVM_TEXT"='15' OR "DVM"."DVM_TEXT"='50') AND
("DVM"."DVM_TAXABILITY_IND"<>'1' AND "DVM"."DVM_TAXABILITY_IND"<>'4' AND
"DVM"."DVM_TAXABILITY_IND"<>'8' AND "DVM"."DVM_TAXABILITY_IND"<>'9' AND
"DVM"."DVM_TAXABILITY_IND"<>'Z' AND "DVM"."DVM_TAXABILITY_IND"<>'7' OR
"DVM"."DVM_TAXABILITY_IND"='7' AND ("DVM"."DVM_KOEST_AMT">0 OR
"DVM"."DVM_ED073_AMT">0)))
10 - access("DVM"."DVM_SIT_ID"='TD')
filter(TRUNC("DVM"."DVM_EX_DATE")=TO_DATE(' 2008-01-25 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
11 - filter('EA'='TE')
13 - filter("DVM"."DVM_DIVIDEND_STATUS"='P' AND
("DVM"."DVM_TAXABILITY_IND"<>'1' AND "DVM"."DVM_TAXABILITY_IND"<>'4' AND
"DVM"."DVM_TAXABILITY_IND"<>'8' AND "DVM"."DVM_TAXABILITY_IND"<>'9' AND
"DVM"."DVM_TAXABILITY_IND"<>'Z' AND "DVM"."DVM_TAXABILITY_IND"<>'7' OR
"DVM"."DVM_TAXABILITY_IND"='7' AND ("DVM"."DVM_KOEST_AMT">0 OR
"DVM"."DVM_ED073_AMT">0)))
14 - access("DVM"."DVM_SIT_ID"='EA' AND ("DVM"."DVM_DIVIDEND_TYPE"='D' OR
"DVM"."DVM_DIVIDEND_TYPE"='I'))
filter(TRUNC("DVM"."DVM_EX_DATE")=TO_DATE(' 2008-01-25 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
15 - filter('EA'='TE')
16 - filter("DVM"."DVM_DIVIDEND_STATUS"='P' AND "DVM"."DVM_RED_BONUS_PCT" IS
NOT NULL AND "DVM"."DVM_RED_BONUS_PCT">0 AND ("DVM"."DVM_TAXABILITY_IND"<>'1'
AND "DVM"."DVM_TAXABILITY_IND"<>'4' AND "DVM"."DVM_TAXABILITY_IND"<>'8' AND
"DVM"."DVM_TAXABILITY_IND"<>'9' AND "DVM"."DVM_TAXABILITY_IND"<>'Z' AND
"DVM"."DVM_TAXABILITY_IND"<>'7' OR "DVM"."DVM_TAXABILITY_IND"='7' AND
("DVM"."DVM_KOEST_AMT">0 OR "DVM"."DVM_ED073_AMT">0)))
17 - access("DVM"."DVM_SIT_ID"='EA' AND "DVM"."DVM_DIVIDEND_TYPE"='P')
filter(TRUNC("DVM"."DVM_EX_DATE")=TO_DATE(' 2008-01-25 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
Note: cpu costing is off
IN 10g
================
PLAN_TABLE_OUTPUT
Plan hash value: 1406705375
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 668 | 7 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 668 | 7 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | SEC | 1 | 486 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SEC_NU01 | 1 | | 2 (0)| 00:00:01 |
| 4 | VIEW | DIV | 1 | 182 | 5 (0)| 00:00:01 |
| 5 | UNION ALL PUSHED PREDICATE | | | | | |
|* 6 | FILTER | | | | | |
|* 7 | TABLE ACCESS BY INDEX ROWID| DVM | 1 | 97 | 5 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | DVM_PK | 6 | | 2 (0)| 00:00:01 |
|* 9 | FILTER | | | | | |
|* 10 | TABLE ACCESS BY INDEX ROWID| DVM | 1 | 95 | 2 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | DVM_PK | 1 | | 2 (0)| 00:00:01 |
|* 12 | FILTER | | | | | |
|* 13 | TABLE ACCESS BY INDEX ROWID| DVM | 1 | 95 | 2 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | DVM_PK | 1 | | 2 (0)| 00:00:01 |
|* 15 | FILTER | | | | | |
|* 16 | TABLE ACCESS BY INDEX ROWID| DVM | 1 | 98 | 2 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | DVM_PK | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"."SEC_SIT_ID"='TE' AND "B"."SEC_ISIN"='DE0007236101')
6 - filter(('TE'="B"."SEC_SIT_ID" OR 'TO'="B"."SEC_SIT_ID") AND
'TE'="B"."SEC_SIT_ID")
7 - filter(("DVM_TAXABILITY_IND"<>'1' AND "DVM_TAXABILITY_IND"<>'9' AND
"DVM_TAXABILITY_IND"<>'7' AND "DVM_TAXABILITY_IND"<>'8' AND
"DVM_TAXABILITY_IND"<>'Z' AND "DVM_TAXABILITY_IND"<>'4' OR "DVM_TAXABILITY_IND"='7'
AND ("DVM_KOEST_AMT">0 OR "DVM_ED073_AMT">0)) AND "DVM_ACTIVE"='1' AND
"DVM_DIVIDEND_STATUS"<>'1C' AND TRUNC(INTERNAL_FUNCTION("DVM_EX_DATE"))=TO_DATE('
2008-01-25 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "DVM_DIVIDEND_STATUS"<>'2C' AND
"DVM_DIVIDEND_STATUS"<>'9C' AND "DVM_DIVIDEND_STATUS"<>'8C' AND
"DVM_DIVIDEND_STATUS"<>'PC' AND "DVM_DIVIDEND_STATUS"<>'7C' AND
"DVM_DIVIDEND_STATUS"<>'NC')
8 - access("DVM_SIT_ID"='TE' AND "DVM_SEC_ID"="B"."SEC_ID")
9 - filter(NULL IS NOT NULL AND 'TD'="B"."SEC_SIT_ID")
10 - filter(("DVM_DIVIDEND_STATUS"='P' OR "DVM_DIVIDEND_STATUS" IS NULL AND
"DVM_DIVIDEND_TYPE"='I') AND ("DVM_TAXABILITY_IND"<>'1' AND
"DVM_TAXABILITY_IND"<>'9' AND "DVM_TAXABILITY_IND"<>'7' AND
"DVM_TAXABILITY_IND"<>'8' AND "DVM_TAXABILITY_IND"<>'Z' AND
"DVM_TAXABILITY_IND"<>'4' OR "DVM_TAXABILITY_IND"='7' AND ("DVM_KOEST_AMT">0 OR
"DVM_ED073_AMT">0)) AND TRUNC(INTERNAL_FUNCTION("DVM_EX_DATE"))=TO_DATE('
2008-01-25 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND ("DVM_TEXT"='01' OR
"DVM_TEXT"='09' OR "DVM_TEXT"='15' OR "DVM_TEXT"='50'))
11 - access("DVM_SIT_ID"='TD' AND "DVM_SEC_ID"="B"."SEC_ID")
12 - filter(NULL IS NOT NULL AND 'EA'="B"."SEC_SIT_ID")
13 - filter("DVM_DIVIDEND_STATUS"='P' AND ("DVM_DIVIDEND_TYPE"='D' OR
"DVM_DIVIDEND_TYPE"='I') AND ("DVM_TAXABILITY_IND"<>'1' AND
"DVM_TAXABILITY_IND"<>'9' AND "DVM_TAXABILITY_IND"<>'7' AND
"DVM_TAXABILITY_IND"<>'8' AND "DVM_TAXABILITY_IND"<>'Z' AND
"DVM_TAXABILITY_IND"<>'4' OR "DVM_TAXABILITY_IND"='7' AND ("DVM_KOEST_AMT">0 OR
"DVM_ED073_AMT">0)) AND TRUNC(INTERNAL_FUNCTION("DVM_EX_DATE"))=TO_DATE('
2008-01-25 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
14 - access("DVM_SIT_ID"='EA' AND "DVM_SEC_ID"="B"."SEC_ID")
15 - filter(NULL IS NOT NULL AND 'EA'="B"."SEC_SIT_ID")
16 - filter("DVM_RED_BONUS_PCT" IS NOT NULL AND "DVM_RED_BONUS_PCT">0 AND
"DVM_DIVIDEND_TYPE"='P' AND "DVM_DIVIDEND_STATUS"='P' AND
("DVM_TAXABILITY_IND"<>'1' AND "DVM_TAXABILITY_IND"<>'9' AND
"DVM_TAXABILITY_IND"<>'7' AND "DVM_TAXABILITY_IND"<>'8' AND
"DVM_TAXABILITY_IND"<>'Z' AND "DVM_TAXABILITY_IND"<>'4' OR "DVM_TAXABILITY_IND"='7'
AND ("DVM_KOEST_AMT">0 OR "DVM_ED073_AMT">0)) AND
TRUNC(INTERNAL_FUNCTION("DVM_EX_DATE"))=TO_DATE(' 2008-01-25 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
17 - access("DVM_SIT_ID"='EA' AND "DVM_SEC_ID"="B"."SEC_ID")
Please suggest, anything to do with init parameters? stats?
Thanks for your time
March 03, 2009 - 7:14 am UTC
well, first and foremost - 9i is not 10g, I would not expect the same plans -- many times will they be the same? Sure. All of the time? Of course not, 10g is light years ahead of 9i. Things change.
And here, you have a query against a view with more tables and tables that have statistics and structure that I know nothing about - in other words:
entirely insufficient data to even look at. You don't even go as far as to say "what bit" you think I should look at? Sorry, there isn't even anything to think about starting with here.
analytic function partition in view and filter outside on the same can profit from push predicate
Berny, June 15, 2009 - 10:14 am UTC
Maybe the following approach can help other users too. To demonstrate the mechanism I created a test case with the well known EMP table.
SQL> set autotrace on
Additional index on emp (the only index):
SQL> drop index uq_deptno_empno;
Index dropped.
SQL> create unique index uq_deptno_empno on emp (deptno, empno);
Index created.
SQL>
The following SQL is what I would like, however I'd like to put everything except the where-clause within a view,
while still ending up with the same result. As you can see, this SQL takes advange of the index I created:
SQL> select row_number() over (order by empno) rank, e.* from emp e where deptno=10;
RANK EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
1 7782 CLARK MANAGER 7839 09.06.1981 00:00:00 2450 10
2 7839 KING PRESIDENT 17.11.1981 00:00:00 5000 10
3 7934 MILLER CLERK 7782 23.01.1982 00:00:00 1300 10
Execution Plan
----------------------------------------------------------
Plan hash value: 1708463864
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 261 | 3 (34)| 00:00:01 |
| 1 | WINDOW NOSORT | | 3 | 261 | 3 (34)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 261 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | UQ_DEPTNO_EMPNO | 3 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"=10)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
52 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
572 bytes sent via SQL*Net to client
245 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
When I put everything except the where-clause within a view however, I end up with an SQL equivalent to the following.
As you can see while the first SQL was taking advantage of the index, that is no longer possible with this one.
The ranking, which now has to be applied BEFORE, avoids use of the index. The result is no longer logically the same either.
As Tom put it above: "a where on a view, is not the same as a VIEW with a WHERE!!!"
SQL> select * from
2 (select row_number() over (order by empno) rank, e.* from emp e)
3 where deptno=10;
RANK EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7 7782 CLARK MANAGER 7839 09.06.1981 00:00:00 2450 10
9 7839 KING PRESIDENT 17.11.1981 00:00:00 5000 10
14 7934 MILLER CLERK 7782 23.01.1982 00:00:00 1300 10
Execution Plan
----------------------------------------------------------
Plan hash value: 3116413243
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1400 | 4 (25)| 00:00:01 |
|* 1 | VIEW | | 14 | 1400 | 4 (25)| 00:00:01 |
| 2 | WINDOW SORT | | 14 | 1218 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO"=10)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
573 bytes sent via SQL*Net to client
245 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
However when I partition my ranking within the view (or subselect here) by the same column as I
filter outside, I end up with logically the same result. Not that now the index gets used again as well.
SQL> select * from
2 (select row_number() over (partition by deptno order by empno) rank, e.* from emp e)
3 where deptno=10;
RANK EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
1 7782 CLARK MANAGER 7839 09.06.1981 00:00:00 2450 10
2 7839 KING PRESIDENT 17.11.1981 00:00:00 5000 10
3 7934 MILLER CLERK 7782 23.01.1982 00:00:00 1300 10
Execution Plan
----------------------------------------------------------
Plan hash value: 1112093709
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 300 | 2 (0)| 00:00:01 |
| 1 | VIEW | | 3 | 300 | 2 (0)| 00:00:01 |
| 2 | WINDOW NOSORT | | 3 | 300 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 300 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | UQ_DEPTNO_EMPNO | 3 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"=10)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
573 bytes sent via SQL*Net to client
245 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
Now put that subselct in a view:
SQL> create or replace view v_emp_rank as
2 (select row_number() over (partition by deptno order by empno) rank, e.* from emp e);
View created.
SQL>
And select using the view with same predicate. The index gets still used, since the optimized realized that
the result won't get changed when pushing the predicate within the view, due to filtering on the analytic function
partition:
SQL> select * from v_emp_rank
2 where deptno=10;
RANK EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
1 7782 CLARK MANAGER 7839 09.06.1981 00:00:00 2450 10
2 7839 KING PRESIDENT 17.11.1981 00:00:00 5000 10
3 7934 MILLER CLERK 7782 23.01.1982 00:00:00 1300 10
Execution Plan
----------------------------------------------------------
Plan hash value: 4290044137
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 300 | 2 (0)| 00:00:01 |
| 1 | VIEW | V_EMP_RANK | 3 | 300 | 2 (0)| 00:00:01 |
| 2 | WINDOW NOSORT | | 3 | 300 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 300 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | UQ_DEPTNO_EMPNO | 3 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"=10)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
16 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
572 bytes sent via SQL*Net to client
245 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
why row_number predicate is not pushed in ?
Andriy, July 08, 2009 - 5:20 pm UTC
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
drop table t
2 /
Table dropped.
SQL>
SQL> create table t as select rpad('1',50,'a') fielda , rpad('1',500,'a') fieldb from dual connect by level < 10000
2 /
Table created.
SQL> insert into t select rpad('2',50,'a') fielda , rpad('2',500,'a') fieldb from dual connect by level < 10000
2 /
9999 rows created.
SQL> insert into t select rpad('3',50,'a') fielda , rpad('3',500,'a') fieldb from dual connect by level < 10000
2 /
9999 rows created.
SQL> insert into t select rpad('4',50,'a') fielda , rpad('4',500,'a') fieldb from dual connect by level < 10000
2 /
9999 rows created.
SQL>
SQL> create index t$i on t(fielda)
2 /
Index created.
SQL>
SQL> set linesize 1000
SQL> set timing on
SQL> set autot on
SQL>
SQL> rem in this sql oracle know after fielda = rpad('3',50,'a') that
SQL> rem the result query contains only partition by fielda
SQL> rem so it can stop row_number() after first value
SQL> rem but looks like it count whole window before
SQL> rem use predicate rm = 1
SQL> select * from (
2 select fielda,fieldb, row_number() over (partition by fielda order by fielda) rn from t
3 ) where rn =1 and fielda = rpad('3',50,'a')
4 /
FIELDA FIELDB
-------------------------------------------------- ------------------------------------------------------------------
3aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 3aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
Elapsed: 00:00:00.09
Execution Plan
----------------------------------------------------------
Plan hash value: 2678145655
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8459 | 9227K| 3327 (1)| 00:00:20 |
|* 1 | VIEW | | 8459 | 9227K| 3327 (1)| 00:00:20 |
|* 2 | WINDOW NOSORT | | 8459 | 9962K| 3327 (1)| 00:00:20 |
| 3 | TABLE ACCESS BY INDEX ROWID| T | 8459 | 9962K| 768 (1)| 00:00:05 |
|* 4 | INDEX RANGE SCAN | T$I | 8459 | | 78 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"=1)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "FIELDA" ORDER BY "FIELDA")<=1)
4 - access("FIELDA"='3aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
32 recursive calls
0 db block gets
1029 consistent gets
89 physical reads
0 redo size
988 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> rem but somehow adding rownum wich dont have sence becouse originakl result has single row answer
SQL> rem produce the much faster plan.
SQL>
SQL> select * from (
2 select fielda,fieldb, row_number() over (partition by fielda order by fielda) rn from t
3 ) where rn =1 and fielda = rpad('3',50,'a') and rownum = 1
4 /
FIELDA FIELDB
-------------------------------------------------- ------------------------------------------------------------------
3aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 3aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 291325239
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1117 | 3113 (1)| 00:00:19 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | VIEW | | 8459 | 9227K| 3113 (1)| 00:00:19 |
|* 3 | WINDOW NOSORT | | 8459 | 9119K| 3113 (1)| 00:00:19 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 8459 | 9119K| 768 (1)| 00:00:05 |
|* 5 | INDEX RANGE SCAN | T$I | 8459 | | 78 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
2 - filter("RN"=1)
3 - filter(ROW_NUMBER() OVER ( PARTITION BY "FIELDA" ORDER BY "FIELDA")<=1)
5 - access("FIELDA"='3aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
204 consistent gets
0 physical reads
0 redo size
988 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> rem can you describe why and does it realy row_number() cant be used same as rownum
SQL> rem for optimization and row_number predicate cant be pushed in
July 08, 2009 - 7:31 pm UTC
row_number() over (partition by fielda order by fielda)
that is a strange case, it is a completely non-deterministic query - run against the same data - it could/would easily return different answers from run to run - even in the same database.
It doesn't make sense to order by fielda (not unique) and assign row_number().
I'm not worried about this particular case, since it returns basically "random data", not worth any effort to look at.
order by rowid (now deterministic, now the queries are the same), and you'd see something different.
why row_number predicate not pushed in
Andriy, July 08, 2009 - 9:06 pm UTC
Thank for your quick response.
I have recreated test with unique index for repitable sorting as result. But the test result still same.
in row_number()=1 predicate is not pushed in. And adding rownum which dont change query some how results in the query speed up .
drop table t
2 /
Table dropped.
SQL> create table t as select rpad('1',50,'a') fielda , rpad('1',500,'a') fieldb , 1 pk from dual connect
2 by level < 10000
3 /
Table created.
SQL>
SQL> insert into t select rpad('2',50,'a') fielda , rpad('2',500,'a') fieldb , 1 pk from dual connect by
2 level < 10000
3 /
9999 rows created.
SQL>
SQL> insert into t select rpad('3',50,'a') fielda , rpad('3',500,'a') fieldb , 1 pk from dual connect by
2 level < 10000
3 /
9999 rows created.
SQL>
SQL> insert into t select rpad('4',50,'a') fielda , rpad('4',500,'a') fieldb , 1 pk from dual connect by
2 level < 10000
3 /
9999 rows created.
SQL>
SQL> update t set pk = rownum
2 /
39996 rows updated.
SQL> commit
2 /
Commit complete.
SQL> alter table t modify pk not null
2 /
Table altered.
SQL> alter table t modify fielda not null
2 /
Table altered.
SQL> create unique index t$i on t(fielda,pk)
2 /
Index created.
SQL> set trimspool on
SQL> set linesize 1000
SQL> rem in this sql oracle know after fielda = rpad('3',50,'a') that
SQL> rem the result query contains only partition by fielda
SQL> rem so it can stop row_number() after first value
SQL> rem but looks like it count whole window before
SQL> rem use predicate rm = 1
SQL> set autot on
SQL> select * from (
2 select fielda,fieldb, row_number() over (partition by fielda order by fielda , pk ) rn
from t
3 ) where rn =1 and fielda = rpad('3',50,'a')
4 /
FIELDA FIELDB RN PK
--------------------------------------------------
3aaaaaaaaaaa ... 3aaaaaaaaaaaaaaaa 1 19567
Execution Plan
----------------------------------------------------------
Plan hash value: 2678145655
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11061 | 11M| 4404 (1)| 00:00:26 |
|* 1 | VIEW | | 11061 | 11M| 4404 (1)| 00:00:26 |
|* 2 | WINDOW NOSORT | | 11061 | 12M| 4404 (1)| 00:00:26 |
| 3 | TABLE ACCESS BY INDEX ROWID| T | 11061 | 12M| 1025 (1)| 00:00:07 |
|* 4 | INDEX RANGE SCAN | T$I | 11061 | | 109 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"=1)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "FIELDA" ORDER BY
"FIELDA","PK")<=1)
4 - access("FIELDA"='3aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
932 consistent gets
0 physical reads
0 redo size
988 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> rem but somehow adding rownum wich dont have sence becouse original result has single row answer
SQL> rem produce the much faster plan.
SQL> select * from (
2 select fielda,fieldb, row_number() over (partition by fielda order by fielda , pk) rn from t
3 ) where rn =1 and fielda = rpad('3',50,'a') and rownum = 1
4 /
FIELDA FIELDB RN PK
--------------------------------------------------
3aaaaaaaaaaa ... 3aaaaaaaaaaaaaaaa 1 19567
Execution Plan
----------------------------------------------------------
Plan hash value: 291325239
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1117 | 4124 (1)| 00:00:25 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | VIEW | | 11061 | 11M| 4124 (1)| 00:00:25 |
|* 3 | WINDOW NOSORT | | 11061 | 11M| 4124 (1)| 00:00:25 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 11061 | 11M| 1025 (1)| 00:00:07 |
|* 5 | INDEX RANGE SCAN | T$I | 11061 | | 109 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
2 - filter("RN"=1)
3 - filter(ROW_NUMBER() OVER ( PARTITION BY "FIELDA" ORDER BY
"FIELDA","PK")<=1)
5 - access("FIELDA"='3aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
988 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> rem can you describe why and does it realy row_number() cant be used same as rownum
SQL> rem for optimization and row_number predicate cant be pushed in
SQL> set autot off
SQL>
SQL>
July 09, 2009 - 12:04 am UTC
what non-default init.ora's do you have set, I don't think this would use an index typically and I get pushed ranks in both
ops$tkyte%ORA10GR2> column fielda format a10 truncate
ops$tkyte%ORA10GR2> column fieldb format a10 truncate
ops$tkyte%ORA10GR2> set linesize 1000
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set autotrace on
ops$tkyte%ORA10GR2> select * from (
2 select fielda,fieldb, row_number() over (partition by fielda order by fielda , pk ) rn
3 from t
4 ) where rn =1 and fielda = rpad('3',50,'a')
5 /
FIELDA FIELDB RN
---------- ---------- ----------
3aaaaaaaaa 3aaaaaaaaa 1
Execution Plan
----------------------------------------------------------
Plan hash value: 3047187157
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11712 | 3339K| | 1494 (1)| 00:00:18 |
|* 1 | VIEW | | 11712 | 3339K| | 1494 (1)| 00:00:18 |
|* 2 | WINDOW SORT PUSHED RANK| | 11712 | 3339K| 7224K| 1494 (1)| 00:00:18 |
|* 3 | TABLE ACCESS FULL | T | 11712 | 3339K| | 754 (1)| 00:00:10 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"=1)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "FIELDA" ORDER BY
"FIELDA","PK")<=1)
3 - filter("FIELDA"='3aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3372 consistent gets
0 physical reads
0 redo size
1072 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
ops$tkyte%ORA10GR2> select * from (
2 select fielda,fieldb, row_number() over (partition by fielda order by fielda , pk) rn from t
3 ) where rn =1 and fielda = rpad('3',50,'a') and rownum = 1
4 /
FIELDA FIELDB RN
---------- ---------- ----------
3aaaaaaaaa 3aaaaaaaaa 1
Execution Plan
----------------------------------------------------------
Plan hash value: 124228238
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 292 | | 1494 (1)| 00:00:18 |
|* 1 | COUNT STOPKEY | | | | | | |
|* 2 | VIEW | | 11712 | 3339K| | 1494 (1)| 00:00:18 |
|* 3 | WINDOW SORT PUSHED RANK| | 11712 | 3339K| 7224K| 1494 (1)| 00:00:18 |
|* 4 | TABLE ACCESS FULL | T | 11712 | 3339K| | 754 (1)| 00:00:10 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
2 - filter("RN"=1)
3 - filter(ROW_NUMBER() OVER ( PARTITION BY "FIELDA" ORDER BY
"FIELDA","PK")<=1)
4 - filter("FIELDA"='3aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3372 consistent gets
0 physical reads
0 redo size
1072 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
ops$tkyte%ORA10GR2> set autotrace off
why row_number predicate not pushed in ...
Andriy, July 09, 2009 - 7:11 am UTC
after filtering fielda we get 25% of the table and we ask oracle to sort the result in the same order as index exists for numbering lines.
after this oracle can see that we need only single line in result. So index contains all fields and correct ordering.
Looks like index using is good decision.
Any way I can add some more inserts to create the table more large to create situation when the index will be the only choice. currently after filtering by fielda it is 25% of the table we can chage to 1% of the table.
What oracle surprise me in is the "932 consistent gets".
We are filtering table by "fileda" and in the query we ask to do numbering "partition by fielda" so oracle know that after numbering in the result set will be unique numbering. and really because of using presorted index it can stop after first line which get the row_number=1 .
but "932 consistent gets" say us that whole result set were processed before filtering by row_number.
and second example is more surpised. Result set of the first query contains only single line so adding rownum supposed not change any. But is realy speed up the plan we have only " 4 consistent gets"
so whats going when we add rownum. and why oracle dont stop numbering when we have predicate row_number() = 1 ?
so do we can use row_numbering() = 1 to replace the query like next
select * from (select * , rownum from order by ..) where rownum = 1
My init.ora is next
commit_write='BATCH','NOWAIT'
filesystemio_options=setall
o7_dictionary_accessibility=false
recyclebin=OFF
_kgl_large_heap_warning_threshold=104857600
_trace_files_public=false
_cursor_plan_unparse_enabled=false
db_file_multiblock_read_count=16 (we have RAID with those strip size)
job_queue_processes=10
open_cursors=4000
compatible=10.2.0.4.0
processes=1024
sessions=1024
dispatchers="(PROTOCOL=TCP) (SERVICE=_ORACLE_SID_XDB)"
undo_management=AUTO
undo_tablespace=UNDOTBS
PS why row_number predicate not pushed in ...
A reader, July 09, 2009 - 1:35 pm UTC
please remove from test queries the "fieldb" it left in the queries by eccident.
the correct queries is
select * from (
select fielda, row_number() over (partition by fielda order by fielda , pk ) rn
from t
) where rn =1 and fielda = rpad('3',50,'a')
>>>>> 97 consistent gets
select * from (
select fielda, row_number() over (partition by fielda order by fielda , pk ) rn
from t
) where rn =1 and fielda = rpad('3',50,'a') and rownum = 1
>>>>> 3 consistent gets
why WINDOWS NOSORT STOPKEY cant be used for query with partition by
Andriy, July 15, 2009 - 10:57 am UTC
why WINDOWS NOSORT STOPKEY cant be used for query with partition by and predicate on partition by field?
select * from (
select fielda
, row_number() over (partition by fielda order by 1) rown
from test
)
where fielda = '3aaaaaaaaaaaaaaaaaaaaaaaa' and rown = 1;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | S | E-Rows | E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
|* 1 | VIEW | | 1 | 248K| 00:04:48 | 1 |00:00:00.01 | 3741 |
|* 2 | WINDOW NOSORT | | 1 | 248K| 00:04:48 | 250K|00:00:00.25 | 3741 |
|* 3 | INDEX RANGE SCAN| TEST$I$III | 1 | 248K| 00:00:21 | 250K|00:00:00.01 | 3741 |
-------------------------------------------------------------------------------------------------
As you can see
AROWS = 250000 but if we do the same without partition
select /*+gather_plan_statistics*/* from (
select fielda
, row_number() over (order by 1) rown
from test where fielda = '3aaaaaaaaaaaaaaaaaaaaaaaa'
)
where rown = 1;
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | S| E-Rows | E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
|* 1 | VIEW | | 1| 248K| 00:04:48 | 1 |00:00:00.01 | 5 |
|* 2 | WINDOW NOSORT STOPKEY| | 1| 248K| 00:04:48 | 1 |00:00:00.01 | 5 |
|* 3 | INDEX RANGE SCAN | TEST$I$III | 1| 248K| 00:00:21 | 2 |00:00:00.01 | 5 |
---------------------------------------------------------------------------------------------------
AROWS = 2
July 15, 2009 - 12:26 pm UTC
explain first what use "order by 1" in that context is?
You are getting a random record - that is the same as "order by 'HELLO WORLD'" in that context
Re: why WINDOWS NOSORT STOPKEY cant be used for query with partition by
Andriy, July 15, 2009 - 3:34 pm UTC
why WINDOWS NOSORT STOPKEY cant be used for query with partition by and predicate on partition by field?
without "partition by"
STOPKEY is used (a-rows =
2)
with "partition by" a-rows =
1112SQL> drop table test_stopkey
2 /
Table dropped.
SQL>
SQL> create table test_stopkey as select rownum pk, mod(rownum,100) groupid , lpad('*',999,'*') data from dual connect by level < 111111
2 /
Table created.
SQL>
SQL> alter table test_stopkey modify pk primary key
2 /
Table altered.
SQL>
SQL> alter table test_stopkey modify groupid not null
2 /
Table altered.
SQL>
SQL> create index test_stopkey$i on test_stopkey(groupid,pk)
2 /
Index created.
SQL>
SQL> exec dbms_stats.gather_table_stats(user,'test_stopkey',cascade=>true)
PL/SQL procedure successfully completed.
SQL>
SQL> set serveroutput off
SQL> set pagesize 9999
SQL> set linesize 1000
SQL> set trimspool on
SQL>
SQL> select /*+gather_plan_statistics*/* from (
2 select groupid , pk
3 , row_number() over (order by pk) rown
4 from test_stopkey where groupid = 3
5 )
6 where rown = 1
7 /
GROUPID PK ROWN
---------- ---------- ----------
3 3 1
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'RUNSTATS_LAST LAST'))
2 /
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID b50nmt1jmr4gw, child number 0
-------------------------------------
select /*+gather_plan_statistics*/* from ( select groupid , pk ,
row_number() over (order by pk) rown from test_stopkey where groupid = 3 ) where
rown = 1
Plan hash value: 1214479502
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
|* 1 | VIEW | | 1 | 1093 | 1 |00:00:00.01 | 3 |
|* 2 | WINDOW NOSORT STOPKEY| | 1 | 1093 | 1 |00:00:00.01 | 3 |
|* 3 | INDEX RANGE SCAN | TEST_STOPKEY$I | 1 | 1093 | 2 |00:00:00.01 | 3 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ROWN"=1)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "PK")<=1)
3 - access("GROUPID"=3)
23 rows selected.
SQL>
SQL> select /*+gather_plan_statistics*/* from (
2 select groupid , pk
3 , row_number() over (partition by groupid order by pk) rown
4 from test_stopkey
5 )
6 where rown = 1 and groupid = 3
7 /
GROUPID PK ROWN
---------- ---------- ----------
3 3 1
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'RUNSTATS_LAST LAST'))
2 /
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0ptby2bjg7pz6, child number 0
-------------------------------------
select /*+gather_plan_statistics*/* from ( select groupid , pk ,
row_number() over (partition by groupid order by pk) rown from test_stopkey )
where rown = 1 and groupid = 3
Plan hash value: 1343125809
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------
|* 1 | VIEW | | 1 | 1093 | 1 |00:00:00.01 | 6 |
|* 2 | WINDOW NOSORT | | 1 | 1093 | 1112 |00:00:00.01 | 6 |
|* 3 | INDEX RANGE SCAN| TEST_STOPKEY$I | 1 | 1093 | 1112 |00:00:00.01 | 6 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ROWN"=1)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "GROUPID" ORDER BY "PK")<=1)
3 - access("GROUPID"=3)
23 rows selected.
July 15, 2009 - 4:31 pm UTC
guess that it is just not smart enough in this case to recognize a special case optimization.
I agree that it could in this case - because of the filter/access on groupid.
I'll ask someone else to look at this (someone that could actually change the capability...) and see what they say.
get first line in each group: row_number vs pl/sql
Andriy, July 15, 2009 - 5:41 pm UTC
Followup July 15, 2009 - 4pm US/Eastern:
>>guess that it is just not smart enough in this case to recognize a special case optimization.
>>I agree that it could in this case - because of the filter/access on groupid.
>>I'll ask someone else to look at this (someone that could actually change the capability...) and see what they say.
thanks for your quick response.
Also what can you say about other example?
I try to get the first lines of each group and row_number approach became much slower then pl/sql function. What do you think, is it clear anough to be optimised in sql?
with
row_number its about Elapsed: 00:00:00.31
but
pl/sql do the same in Elapsed: 00:00:00.02
SQL> drop table test_distinct
2 /
Table dropped.
Elapsed: 00:00:00.01
SQL>
SQL> create table
2 test_distinct as select rownum pk
3 , mod(rownum,10) groupid
4 , lpad('*',30,'*') data
5 from dual connect by level < 111111
6 /
Table created.
Elapsed: 00:00:00.21
SQL>
SQL> alter table test_distinct modify pk primary key
2 /
Table altered.
Elapsed: 00:00:00.18
SQL>
SQL> alter table test_distinct modify groupid not null
2 /
Table altered.
Elapsed: 00:00:00.01
SQL>
SQL> create index test_distinct$i on test_distinct(groupid,pk)
2 /
Index created.
Elapsed: 00:00:00.15
SQL>
SQL> exec dbms_stats.gather_table_stats(user,'test_distinct',cascade=>true)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.46
SQL>
SQL> select groupid, count(*) from test_distinct group by groupid
2 /
GROUPID COUNT(*)
---------- ----------
1 11111
6 11111
2 11111
4 11111
5 11111
8 11111
3 11111
7 11111
0 11111
9 11111
10 rows selected.
Elapsed: 00:00:00.04
SQL>
SQL> set serveroutput off
SQL> set linesize 1500
SQL> set trimspool on
SQL> set pagesize 9999
SQL>
SQL> set timing on
SQL>
SQL> select * from (
2 select t.*, row_number() over (partition by groupid order by pk) rn from test_distinct t
3 ) where rn = 1
4 /
PK GROUPID DATA RN
---------- ---------- ------------------------------ ----------
10 0 ****************************** 1
1 1 ****************************** 1
2 2 ****************************** 1
3 3 ****************************** 1
4 4 ****************************** 1
5 5 ****************************** 1
6 6 ****************************** 1
7 7 ****************************** 1
8 8 ****************************** 1
9 9 ****************************** 1
10 rows selected.
Elapsed: 00:00:00.29
SQL>
SQL> drop type test_distinct_tab
2 /
Type dropped.
Elapsed: 00:00:00.01
SQL> drop type test_distinct_row
2 /
Type dropped.
Elapsed: 00:00:00.01
SQL>
SQL> CREATE OR REPLACE TYPE test_distinct_row AS object
2 (
3 groupid number
4 ,data varchar2(30)
5 ,pk number
6 )
7 /
Type created.
Elapsed: 00:00:00.01
SQL>
SQL> CREATE OR REPLACE TYPE test_distinct_tab AS TABLE OF test_distinct_row
2 /
Type created.
Elapsed: 00:00:00.01
SQL>
SQL> CREATE OR REPLACE FUNCTION f
2 RETURN test_distinct_tab PIPELINED
3 IS
4 str test_distinct_row;
5 groupid number;
6 begin
7 select test_distinct_row(groupid,data,pk) into str from ( select groupid , data , pk from test_distinct order by groupid,pk ) where rownum = 1;
8 pipe row(str);
9 LOOP
10 select test_distinct_row(groupid,data,pk) into str from ( select groupid , data , pk from test_distinct where groupid > str.groupid order by groupid,pk ) where rownum = 1;
11 pipe row(str);
12 END LOOP;
13 EXCEPTION
14 WHEN NO_DATA_FOUND
15 THEN null;
16 END;
17 /
Function created.
Elapsed: 00:00:00.03
SQL>
SQL> select * from table(f())
2 /
GROUPID DATA PK
---------- ------------------------------ ----------
0 ****************************** 10
1 ****************************** 1
2 ****************************** 2
3 ****************************** 3
4 ****************************** 4
5 ****************************** 5
6 ****************************** 6
7 ****************************** 7
8 ****************************** 8
9 ****************************** 9
10 rows selected.
Elapsed: 00:00:00.02
July 16, 2009 - 11:08 am UTC
I talked to someone in the group that does the windowing functions and they agree that this is something that should be done (your first issue). right now, if there is a partition by - the push doesn't happen. They are going to integrate that in.
I added, before reading this, to the discussion I was having this bit:
Also, what about a case such as:
create index i on t (x,y);
where X is very low cardinality. A query of the form
select * from (select t.*, row_number() over (partition by x order by y) rn from t) where rn = 1;
could use the equivalent of an index skip scan - say X has 5 unique values, but the table T has 1,000,000 records. You could skip scan to pick up the first record from each X group immediately, rather than hitting the entire structure.
sort of a skip scan PLUS window sort pushed rank
which would cover your case here exactly. Right now, it would have to read the entire structure - if it knew the thing on the leading edge of the index was low cardinality and it "skipped around" in the index, then the SQL would best the procedural approach (and your procedural approach mimics exactly the skip scan I was thinking of)
Beware of a consideration with your approach - this is just a warning.
Normally, a query is read consistent by default with itself. However, when that query calls PLSQL and that plsql queries over and over (runs many individual sql statements), then each of the INDIVIDUAL queries is read consistent with themselves - but not together.
That is, suppose when your query started, there were your 10 groups - 0 through 9.
Now, suppose in some session other than you own, someone issued:
delete from t where groupid = 0;
insert into t (pk, groupid, ... ) value ( s.nextval, 10, .... );
and they have not committed.
When you run the pure sql - it will return a read consistent result set consisting of groupids 0 through 9 - no matter what this other session does.
However, when you run the pipelined function - if the other session issues commit AFTER/DURING your processing of group 0 and before/during the processing of group 9 - you will get groupids 0 through 10 - a state that never existed in the database. So, approach this procedural approach with caution.
RE:get first line in each group: row_number vs pl/sql
Andriy, July 17, 2009 - 1:16 pm UTC
your description "sort of a skip scan PLUS window sort pushed rank" is stiffly accurate and is realy correct summary of my question.
skip scan + windows sort/nosoft/push rank/stopkey will be very efficient.
PS and not only in case when "say X has 5 unique values, but the table T has 1,000,000 records. "
if we have 100 000 distinct values and table T has 1,000,000 records. and, only single value have count(*) = 499 999 the sckip scan with ranking will alow us to skip those 499 999 while processing the table.
PSS
>>However, when you run the pipelined function - if the
>>other session issues commit AFTER/DURING your processing of
>>group 0 and before/during the processing of group 9 - you
>>will get groupids 0 through 10 - a state that never existed
>>in the database. So, approach this procedural approach with
>>caution.
I plan to be very careful with consistency in pl/sql in upper example. Looks like in my design i can currently use it. Thanks for reminding.
July 24, 2009 - 8:37 am UTC
and after talking with the developer of this functionality... a bug was filed by me to have this sort of processing included in the future for the window functions
predicate ordering with pipeline function
A reader, September 08, 2009 - 2:31 pm UTC
Tom,
I am having trouble getting the correct execution order.I have specified the push_pred hint but it doesnt seem to work in my case.
here's what I am trying to do:
---------------------------------------------------
create or replace view v1 as
(
with driving query as
(
select table_1.col1 cola,
table_2.col2 colb,
table_3.col3 colc
from
table_1,
table_2,
table_3 where table_1.col1=table_2.col1
and table_2.col2=table_3.col2
)
--main query
select A,B,C from
table(package_1.function_pipe(cursor(select cola A,
colb B,
colc C
from driving_query))) result
where result.A >0
order by A,B;
----
select /*+ push_pred(alias_1.result.driving_query) */* from v1 alias_1 where C=3
i am trying to push the condition C=3 into the driving_query,
but it always applies the condition to the output of the pipeline function.
I tried different parameters in the push_pred -> alias_1, alias_1.result etc and it doesnt seem to work.
How can i make the conditions be applied to the driving_query first?
thanks a lot!
September 09, 2009 - 5:58 am UTC
... i am trying to push the condition C=3 into the driving_query, ...
how could it do that? In any stretch of the imagination - how could that work?
result.A is NOT the sale as cola from driving_query, result.A is the output of the pipelined function - it has no relationship, no bearing, nothing to do with cola from driving query.
... but it always applies the condition to the output of the pipeline function. ...
of course it does - but only because
a) that is what you coded
b) that is the only reasonable thing that could possibly be done.
... How can i make the conditions be applied to the driving_query first? ....
apply them to driving query!!!!
select A,B,C from
table(package_1.function_pipe(cursor(select cola A,
colb B,
colc C
from driving_query where cola > 0 ))) result
order by A,B;
I cannot even begin to understand how you think a predicate against the output of a pipelined function could be "pushed" into an INPUT to the pipelined function.
A reader, September 09, 2009 - 11:18 am UTC
Thank you Tom, I understand what I was doing wrong now.
Earlier, I would apply the condition to the driving_query , like you said, and store the result in a GTT, then read the GTT and apply the table function on it.
The requirement has changed and I have to do the entire thing in just one step, through a single view call.
Is there a work around for this?
thanks
September 14, 2009 - 10:11 am UTC
move the hint to the cursor that needs it??
Query transformations
A reader, September 27, 2010 - 8:33 am UTC
September 27, 2010 - 12:14 pm UTC
no such list exists as far as I know. Much of the optimizer is not externally documented.
View ...
A Reader, October 04, 2010 - 5:43 am UTC
Hi Tom
Thanks for your time.
Database version : 10.2.0.4
I have a query :
SELECT *
FROM (SELECT t1.objid
FROM view_T1 t1
WHERE (t1.x_case_type = :"SYS_B_0")
AND (t1.s_x_svc_type = :"SYS_B_1")
order by t1.objid asc) WHERE ROWNUM < :"SYS_B_3"
Below is the tkprof.
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 2 8.00 105.69 29132 516072 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 8.00 105.69 29132 516072 0 4
Misses in library cache during parse: 0
Optimizer goal: FIRST_ROWS
Parsing user id: 44
Rows Row Source Operation
------- ---------------------------------------------------
8901 SORT AGGREGATE (cr=316445 pr=13952 pw=0 time=67224331 us)
396 NESTED LOOPS (cr=316445 pr=13952 pw=0 time=67178868 us)
53843 NESTED LOOPS (cr=154882 pr=9749 pw=0 time=20365464 us)
53843 TABLE ACCESS BY INDEX ROWID TTP (cr=45554 pr=1570 pw=0 time=6293669 us)
53843 INDEX RANGE SCAN FL5826 (cr=17970 pr=807 pw=0 time=4761311 us)(object id 45581)
53843 TABLE ACCESS BY INDEX ROWID TT (cr=109328 pr=8179 pw=0 time=40223934 us)
53843 INDEX UNIQUE SCAN SYS_C002665 (cr=53848 pr=805 pw=0 time=4559997 us)(object id 45575)
396 TABLE ACCESS BY INDEX ROWID TCOND (cr=161563 pr=4203 pw=0 time=17550209 us)
53843 INDEX UNIQUE SCAN SYS_C002116 (cr=107691 pr=1508 pw=0 time=6887961 us)(object id 43297)
4 COUNT STOPKEY (cr=516072 pr=29132 pw=0 time=105690903 us)
4 VIEW (cr=516072 pr=29132 pw=0 time=105690892 us)
4 SORT ORDER BY STOPKEY (cr=516072 pr=29132 pw=0 time=105690884 us)
11801 VIEW view_T1 (cr=516072 pr=29132 pw=0 time=148749809 us)
11801 FILTER (cr=516072 pr=29132 pw=0 time=148726201 us)
11801 NESTED LOOPS OUTER (cr=199627 pr=15180 pw=0 time=42196302 us)
8902 NESTED LOOPS OUTER (cr=149398 pr=10522 pw=0 time=19857567 us)
8901 NESTED LOOPS OUTER (cr=140477 pr=10382 pw=0 time=20883264 us)
8901 NESTED LOOPS OUTER (cr=131574 pr=10382 pw=0 time=20785325 us)
8901 NESTED LOOPS OUTER (cr=113770 pr=10382 pw=0 time=20705187 us)
8901 NESTED LOOPS (cr=104923 pr=10369 pw=0 time=20633975 us)
8901 NESTED LOOPS (cr=87119 pr=10313 pw=0 time=20399332 us)
8901 NESTED LOOPS (cr=78216 pr=10313 pw=0 time=20337006 us)
8901 NESTED LOOPS (cr=69313 pr=10313 pw=0 time=20292487 us)
8901 NESTED LOOPS (cr=60410 pr=10313 pw=0 time=20239064 us)
8901 NESTED LOOPS (cr=51507 pr=10313 pw=0 time=20176746 us)
8901 NESTED LOOPS OUTER (cr=42604 pr=10313 pw=0 time=20105507 us)
8901 TABLE ACCESS BY INDEX ROWID TC (cr=42600 pr=10313 pw=0 time=20078789 us)
20883 INDEX RANGE SCAN CASE_TYPE_LVL1_IDX (cr=135 pr=134 pw=0 time=318102 us)(object id 182190)
3 INDEX UNIQUE SCAN SYS_C002557 (cr=4 pr=0 pw=0 time=15835 us)(object id 45106)
8901 INDEX UNIQUE SCAN SYS_C002303 (cr=8903 pr=0 pw=0 time=50471 us)(object id 44120)
8901 INDEX UNIQUE SCAN SYS_C002303 (cr=8903 pr=0 pw=0 time=44152 us)(object id 44120)
8901 INDEX UNIQUE SCAN SYS_C002303 (cr=8903 pr=0 pw=0 time=38435 us)(object id 44120)
8901 INDEX UNIQUE SCAN SYS_C002303 (cr=8903 pr=0 pw=0 time=38552 us)(object id 44120)
8901 INDEX UNIQUE SCAN SYS_C002702 (cr=8903 pr=0 pw=0 time=43853 us)(object id 45701)
8901 INDEX UNIQUE SCAN SYS_C002116 (cr=17804 pr=56 pw=0 time=209139 us)(object id 43297)
8845 INDEX UNIQUE SCAN SYS_C002080 (cr=8847 pr=13 pw=0 time=150783 us)(object id 43143)
8901 INDEX UNIQUE SCAN SYS_C002116 (cr=17804 pr=0 pw=0 time=56172 us)(object id 43297)
8901 INDEX RANGE SCAN IND_EMPLOYEE2USER (cr=8903 pr=0 pw=0 time=66028 us)(object id 43873)
8507 INDEX RANGE SCAN IND_LAST_CLOSE2CASE (cr=8921 pr=140 pw=0 time=1228111 us)(object id 43184)
11662 TABLE ACCESS BY INDEX ROWID TNL (cr=50229 pr=4658 pw=0 time=6795537 us)
83723 INDEX RANGE SCAN IND_CASE_NOTES2CASE (cr=9158 pr=607 pw=0 time=3415069 us)(object id 44491)
8901 SORT AGGREGATE (cr=316445 pr=13952 pw=0 time=67224331 us)
396 NESTED LOOPS (cr=316445 pr=13952 pw=0 time=67178868 us)
53843 NESTED LOOPS (cr=154882 pr=9749 pw=0 time=20365464 us)
53843 TABLE ACCESS BY INDEX ROWID TTP (cr=45554 pr=1570 pw=0 time=6293669 us)
53843 INDEX RANGE SCAN FL5826 (cr=17970 pr=807 pw=0 time=4761311 us)(object id 45581)
53843 TABLE ACCESS BY INDEX ROWID TT (cr=109328 pr=8179 pw=0 time=40223934 us)
53843 INDEX UNIQUE SCAN SYS_C002665 (cr=53848 pr=805 pw=0 time=4559997 us)(object id 45575)
396 TABLE ACCESS BY INDEX ROWID TCOND (cr=161563 pr=4203 pw=0 time=17550209 us)
53843 INDEX UNIQUE SCAN SYS_C002116 (cr=107691 pr=1508 pw=0 time=6887961 us)(object id 43297)
********************************************************************************
View Definition:
CREATE OR REPLACE FORCE VIEW view_T1 (objid,
id_number,
status,
x_directory_number,
x_target_restore_dt,
x_customer_name,
xct,
x_css_fault_ref,
x_op_user_name,
x_smc_notes,
C_Time,
x_case_type,
type1,
xct_jeopardy,
x_svc_type,
s_x_svc_type,
x_recordno,
cnt,
x_jeopardy,
x_current_milestone,
x_result
)
AS
(SELECT T1.objid AS objid,
T1.id_number AS id_number,
table_gse_status.title AS status,
T1.x_directory_number AS x_directory_number,
T1.x_target_restore_dt AS x_target_restore_dt,
T1.x_customer_name AS x_customer_name,
DECODE
(T1.xct,
'00000', 'NEW',
DECODE (T1.xct,
'11111', 'COMPLETED',
SUBSTR (T1.xct,
0,
INSTR (T1.xct, 'UST')
- 1
)
)
) AS xct,
T1.x_css_fault_ref AS x_css_fault_ref,
T1.x_op_user_name AS x_op_user_name,
table_noteslog.description AS x_smc_notes,
T1.C_Time
AS C_Time,
T1.case_type_lvl1 AS x_case_type,
T1.case_type_lvl1 AS type1,
T1.id_number AS xct_jeopardy,
T1.x_svc_type AS x_svc_type,
T1.s_x_svc_type AS s_x_svc_type, 0 AS x_recordno,
(SELECT COUNT (TT.objid)
FROM TT,
TT_participant,
tcond
WHERE TT_participant.task_participant2task = TT.objid
AND TT_participant.focus_lowid = T1.objid
AND TT_participant.focus_type = 0
AND TT.task_state2condition = tcond.objid
AND tcond.title != 'Closed Action Item') AS cnt,
(SELECT table_jpdy_level.title
FROM table_jpdy_level,
table_slm_target,
table_slm
WHERE table_slm_target.slm_target2jpdy_level =
table_jpdy_level.objid
AND table_slm.current2slm_target = table_slm_target.objid
AND table_slm.focus_lowid = T1.objid
AND table_slm.focus_type = 0) AS x_jeopardy,
(SELECT table_slm_target.milestone
FROM table_slm_target, table_slm
WHERE table_slm.current2slm_target =
table_slm_target.objid
AND table_slm.focus_lowid = T1.objid
AND table_slm.focus_type = 0) AS x_current_milestone,
T1.x_result AS x_result
FROM tu table_owner INNER JOIN TC T1
ON table_owner.objid = T1.case_owner2user
INNER JOIN tcond
ON tcond.objid = T1.case_state2condition
INNER JOIN T3 table_gse_status
ON table_gse_status.objid = T1.casests2gbst_elm
LEFT OUTER JOIN TC T2
ON T2.objid = T1.case_victim2case
INNER JOIN T3 table_gse_priority
ON table_gse_priority.objid = T1.respprty2gbst_elm
INNER JOIN T3 table_gse_severity
ON table_gse_severity.objid = T1.respsvrty2gbst_elm
INNER JOIN T3 table_gse_type
ON table_gse_type.objid = T1.calltype2gbst_elm
LEFT OUTER JOIN TNL table_noteslog
ON table_noteslog.case_notes2case = T1.objid
AND table_noteslog.x_flag = 'SMC'
LEFT OUTER JOIN table_queue
ON table_queue.objid = T1.case_currq2queue
LEFT OUTER JOIN tcond cond
ON cond.objid = T1.case_state2condition
LEFT OUTER JOIN table_close_case
ON table_close_case.last_close2case = T1.objid
LEFT OUTER JOIN TE emp
ON table_owner.objid = emp.employee2user
)
ORDER BY C_Time DESC;
------------------------
Further,
....
LEFT OUTER JOIN TNL table_noteslog
ON table_noteslog.case_notes2case = T1.objid
AND table_noteslog.x_flag = 'SMC'
.....
This outer join does not belong to.( Un necessary). we will chnage it in code.
Question:
a) Tkprof shows most of the time spent is here:
....
8901 NESTED LOOPS OUTER (cr=42604 pr=10313 pw=0 time=20105507 us)
8901 TABLE ACCESS BY INDEX ROWID TC (cr=42600 pr=10313 pw=0 time=20078789 us)
20883 INDEX RANGE SCAN CASE_TYPE_LVL1_IDX (cr=135 pr=134 pw=0 time=318102 us)(object id 182190)
3 INDEX UNIQUE SCAN SYS_C002557 (cr=4 pr=0 pw=0 time=15835 us)(object id 45106)
...
I tried hinting it to use Hash_JOIn but not effective.
CASE_TYPE_LVL1_IDX is index on table ( TC) column (CASE_TYPE_LVL1)
b) I tried using analytics to find the top 200 results, But it has no improvement.
( ......WHERE ROWNUM < :"SYS_B_3" is 200. we just want top 200 rows)
Question :
a) how the above query/view can be best re-written ? avoiding re-cursive calls etc..( your expert comments ).
regards
October 04, 2010 - 5:47 am UTC
ouch, cursor sharing = force or similar. How sad is that.
Sorry, but this query is what I would call 'too large' to look at. I'd have to ask for way too much additional information, like the schema, the constraints, etc. In short, a ton of information. then I'd have to figure out what the REAL question being asked was (reverse engineering a query doesn't work, I'd have to live with all of your assumptions found in the query and they may or may not have to exist in real life).
I don't have the time to tune individual queries for everyone - sorry.
contd.. above
A Reader, October 04, 2010 - 9:12 am UTC
Tom,
Thanks.
I was expecting this answer from you, when I posted it.
:)
I will dig out it further.
Thanks for your time.
regards
View changing explain plan to push predicate?
Kim Berg Hansen, March 22, 2011 - 11:59 am UTC
Hi, Tom
I just finished developing a query and very happy with it - executed in about 1 second. Then I did "create view q_v as <the query>" and was surprised that a "select * from q_v" suddenly used 18 seconds?
I cannot contrive an emp/dept example to recreate the behaviour and I won't burden you with 18 table creates and loads of test data :-) But I'll try to sketch what I do and perhaps you have an idea to what I might try to fix it.
My query is along these lines:
with s1 as (
select c1, c2, c3, <aggregates>
from <7-table join with predicates>
group by c1, c2, c3
), s2 as (
select c1, <aggregates>
from <6-table join with predicates>
group by c1
), s3 as (
select c1, c2, c3, <aggregates>
from <2-table join with predicates>
group by c1, c2, c3
)
select
*
from s1
join s2
on s2.c1 = s1.c1
left outer join s3
on s3.c1 = s1.c1
and s3.c2 = s1.c2
and s3.c3 = s1.c3
An explain plan of this goes like this:
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 222 | 215 (2)| 00:00:03 |
|* 1 | HASH JOIN | | 1 | 222 | 215 (2)| 00:00:03 |
|* 2 | HASH JOIN OUTER | | 1 | 162 | 203 (1)| 00:00:03 |
| 3 | VIEW | | 1 | 107 | 17 (42)| 00:00:01 |
| 4 | HASH GROUP BY | | 1 | 213 | 17 (0)| 00:00:01 |
< 7 nested loops >
| 25 | VIEW | | 1 | 55 | 193 (2)| 00:00:03 |
| 26 | HASH GROUP BY | | 1 | 61 | 193 (2)| 00:00:03 |
< hash join of 2 tables >
| 32 | VIEW | | 5 | 300 | 61 (82)| 00:00:01 |
| 33 | HASH GROUP BY | | 5 | 950 | 61 (0)| 00:00:01 |
< 6 nested loops >
------------------------------------------------------------------------------------------------------------------------
Each of my factored subqueries are treated as VIEW with their own independent group by, and the results are hash joined and outer hash joined together. This is very good and just as I expected.
Now I do one very simple thing:
select * from (
<all of the above query>
)
The explain plan now is like this:
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 222 | 23 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 222 | 23 (0)| 00:00:01 |
| 2 | NESTED LOOPS OUTER | | 1 | 165 | 11 (0)| 00:00:01 |
| 3 | VIEW | | 1 | 118 | 17 (42)| 00:00:01 |
| 4 | HASH GROUP BY | | 1 | 213 | 17 (0)| 00:00:01 |
< 7 nested loops >
|* 25 | VIEW PUSHED PREDICATE | | 1 | 47 | 6 (17)| 00:00:01 |
| 26 | SORT GROUP BY | | 1 | 61 | 6 (17)| 00:00:01 |
< 2 nested loops >
| 33 | VIEW PUSHED PREDICATE | | 1 | 57 | | |
|* 34 | FILTER | | | | | |
| 35 | SORT AGGREGATE | | 1 | 190 | | |
< 6 nested loops >
------------------------------------------------------------------------------------------------------------------------
Gone are my beautiful hash joins and now replaced by nested loops and pushed predicates?
This explain plan of course also happens with my select * from <view> which is the same as select * from (<query>).
What could be possible explanations of why an explain plan changes so dramatically just by putting a "select *" around a query? I would like to understand why this is happening :-)
I can see that cardinality is off. The statement actually returns 431 rows (having read maybe a total of about 10-20.000 rows througout the tables involved.) That is something I have to try and fix somehow. But it does not explain why there is a difference in the two plans?
Thanks in advance for any pointers.
Predicate pushing
A reader, August 23, 2011 - 3:33 pm UTC
On 10g R2, I have the following query;
select t1.col1,
t1.col2,
sum_vw.*
from t1,
error_list pl,
(select t1.col1,
sum(t1.amt1) amt1,
sum(t1.amt1) amt2,
sum(t1.amt1) amt3,
sum(t1.amt1) amt4
from t1
group by t1.col1
) sum_vw
where t1.id = pl.id
and sum_vw.col1 = t1.col1
The above is not predicate pushing and I assume that's because of the group by, doing a full scan of T1 in the sum_vw. I've ended up doing the following instead;
select t1.col1,
t1.col2,
(select sum(a1.amt1) from a1 where a1.col1 = t1.col1 group by a1.col1) amt1,
(select sum(a1.amt2) from a1 where a1.col1 = t1.col1 group by a1.col1) amt2,
(select sum(a1.amt3) from a1 where a1.col1 = t1.col1 group by a1.col1) amt3,
(select sum(a1.amt4) from a1 where a1.col1 = t1.col1 group by a1.col1) amt4
from t1,
error_list pl,
(select t1.col1,
sum(t1.amt1) amt1,
sum(t1.amt1) amt2,
sum(t1.amt1) amt3,
sum(t1.amt1) amt4
from t1
group by t1.col1
) sum_vw
where t1.id = pl.id
and sum_vw.col1 = t1.col1
The performance improves dramatically with the above. Is this the right thing to do ?
I heard in 11g that join predicate pushing(a.k.a JPPD) would mean I can use HINTs to get round this issue?
August 30, 2011 - 3:26 pm UTC
I heard in 11g that join predicate pushing(a.k.a JPPD) would mean I can use HINTs to get round this issue?
No, it just means that if we can - we will do that. No hints.
I don't see any predicates that would be pushed - do you have a full example - there must be more going on here then you posted - these are views or something?
pushing predicates
A reader, August 23, 2011 - 10:19 pm UTC
oops, sorry bad cut&paste job above, should be;
select t1.col1,
t1.col2,
(select sum(a1.amt1) from a1 where a1.col1 = t1.col1 group by a1.col1) amt1,
(select sum(a1.amt2) from a1 where a1.col1 = t1.col1 group by a1.col1) amt2,
(select sum(a1.amt3) from a1 where a1.col1 = t1.col1 group by a1.col1) amt3,
(select sum(a1.amt4) from a1 where a1.col1 = t1.col1 group by a1.col1) amt4
from t1,
error_list pl
where t1.id = pl.id
Predicate not pushed in
Raj, May 12, 2012 - 6:51 am UTC
Hi Tom,
I am not able to understand why the predicate is pushed in the first case but not in the second.
testcase
<code>
drop table test_dept
/
drop table test_emp
/
drop table test_emp1
/
drop table temp_staging
/
create table test_dept
(
deptno number not null,
dname varchar2(30)
)
/
create index test_dept_idx on test_dept(deptno)
/
create table test_emp
(
empno number not null ,
deptno number not null
)
/
create index test_emp_idx on test_emp(deptno)
/
create table test_emp1
(
empno number not null ,
deptno number not null
)
/
create index test_emp1_idx on test_emp1(deptno)
/
create table temp_staging
(
username varchar2(30) ,
id number ,
deptno number
)
/
create index temp_staging_idx on temp_staging(username, id)
/
insert into test_dept
( deptno, dname )
select level, 'DNAME' || level from dual connect by level <= 10
/
insert into test_emp
( empno, deptno )
select level, mod(level, 10) + 1 from dual connect by level <= 500
/
insert into test_emp1
( empno, deptno )
select level, mod(level, 10) + 1 from dual connect by level <= 500
/
insert into temp_staging
( username, id, deptno )
select 'user' || round(level / 3) , round(level / 3) id, round(level / 3) deptno
from dual connect by level <= 400
/
begin
dbms_stats.gather_table_stats(null, 'TEST_DEPT', cascade => true);
dbms_stats.gather_table_stats(null, 'TEST_EMP', CASCADE => true);
dbms_stats.gather_table_stats(null, 'TEST_EMP1', cascade => true);
dbms_stats.gather_table_stats(null, 'TEMP_STAGING', cascade => true);
end;
/
with dept_filter
as
(
select username, id, dept.deptno, count(*) over(partition by username, id) cnt
from test_dept dept, temp_staging
where
dept.deptno = temp_staging.deptno
),
emp_select as
(
select emp.*, username, id
from test_emp emp, dept_filter
where
emp.deptno = dept_filter.deptno
),
emp1_select as
(
select emp1.*, username, id
from test_emp1 emp1, dept_filter
where
emp1.deptno = dept_filter.deptno
),
combine_all as
(
select * from emp_select
union all
select * from emp1_select
)
select --+ gather_plan_statistics
* from emp_select
where username = 'user1' and id = 1
/
select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'))
/
with dept_filter
as
(
select username, id, dept.deptno, count(*) over(partition by username, id) cnt
from test_dept dept, temp_staging
where
dept.deptno = temp_staging.deptno
),
emp_select as
(
select emp.*, username, id
from test_emp emp, dept_filter
where
emp.deptno = dept_filter.deptno
),
emp1_select as
(
select emp1.*, username, id
from test_emp1 emp1, dept_filter
where
emp1.deptno = dept_filter.deptno
),
combine_all as
(
select * from emp_select
union all
select * from emp1_select
)
select --+ gather_plan_statistics
* from combine_all
where username = 'user1' and id = 1
/
select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'))
/
output of the above code is
SQL> @ test_case
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
Table dropped.
Table dropped.
Table dropped.
Table dropped.
Table created.
Index created.
Table created.
Index created.
Table created.
Index created.
Table created.
Index created.
10 rows created.
500 rows created.
500 rows created.
400 rows created.
PL/SQL procedure successfully completed.
output snipped....
150 rows selected.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 947jq2jfttbw1, child number 0
-------------------------------------
with dept_filter as ( select username, id, dept.deptno, count(*)
over(partition by username, id) cnt from test_dept dept,
temp_staging where dept.deptno = temp_staging.deptno ),
emp_select as ( select emp.*, username, id from test_emp emp,
dept_filter where emp.deptno = dept_filter.deptno ),
emp1_select as ( select emp1.*, username, id from test_emp1 emp1,
dept_filter where emp1.deptno = dept_filter.deptno ),
combine_all as ( select * from emp_select union all select *
from emp1_select ) select --+ gather_plan_statistics * from emp_select
where username = 'user1' and id = 1
Plan hash value: 521486985
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 150 |00:00:00.01 | 38 |
| 1 | NESTED LOOPS | | 1 | | 150 |00:00:00.01 | 38 |
| 2 | NESTED LOOPS | | 1 | 149 | 150 |00:00:00.01 | 27 |
| 3 | NESTED LOOPS | | 1 | 3 | 3 |00:00:00.01 | 14 |
| 4 | TABLE ACCESS BY INDEX ROWID| TEMP_STAGING | 1 | 3 | 3 |00:00:00.01 | 8 |
|* 5 | INDEX RANGE SCAN | TEMP_STAGING_IDX | 1 | 3 | 3 |00:00:00.01 | 5 |
|* 6 | INDEX RANGE SCAN | TEST_DEPT_IDX | 3 | 1 | 3 |00:00:00.01 | 6 |
|* 7 | INDEX RANGE SCAN | TEST_EMP_IDX | 3 | 50 | 150 |00:00:00.01 | 13 |
| 8 | TABLE ACCESS BY INDEX ROWID | TEST_EMP | 150 | 50 | 150 |00:00:00.01 | 11 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("USERNAME"='user1' AND "ID"=1)
6 - access("DEPT"."DEPTNO"="TEMP_STAGING"."DEPTNO")
7 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
output of the second query snipped
EMPNO DEPTNO USERNAME ID
---------- ---------- ------------------------------ ----------
10 1 user1 1
10 1 user1 1
....
500 1 user1 1
300 rows selected.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9rd9knkfn2bpa, child number 0
-------------------------------------
with dept_filter as ( select username, id, dept.deptno, count(*)
over(partition by username, id) cnt from test_dept dept,
temp_staging where dept.deptno = temp_staging.deptno ),
emp_select as ( select emp.*, username, id from test_emp emp,
dept_filter where emp.deptno = dept_filter.deptno ),
emp1_select as ( select emp1.*, username, id from test_emp1 emp1,
dept_filter where emp1.deptno = dept_filter.deptno ),
combine_all as ( select * from emp_select union all select *
from emp1_select ) select --+ gather_plan_statistics * from combine_all
where username = 'user1' and id = 1
Plan hash value: 2678387589
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 300 |00:00:00.01 | 60 | 1 | 1 | | | |
| 1 | TEMP TABLE TRANSFORMATION | | 1 | | 300 |00:00:00.01 | 60 | 1 | 1 | | | |
| 2 | LOAD AS SELECT | | 1 | | 1 |00:00:00.01 | 14 | 0 | 1 | 265K| 265K| 265K (0)|
| 3 | WINDOW SORT | | 1 | 30 | 30 |00:00:00.01 | 10 | 0 | 0 | 4096 | 4096 | 4096 (0)|
| 4 | NESTED LOOPS | | 1 | 30 | 30 |00:00:00.01 | 10 | 0 | 0 | | | |
| 5 | TABLE ACCESS FULL | TEMP_STAGING | 1 | 400 | 400 |00:00:00.01 | 7 | 0 | 0 | | | |
|* 6 | INDEX RANGE SCAN | TEST_DEPT_IDX | 400 | 1 | 30 |00:00:00.01 | 3 | 0 | 0 | | | |
| 7 | VIEW | | 1 | 3000 | 300 |00:00:00.01 | 43 | 1 | 0 | | | |
| 8 | UNION-ALL | | 1 | | 300 |00:00:00.01 | 43 | 1 | 0 | | | |
|* 9 | HASH JOIN | | 1 | 1500 | 150 |00:00:00.01 | 23 | 1 | 0 | 1114K| 1114K| 379K (0)|
|* 10 | VIEW | | 1 | 30 | 3 |00:00:00.01 | 6 | 1 | 0 | | | |
| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9FC871_755942EE | 1 | 30 | 30 |00:00:00.01 | 6 | 1 | 0 | | | |
| 12 | TABLE ACCESS FULL | TEST_EMP | 1 | 500 | 500 |00:00:00.01 | 17 | 0 | 0 | | | |
|* 13 | HASH JOIN | | 1 | 1500 | 150 |00:00:00.01 | 20 | 0 | 0 | 1114K| 1114K| 371K (0)|
|* 14 | VIEW | | 1 | 30 | 3 |00:00:00.01 | 3 | 0 | 0 | | | |
| 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9FC871_755942EE | 1 | 30 | 30 |00:00:00.01 | 3 | 0 | 0 | | | |
| 16 | TABLE ACCESS FULL | TEST_EMP1 | 1 | 500 | 500 |00:00:00.01 | 17 | 0 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("DEPT"."DEPTNO"="TEMP_STAGING"."DEPTNO")
9 - access("EMP"."DEPTNO"="DEPT_FILTER"."DEPTNO")
10 - filter(("USERNAME"='user1' AND "ID"=1))
13 - access("EMP1"."DEPTNO"="DEPT_FILTER"."DEPTNO")
14 - filter(("USERNAME"='user1' AND "ID"=1))
As you can see from the execution plan predicate has been pushed in the first query whereas in the second query it's getting pushed post the temp table transformation. I know that it's not doing a lot of buffer gets but in my real example the staging table is smaller but the other tables (emp, emp1) are in millions.
I am experiencing this only when I have to combine these two tables.
Your comments on this ?
Thanks
Raj</code>
May 12, 2012 - 7:22 am UTC
it is not that it cannot be, but rather - due to the materialization of some of the with factored subqueries - it chose not to.
You use the with subquery factoring predominantly as a 'hint' to tell the optimizer "if you see me using this thing more than once, it would be nice for you to materialize it and use the temp table instead"
If you were to add a materialize hint to the dept_filter on the first query, you would see the same behavior.
Raj, May 12, 2012 - 9:30 am UTC
Hi Tom,
yes I can see that behaviour happening when use the materialize hint in the first query. However, in this circumstance I don't want oracle to materialize it before applying the filter. It's materializing it and then the predicate is pushed in on the materialized view. I am not sure why the optimizer thinks it's not safe to push the predicate before it is getting materialized. I will try to run the 10053 trace in my sand box. But in the mean time can you think of an alternative option. I am not sure what information I need to give the optimizer to achieve that. Can you think of any ? The reason I don't want it to apply the filter late is because the staging table data can be in thousands and I don't want it to scan thousands of buffers every time when post applying the filter it's going to be only a handful. I don't want to hint it if I can avoid it. But I don't mind using that as a last resort.
thanks for your help.
Raj
May 12, 2012 - 11:08 am UTC
then use inline views, not with subquery factoring. with subquery factoring is like a hint to "materialize" - in particular when you reference the with subquery view more than once.
or, push the predicate yourself in the first place.
"sort of skip scan plus window sort pushed".
Carlo Sirna, June 12, 2012 - 9:51 am UTC
Hi, Tom,
During the discussion of this topic it came up a new possible optimized access method you proposed and described as a "sort of skip scan plus window sort pushed".
Since has passed some time since that reply of yours, I was wondering if the optimization you proposed to the "analytic functions team", has been implemented or it is "yet to be done" or it will never be implemented because they did find some drawbacks.
Looking at the execution plans I have here it seems that in 11R2 it is still missing, and actually I have tons of queries that would have great gains from it.
Should I start to rethink my queries (making them maybe less readable and harder to mantain) or may I cross figers waiting for oracle 12?
Thank you!
June 12, 2012 - 11:35 am UTC
I'm not sure myself.... something else to test soon.
I'm not huge into playing with the betas (of anything) too much - too many things change.
but it is getting close to the time for doing so...
With analytic function (not) using index
Sasa Petkovic, August 21, 2012 - 3:59 am UTC
Hi Tom,
I have been on your seminar in Belgrade, Serbia (May 2012)and we have had a chat about one thing I promised would send to you working example.
it is about (not) using index with analytic function.
There it is.
set echo on;
select * from v$version;
drop table test_af;
create table test_af as select * from all_objects;
alter table test_af add primary key (object_id);
create index i_test_af_owner on test_af (owner);
analyze table test_af compute statistics for table for all columns for all indexes;
var owner varchar2(25);
exec :owner := 'SYS';
set timing on;
set autotrace traceonly;
rem 1. without analytic function use index
select * from (
select
test_af.*
from test_af
)
where owner = :owner;
rem 2. with analytic function "partitioned by object_type" - full table scan
select * from (
select
row_number() over (partition by object_type order by created) as rn,
test_af.*
from test_af
)
where owner = :owner;
rem 3.with analytic function but different "partition by" - use index
select * from (
select
row_number() over (partition by owner, object_type order by created) as rn,
test_af.*
from test_af
)
where owner = :owner;
Same results are with Oracle 9 and 10g.
Regards,
August 28, 2012 - 1:12 pm UTC
analyze table test_af compute statistics for table for all columns for all
indexes;please don't use that, dbms_stats is the only way to gather statistics these days...
this one is easy.
The result of
select * from (
select
row_number() over (partition by object_type order by created) as rn,
test_af.*
from test_af
)
where owner = :owner;
is very very very different from
select
row_number() over (partition by object_type order by created) as rn,
test_af.*
from test_af
where owner = :owner;
analytics are processed AFTER THE WHERE CLAUSE is evaluated. So, applying a where clause to an analytic is very different from evaluating a where clause AND THEN applying the analytic!!!
The first query with the analytic asked:
please assign a sequential number from one on up to each group of object types after sorting by created from small to big.
AND THEN keep only the rows where owner = :owner.
You see - it has to process ALL ROWS, for ALL OBJECT TYPES, sorting by created and then filtering.
The second query with analytics says
please assign a sequential number from one on up to each group of OWNERS and then OBJECT_TYPES after sorting by created. But note that we only need to do this for owner = :owner because we are going to filter out any groups that do not satisfy that.
they are very very different questions. the first analytic partitioned by just object_type had to process EVERY SINGLE ROW in the table to produce the correct analytic result AND THEN filter by owner.
The second said "I see you are processing by OWNER,OBJECT_TYPE - and keeping only owner = :owner, so I can skip processing all of the other owners. I can use the index to find only those that I'm interested in.
push predicate with aggregates
dx, January 11, 2013 - 5:11 am UTC
Tom
I have a partitioned table TRANSACTIONS partitioned by DATETIME.
I have a local partitioned index on this table on column IDCUSTOMER.
I also have a table SBCUSTOMER which I need to join to.
When I run the following query:
SELECT *
FROM sbcustomer cb,
(SELECT idsbcustomer
FROM transactions t
GROUP BY idsbcustomer) tr
WHERE cb.idsbcustomer = tr.idsbcustomer
AND cb.idsbcustomerbet = 201369585
AND cb.taketimestamp >= TO_DATE (:l_fromdate, 'dd/mm/yyyy');
Execution Plan
----------------------------------------------------------
Plan hash value: 912407995
--------------------------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH GROUP BY | |
|* 2 | FILTER | |
| 3 | NESTED LOOPS OUTER | |
| 4 | PARTITION RANGE ITERATOR | |
| 5 | TABLE ACCESS BY LOCAL INDEX ROWID | SBCUSTOMER |
|* 6 | INDEX RANGE SCAN | IDX_SBCUSTOMERPT_TME |
| 7 | PARTITION RANGE ALL | |
| 8 | PARTITION LIST ALL | |
|* 9 | TABLE ACCESS BY LOCAL INDEX ROWID| TRANSACTIONS |
|* 10 | INDEX RANGE SCAN | I_TRANSACTIONS2_2_PT |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE(:L_FROMDATE,'dd/mm/yyyy')<TO_DATE(:L_TODATE,'dd/mm/yyyy')+1)
6 - access("CB"."TAKETIMESTAMP">=TO_DATE(:L_FROMDATE,'dd/mm/yyyy') AND
"CB"."TAKETIMESTAMP"<TO_DATE(:L_TODATE,'dd/mm/yyyy')+1)
9 - filter("T"."datetime"(+)>=TO_DATE(:L_FROMDATE,'dd/mm/yyyy') AND "CB"."TAKETIMESTAMP"<="T"."datetime"(+))
10 - access("CB"."IDSBCUSTOMER"="T"."IDSBCUSTOMER"(+))
I can see that the predicate on idcustomer is being pushed down into the inner aggregate query so that the local index on idcustomer is being used to access the transactions table which is scanning each local index of ALL the partitions.
However I also know that the transaction table datetime is always greater then the sbcustomer taketimestamp date so I actually only need to scan some of the partitions of transaction table.
How can I write the query so that the datetime predicate is also pushed into the group by inner query?
If I do this:
SELECT *
FROM sbcustomer cb,
(SELECT idsbcustomer,
MIN(datetime) As datetime
FROM transactions t
GROUP BY idsbcustomer) tr
WHERE cb.idsbcustomer = tr.idsbcustomer
AND cb.taketimestamp <= tr.datetime
AND cb.idsbcustomerbet = 201369585
AND cb.taketimestamp >= TO_DATE (:l_fromdate, 'dd/mm/yyyy');
I still get PARTITION RANGE ALL (step 7) in the execution plan. So the optimizer is unable to push the datetime predicate to perform partition pruning on the TRANSACTION table, why is this not possible?
Is it something to do with my local index on IDSCUSTOMER column only? Does it need to be prefixed with datetime or is there some of other technique i can use?
Thanks
January 15, 2013 - 8:40 am UTC
give simple creates (NO TABLESPACES - just simple creates)
some dbms_stats.set_table_stats calls to set representative rowcounts on the partitions
so I can run the sql and play with it.
isn't this as easy as just putting
(SELECT idsbcustomer
FROM transactions t
where datetime >= TO_DATE (:l_fromdate, 'dd/mm/yyyy')
GROUP BY idsbcustomer) tr
???
predicate push problem
josh, March 29, 2013 - 6:27 pm UTC
Hi Tom,
I am having hard time pushing the predicates for the query that involves Remote tables(or views) and local tables(or views). Especially when the query contains outer joins and more than 1 remote table(or view). When i go through the 10053, it shows the reasons “JPPD bypassed: View not on right-side of outer-join” and “JPPD bypassed: Remote table referenced”. I don’t see any documentation on these restrictions or details. DB Version is 11.2.0.2. Below is the sample query.
It works alright(pushes predicates) when all the tables/views in the query are local. I mean, same exact query with the remote tables replaced with local tables.
select /*+ push_pred(c) */ a.id, a.nam, b.typ, b.disc, c.ttyp, c.mnt
from remote_tbl1@dblink a, remote_tbl2@dblink b, local_view c
where a.id = :B1 and a.typid = c.typid(+) and a.bid = b.bid(+);
-------------------------------------------------------------------------------------------------------------------------
| Id | Pid | Ord | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Inst |IN-OUT|
-------------------------------------------------------------------------------------------------------------------------
| 0 | | 7 | SELECT STATEMENT | | 1 | 264 | | 138K (18)| 00:06:56 | | |
|* 1 | 0 | 6 | HASH JOIN OUTER | | 1 | 264 | | 138K (18)| 00:06:56 | | |
| 2 | 1 | 1 | REMOTE | | 1 | 142 | | 5 (0)| 00:00:01 | TEST_~ | R->S |
| 3 | 1 | 5 | VIEW | LOCAL_VIEW | 1422K| 193M| | 138K (17)| 00:06:55 | | |
|* 4 | 3 | 4 | HASH JOIN | | 1422K| 132M| 157M| 138K (17)| 00:06:55 | | |
| 5 | 4 | 2 | TABLE ACCESS FULL| LOC_TAB1 | 1422K| 84M| | 79613 (18)| 00:03:59 | | |
| 6 | 4 | 3 | TABLE ACCESS FULL| LOC_TAB2 | 5123K| 63M| | 46559 (18)| 00:02:20 | | |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."TYPID"="C"."TYPID"(+))
4 - access("C"."ID"="D"."ID")
Remote SQL Information (identified by operation id):
----------------------------------------------------
2 - SELECT A1.ID,A1.NAM,A1.TYPID,A2.TYP,A2.DISC FROM
"TEST"."REMOTE_TBL1" "A1","TEST"."REMOTE_TBL2" "A2" WHERE
"A1"."BID"="A2"."BID"(+) AND "A1"."ID"=:B1 (accessing
'RMT_DBLINK.DB.ATT.COM' )
predicate pushing for union all
dx, August 04, 2013 - 1:06 pm UTC
Hi
I have a problem which could be down to predicate pushing.
I have 2 partitioned tables which i am unioning together and joining to a third table which is also partitioned.
I have 2 possible queries to do this each performing differnently.
Method 1. Union table CUSTOMERORDER and WEBCUSTOMERACTION together and join result to table SBEVENT.
Method 2. Join table CUSTOMERORDER to table SBEVENT and union with a join of table WEBCUSTOMERACTION to SBEVENT.
I would've thought that method 1 would be faster since it only involves one scan of table c index and the subequent table access.
However it is method 2 that is proving to be more efficient.
These are the table creation scripts:
CREATE TABLE spin_d.customerorder (idcustomerorder NUMBER, idsbaccount NUMBER, idsbevent NUMBER, idoperator VARCHAR2 (30 BYTE), taketimestamp DATE)
NOCOMPRESS
PARTITION BY RANGE (taketimestamp)
INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )
(PARTITION nov_2011 VALUES LESS THAN (TO_DATE (' 2011-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS,
PARTITION VALUES LESS THAN (TO_DATE (' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION VALUES LESS THAN (TO_DATE (' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION VALUES LESS THAN (TO_DATE (' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION VALUES LESS THAN (TO_DATE (' 2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION VALUES LESS THAN (TO_DATE (' 2012-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION VALUES LESS THAN (TO_DATE (' 2012-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION VALUES LESS THAN (TO_DATE (' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION VALUES LESS THAN (TO_DATE (' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION VALUES LESS THAN (TO_DATE (' 2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION VALUES LESS THAN (TO_DATE (' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION VALUES LESS THAN (TO_DATE (' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION VALUES LESS THAN (TO_DATE (' 2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION VALUES LESS THAN (TO_DATE (' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION VALUES LESS THAN (TO_DATE (' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION VALUES LESS THAN (TO_DATE (' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION VALUES LESS THAN (TO_DATE (' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION VALUES LESS THAN (TO_DATE (' 2013-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION VALUES LESS THAN (TO_DATE (' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION VALUES LESS THAN (TO_DATE (' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION VALUES LESS THAN (TO_DATE (' 2013-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION VALUES LESS THAN (TO_DATE (' 2013-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS)
NOCACHE
PARALLEL (DEGREE 2 INSTANCES 1)
MONITORING
ENABLE ROW MOVEMENT;
CREATE INDEX spin_d.idx_customerorder_evt
ON spin_d.customerorder (idsbevent)
LOCAL
NOPARALLEL;
CREATE UNIQUE INDEX spin_d.tmp$$_customerorder_pk0
ON spin_d.customerorder (idcustomerorder)
LOGGING
NOPARALLEL;
ALTER TABLE spin_d.customerorder ADD (
CONSTRAINT con_customerorder1
CHECK ("IDCUSTOMERORDER" IS NOT NULL)
ENABLE NOVALIDATE,
CONSTRAINT customerorder_pk
PRIMARY KEY
(idcustomerorder)
USING INDEX spin_d.tmp$$_customerorder_pk0
ENABLE NOVALIDATE);
CREATE TABLE spin_d.webcustomeraction (idwebcustomeraction NUMBER,
idoperatorrequested VARCHAR2 (90 BYTE),
tsrequested DATE,
idsbaccount NUMBER,
idsbevent NUMBER)
NOCOMPRESS
PARTITION BY RANGE (tsrequested)
INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )
(PARTITION nov_2011 VALUES LESS THAN (TO_DATE (' 2011-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION dec_2011 VALUES LESS THAN (TO_DATE (' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION jan_2012 VALUES LESS THAN (TO_DATE (' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION feb_2012 VALUES LESS THAN (TO_DATE (' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION mar_2012 VALUES LESS THAN (TO_DATE (' 2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION apr_2012 VALUES LESS THAN (TO_DATE (' 2012-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION may_2012 VALUES LESS THAN (TO_DATE (' 2012-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION jun_2012 VALUES LESS THAN (TO_DATE (' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION jul_2012 VALUES LESS THAN (TO_DATE (' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION aug_2012 VALUES LESS THAN (TO_DATE (' 2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION sep_2012 VALUES LESS THAN (TO_DATE (' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION oct_2012 VALUES LESS THAN (TO_DATE (' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION nov_2012 VALUES LESS THAN (TO_DATE (' 2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION dec_2012 VALUES LESS THAN (TO_DATE (' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION jan_2013 VALUES LESS THAN (TO_DATE (' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION feb_2013 VALUES LESS THAN (TO_DATE (' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION mar_2013 VALUES LESS THAN (TO_DATE (' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION apr_2013 VALUES LESS THAN (TO_DATE (' 2013-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION may_2013 VALUES LESS THAN (TO_DATE (' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION jun_2013 VALUES LESS THAN (TO_DATE (' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION jul_2013 VALUES LESS THAN (TO_DATE (' 2013-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS,
PARTITION VALUES LESS THAN (TO_DATE (' 2013-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS)
NOCACHE
PARALLEL (DEGREE 2 INSTANCES 1)
MONITORING
ENABLE ROW MOVEMENT;
CREATE INDEX spin_d.if_wca_sbevent
ON spin_d.webcustomeraction (idsbevent)
LOCAL
PARALLEL (DEGREE 4 INSTANCES 1);
CREATE UNIQUE INDEX spin_d.tmp$$_webcustomeraction_p0
ON spin_d.webcustomeraction (idwebcustomeraction)
LOGGING
NOPARALLEL;
ALTER TABLE spin_d.webcustomeraction ADD (
CONSTRAINT webcustomeraction_pk
PRIMARY KEY
(idwebcustomeraction)
USING INDEX spin_d.tmp$$_webcustomeraction_p0
ENABLE NOVALIDATE);
I have shown the execution plans below which were got from runing on our system with lots of data in the tables.
You should still see similar plans when run just using the creation scripts provided and minimal or no table data.
I believe the problem with method 1 lies in the fact that after reading the index for table from partitions 15 to 21 for WEBCUSTOMERACTION (number 19 in plan 1) it still tries to read all partitions when accessing the table (hence the 184K in STARTS column for step 18).
Method 2 filters out the rows at step 26 and then uses those filtered rowids to access the table and so STARTS is much smaller at step 29 for method 2.
Method 1 sql:
SELECT /*+ NO_PARALLEL FIRST_ROWS */
*
---------------------------------
FROM (SELECT /*+ NO_PARALLEL FIRST_ROWS */
scb.idsbevent AS idsbevent
FROM customerorder scb
WHERE 1 = 1
AND taketimestamp >= TO_DATE ('01/01/2013', 'dd/mm/yyyy')
AND taketimestamp < TO_DATE ('31/07/2013', 'dd/mm/yyyy') + 1
UNION ALL
SELECT /*+ NO_PARALLEL FIRST_ROWS */
wca.idsbevent
FROM webcustomeraction wca
WHERE 1 = 1
AND wca.tsrequested >= TO_DATE ('01/01/2013', 'dd/mm/yyyy')
AND wca.tsrequested < (TO_DATE ('31/07/2013', 'dd/mm/yyyy') + 1)) bets,
(SELECT /*+ NO_PARALLEL FIRST_ROWS */
idsbevent
FROM sbevent ev
WHERE 1 = 1
AND ev.idsbeventtype = 197
AND ev.idsbeventcategory = 242) sbe
WHERE 1 = 1
AND bets.idsbevent = sbe.idsbevent;
The execution plan for this sql when run on our system with a lot of data is:
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6439 (100)| | | | 8596 |00:00:18.02 | 761K| 15101 |
| 1 | WINDOW SORT | | 1 | 1 | 782 | 6439 (1)| 00:01:31 | | | 8596 |00:00:18.02 | 761K| 15101 |
| 2 | NESTED LOOPS | | 1 | 1 | 782 | 6438 (1)| 00:01:31 | | | 8596 |00:00:17.97 | 761K| 15101 |
| 3 | PARTITION RANGE ALL | | 1 | 16 | 240 | 82 (0)| 00:00:02 | 1 |1048575| 26377 |00:00:12.71 | 9664 | 9594 |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID | SBEVENT | 22 | 16 | 240 | 82 (0)| 00:00:02 | 1 |1048575| 26377 |00:00:12.70 | 9664 | 9594 |
| 5 | BITMAP CONVERSION TO ROWIDS | | 22 | | | | | | | 26377 |00:00:00.30 | 112 | 112 |
| 6 | BITMAP AND | | 22 | | | | | | | 18 |00:00:00.29 | 112 | 112 |
|* 7 | BITMAP INDEX SINGLE VALUE | IND_SBEVENTPT_TYPE | 22 | | | | | 1 |1048575| 23 |00:00:00.12 | 46 | 46 |
| 8 | BITMAP CONVERSION FROM ROWIDS | | 22 | | | | | | | 18 |00:00:00.17 | 66 | 66 |
|* 9 | INDEX RANGE SCAN | IND_SBEVENTPT_CATEGORY | 22 | 7849 | | 53 (0)| 00:00:01 | 1 |1048575| 26379 |00:00:00.16 | 66 | 66 |
| 10 | VIEW | | 26377 | 1 | 767 | 397 (0)| 00:00:06 | | | 8596 |00:00:05.23 | 752K| 5507 |
| 11 | UNION ALL PUSHED PREDICATE | | 26377 | | | | | | | 8596 |00:00:05.20 | 752K| 5507 |
| 12 | NESTED LOOPS | | 26377 | 19 | 1596 | 375 (0)| 00:00:06 | | | 7731 |00:00:02.74 | 375K| 2409 |
| 13 | FAST DUAL | | 26377 | 1 | | 2 (0)| 00:00:01 | | | 26377 |00:00:00.02 | 0 | 0 |
| 14 | PARTITION RANGE ITERATOR | | 26377 | 19 | 1596 | 373 (0)| 00:00:06 | 15 | 21 | 7731 |00:00:02.68 | 375K| 2409 |
|* 15 | TABLE ACCESS BY LOCAL INDEX ROWID| SBCUSTOMERBET | 184K| 19 | 1596 | 373 (0)| 00:00:06 | 15 | 21 | 7731 |00:00:02.56 | 375K| 2409 |
|* 16 | INDEX RANGE SCAN | IDX_SBCUSTOMERBET_EVT | 184K| 477 | | 14 (0)| 00:00:01 | 15 | 21 | 7731 |00:00:01.22 | 369K| 74 |
| 17 | PARTITION RANGE ITERATOR | | 26377 | 1 | 76 | 22 (0)| 00:00:01 | 15 | 21 | 865 |00:00:02.34 | 376K| 3098 |
|* 18 | TABLE ACCESS BY LOCAL INDEX ROWID | SBWEBCUSTOMERACTION | 184K| 1 | 76 | 22 (0)| 00:00:01 | 15 | 21 | 865 |00:00:02.23 | 376K| 3098 |
|* 19 | INDEX RANGE SCAN | IF_SBWCA_SBEVENT | 184K| 7 | | 15 (0)| 00:00:01 | 15 | 21 | 8362 |00:00:00.98 | 369K| 74 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("EV"."IDSBEVENTTYPE"=197)
9 - access("EV"."IDSBEVENTCATEGORY"=242)
15 - filter(("TAKETIMESTAMP">=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TAKETIMESTAMP"<TO_DATE(' 2013-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
16 - access("SCB"."IDSBEVENT"="IDSBEVENT")
18 - filter(("WCA"."TSREQUESTED">=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "WCA"."ISACCEPTED"=0 AND "WCA"."IDSBWEBCUSTOMERACTIONTYPE"='BR' AND
"WCA"."TSREQUESTED"<TO_DATE(' 2013-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
19 - access("WCA"."IDSBEVENT"="IDSBEVENT")
Method 2 sql:
SELECT /*+ NO_PARALLEL FIRST_ROWS */
*
---------------------------------
FROM (SELECT /*+ NO_PARALLEL FIRST_ROWS */
-----------------------
scb.idsbevent AS idsbevent
FROM customerorder scb, sbevent ev
WHERE 1 = 1
AND scb.idsbevent = ev.idsbevent
AND ev.idsbeventtype = 197
AND ev.idsbeventcategory = 242
AND taketimestamp >= TO_DATE ('01/01/2013', 'dd/mm/yyyy')
AND taketimestamp < TO_DATE ('31/07/2013', 'dd/mm/yyyy') + 1
UNION ALL
SELECT /*+ NO_PARALLEL FIRST_ROWS */
-----------------------
scb.idsbevent
FROM webcustomeraction scb, sbevent ev
WHERE 1 = 1
AND scb.idsbevent = ev.idsbevent
AND ev.idsbeventtype = 197
AND ev.idsbeventcategory = 242
AND scb.tsrequested >= TO_DATE ('01/01/2013', 'dd/mm/yyyy')
AND scb.tsrequested < (TO_DATE ('31/07/2013', 'dd/mm/yyyy') + 1)) b;
Here is the execution plan for method 2 when run with a lot of data:
(obviously the timings are way differnt due to the data being cached from previous runs but this version does generally run faster)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 933K(100)| | | | 8596 |00:00:05.31 | 771K| 12906 |
| 1 | WINDOW SORT | | 1 | 495K| 365M| 407M| 933K (1)| 03:37:55 | | | 8596 |00:00:05.31 | 771K| 12906 |
| 2 | VIEW | | 1 | 495K| 365M| | 883K (1)| 03:26:09 | | | 8596 |00:00:05.28 | 771K| 12906 |
| 3 | UNION-ALL | | 1 | | | | | | | | 8596 |00:00:05.28 | 771K| 12906 |
| 4 | NESTED LOOPS | | 1 | | | | | | | | 7731 |00:00:03.77 | 385K| 9835 |
| 5 | NESTED LOOPS | | 1 | 495K| 46M| | 413K (1)| 01:36:36 | | | 7731 |00:00:03.64 | 378K| 9500 |
| 6 | NESTED LOOPS | | 1 | 26377 | 386K| | 86 (0)| 00:00:02 | | | 26377 |00:00:02.79 | 9665 | 9492 |
| 7 | FAST DUAL | | 1 | 1 | | | 2 (0)| 00:00:01 | | | 1 |00:00:00.01 | 0 | 0 |
| 8 | PARTITION RANGE ALL | | 1 | 26377 | 386K| | 86 (0)| 00:00:02 | 1 |1048575| 26377 |00:00:02.78 | 9665 | 9492 |
| 9 | TABLE ACCESS BY LOCAL INDEX ROWID| SBEVENT | 22 | 26377 | 386K| | 86 (0)| 00:00:02 | 1 |1048575| 26377 |00:00:02.77 | 9665 | 9492 |
| 10 | BITMAP CONVERSION TO ROWIDS | | 22 | | | | | | | | 26377 |00:00:00.10 | 113 | 83 |
| 11 | BITMAP AND | | 22 | | | | | | | | 18 |00:00:00.09 | 113 | 83 |
|* 12 | BITMAP INDEX SINGLE VALUE | IND_SBEVENTPT_TYPE | 22 | | | | | | 1 |1048575| 23 |00:00:00.05 | 46 | 33 |
| 13 | BITMAP CONVERSION FROM ROWIDS | | 22 | | | | | | | | 18 |00:00:00.04 | 67 | 50 |
|* 14 | INDEX RANGE SCAN | IND_SBEVENTPT_CATEGORY | 22 | 7849 | | | 52 (0)| 00:00:01 | 1 |1048575| 26379 |00:00:00.03 | 67 | 50 |
| 15 | PARTITION RANGE ITERATOR | | 26377 | 477 | | | 14 (0)| 00:00:01 | 15 | 21 | 7731 |00:00:00.83 | 369K| 8 |
|* 16 | INDEX RANGE SCAN | IDX_SBCUSTOMERBET_EVT | 184K| 477 | | | 14 (0)| 00:00:01 | 15 | 21 | 7731 |00:00:00.72 | 369K| 8 |
|* 17 | TABLE ACCESS BY LOCAL INDEX ROWID | SBCUSTOMERBET | 7731 | 19 | 1596 | | 373 (0)| 00:00:06 | 1 | 1 | 7731 |00:00:00.13 | 6657 | 335 |
| 18 | NESTED LOOPS | | 1 | | | | | | | | 865 |00:00:01.50 | 385K| 3071 |
| 19 | NESTED LOOPS | | 1 | 98 | 8918 | | 469K (1)| 01:49:34 | | | 8362 |00:00:00.59 | 378K| 60 |
| 20 | PARTITION RANGE ALL | | 1 | 26377 | 386K| | 82 (0)| 00:00:02 | 1 |1048575| 26377 |00:00:00.05 | 9664 | 0 |
| 21 | TABLE ACCESS BY LOCAL INDEX ROWID | SBEVENT | 22 | 26377 | 386K| | 82 (0)| 00:00:02 | 1 |1048575| 26377 |00:00:00.04 | 9664 | 0 |
| 22 | BITMAP CONVERSION TO ROWIDS | | 22 | | | | | | | | 26377 |00:00:00.01 | 113 | 0 |
| 23 | BITMAP AND | | 22 | | | | | | | | 18 |00:00:00.01 | 113 | 0 |
|* 24 | BITMAP INDEX SINGLE VALUE | IND_SBEVENTPT_TYPE | 22 | | | | | | 1 |1048575| 23 |00:00:00.01 | 46 | 0 |
| 25 | BITMAP CONVERSION FROM ROWIDS | | 22 | | | | | | | | 18 |00:00:00.01 | 67 | 0 |
|* 26 | INDEX RANGE SCAN | IND_SBEVENTPT_CATEGORY | 22 | 7849 | | | 53 (0)| 00:00:01 | 1 |1048575| 26379 |00:00:00.01 | 67 | 0 |
| 27 | PARTITION RANGE ITERATOR | | 26377 | 13 | | | 14 (0)| 00:00:01 | 15 | 21 | 8362 |00:00:00.53 | 369K| 60 |
|* 28 | INDEX RANGE SCAN | IF_SBWCA_SBEVENT | 184K| 13 | | | 14 (0)| 00:00:01 | 15 | 21 | 8362 |00:00:00.46 | 369K| 60 |
|* 29 | TABLE ACCESS BY LOCAL INDEX ROWID | SBWEBCUSTOMERACTION | 8362 | 1 | 76 | | 27 (0)| 00:00:01 | 1 | 1 | 865 |00:00:00.90 | 7004 | 3011 |
|* 30 | FILTER | | 1 | | | | | | | | 0 |00:00:00.01 | 0 | 0 |
| 31 | FAST DUAL | | 0 | 1 | | | 2 (0)| 00:00:01 | | | 0 |00:00:00.01 | 0 | 0 |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
12 - access("EV"."IDSBEVENTTYPE"=197)
14 - access("EV"."IDSBEVENTCATEGORY"=242)
16 - access("SCB"."IDSBEVENT"="EV"."IDSBEVENT")
17 - filter(("TAKETIMESTAMP">=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TAKETIMESTAMP"<TO_DATE(' 2013-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
24 - access("EV"."IDSBEVENTTYPE"=197)
26 - access("EV"."IDSBEVENTCATEGORY"=242)
28 - access("SCB"."IDSBEVENT"="EV"."IDSBEVENT")
29 - filter(("SCB"."TSREQUESTED">=TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "SCB"."ISACCEPTED"=0 AND "SCB"."IDSBWEBCUSTOMERACTIONTYPE"='BR' AND
"SCB"."TSREQUESTED"<TO_DATE(' 2013-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
30 - filter(NULL IS NOT NULL)
Note
-----
- cardinality feedback used for this statement
(sorry i dont know how to get the plans formatted nicely)
Please can you explain why method 1 does not use the filtered rows from the index access at step 19 when accessing the table WEBCUSTOMERACTION ie the STARTS column shows 184K at step 18 and partitions 15 to 21 accessed for method 1.
Whereas method 2 does use the filtered rows from the index read when accessing table WEBCUSTOMERACTION at step 29 and the STARTS is only 8362 and partition accessed is 1??
Is there a problem with predicate pushing in UNION ALL views?
Is there any way of forcing method 1 to behave like method 2 and only access the table the required number of times?
That is where, I believe, the performance difference lies, unless you can see another reason?
Many thanks