Skip to Main Content
  • Questions
  • Predicate pushing in views containing analytic functions

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Heath.

Asked: April 19, 2002 - 1:48 pm UTC

Last updated: January 15, 2013 - 8:40 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Tom,

I have a view in my database that contains an Analytic function. The analytic function windows on a set of rows in the table and returns a subset of them. The initial testing using this view was quite promising, as the work performed by the database to get the answer was MUCH less using the analytic function than joining the table back to itself (the approach used previously). My initial tests were queries against the view based upon the same column that was used to partition the table in the window. However, when I changed the query against the view to use another column in the WHERE clause, the performance dropped significantly. My investigation showed that the predicate was not being pushed into the view, resulting in the whole table being read, partitioned, and sorted before the predicate was applied.

Is there no way to force the predicate all the way into the view? I tried using hints like PUSH_PRED, but couldn't get it to work. Or is there another way to construct the view to get the answers I want?

Many thanks for your help,

Heath

Here is a long snippet from an SQL*Plus session detailing the investigation.



SQL>drop table t
2 /

Table dropped.

Elapsed: 00:00:00.41
SQL>
SQL>create table t( Group_id VARCHAR2(32),
2 Group_Key NUMBER,
3 col2 NUMBER )
4 /

Table created.

Elapsed: 00:00:00.40
SQL>insert into t
2 select object_name,
3 ROW_NUMBER() OVER( partition by object_name
4 order by object_id ),
5 object_id
6 from all_objects
7 where rownum<=25000
8 /

24862 rows created.

Elapsed: 00:00:10.05
SQL>
SQL>insert into t
2 select Group_id || '1',
3 Group_Key,
4 col2 * -1
5 from t
6 /

24862 rows created.

Elapsed: 00:00:02.03
SQL>
SQL>commit
2 /

Commit complete.

Elapsed: 00:00:00.90
SQL>
SQL>-- Create indexes on 2 of the columns.
SQL>-- One on the column used for the analytic function partition
SQL>-- One on another column in the table
SQL>create index IDX_T_GRPID on T(GROUP_ID)
2 /

Index created.

Elapsed: 00:00:08.63
SQL>
SQL>create index IDX_T_COL2 on T(COL2)
2 /

Index created.

Elapsed: 00:00:09.05
SQL>
SQL>-- Compute statistics for the CBO
SQL>analyze table t compute statistics
2 /

Table analyzed.

Elapsed: 00:00:14.51
SQL>-- Create a package that will be used to keep track of the
SQL>-- number of rows processed by the analytic function.
SQL>-- It also will be used to reject some of the rows
SQL>-- processed by the analytic function.
SQL>create or replace package test_anafunc is
2 function test_func( x NUMBER ) return number;
3
4 function get_Count return number;
5 procedure reset_count;
6 end;
7 /

Package created.

Elapsed: 00:00:00.70
SQL>
SQL>create or replace package body test_anafunc is
2
3 p_Count NUMBER(38);
4
5 function test_func( x NUMBER ) return number is
6 begin
7 p_Count := p_Count + 1;
8
9 if( x > 1 ) then
10 return 1;
11 end if;
12
13 return 0;
14 end test_func;
15
16 function get_Count return number is
17 begin
18 return p_Count;
19 end get_Count;
20
21 procedure reset_count is
22 begin
23 p_Count := 0;
24 end reset_Count;
25
26 begin
27 p_Count := 0;
28 end;
29 /

Package body created.

Elapsed: 00:00:00.70
SQL>
SQL>-- Create a view containing the analytic function.
SQL>-- This simulates the view that would be exposed by an application
SQL>-- to the user.
SQL>create or replace view test_view as
2 select a.group_id, a.group_key, a.col2
3 from (select t.group_id, t.group_key, t.col2,
4 ROW_NUMBER() OVER( PARTITION BY GROUP_ID
5 ORDER BY GROUP_KEY ASC NULLS LAST ) RNUM
6 from t
7 where test_anafunc.test_func(GROUP_KEY) = 1 ) a
8 where a.RNUM = 1
9 /

View created.

Elapsed: 00:00:00.90
SQL>-- Let's see how queries against this view get executed.
SQL>
SQL>-- Query the view based upon a group_id.
SQL>-- Pick one from table t that has more than one row for it.
SQL>-- You should see the second row for the group (Group_key=2).
SQL>-- The execution plan should show the use of the index.
SQL>-- The test_anafunc.get_Count should return the number of
SQL>-- rows for that group_id value.
SQL>-- This shows that the predicate is pushed into the inline view.
SQL>execute test_anafunc.reset_Count

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10
SQL>/

View created.

Elapsed: 00:00:00.50
SQL>set autotrace on explain statistics
SQL>select * from test_View where group_id = 'TRACE'
2 /

GROUP_ID GROUP_KEY COL2
-------------------------------- ---------- ----------
TRACE 2 7942

Elapsed: 00:00:00.21

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=57)
1 0 VIEW (Cost=5 Card=1 Bytes=57)
2 1 WINDOW (SORT)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3 Card=1 Bytes=27)
4 3 INDEX (RANGE SCAN) OF 'IDX_T_GRPID' (NON-UNIQUE) (Cost=1 Card=1)




Statistics
----------------------------------------------------------
42 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
486 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>set autotrace off
SQL>select test_anafunc.get_Count from sys.dual
2 /

GET_COUNT
----------
7

Elapsed: 00:00:00.20
SQL>-- Query based upon a column other than the one used for the partition.
SQL>-- Pick an object_id that exists in table t in the col2 column.
SQL>-- You should get one row back.
SQL>-- However, the execution plan will not show the use of the index. You'll
SQL>-- see a full table scan instead.
SQL>-- The test_anafunc.get_Count will return a total equal to the total
SQL>-- number of rows.
SQL>-- This shows that the predicate is NOT pushed into the inline view
SQL>-- and that we are processing the entire table instead of just those
SQL>-- rows that meet the where clause criteria.
SQL>
SQL>execute test_anafunc.reset_Count

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10
SQL>/

GET_COUNT
----------
0

Elapsed: 00:00:00.10
SQL>set autotrace on explain statistics
SQL>select * from test_view where col2 = 10816
2 /

GROUP_ID GROUP_KEY COL2
-------------------------------- ---------- ----------
ADDRESSLOCATION_SDOGEOM 2 10816

Elapsed: 00:00:05.58

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=498 Bytes=28386)
1 0 VIEW (Cost=40 Card=498 Bytes=28386)
2 1 WINDOW (SORT)
3 2 TABLE ACCESS (FULL) OF 'T' (Cost=36 Card=498 Bytes=13446)




Statistics
----------------------------------------------------------
50 recursive calls
11 db block gets
242 consistent gets
251 physical reads
0 redo size
505 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
1 sorts (disk)
1 rows processed

SQL>set autotrace off
SQL>select test_anafunc.get_Count from sys.dual
2 /

GET_COUNT
----------
49724

Elapsed: 00:00:00.10
SQL>
SQL>-- Contrast this to what I really wanted for the col2 query.
SQL>execute test_anafunc.reset_Count

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10
SQL>/

GET_COUNT
----------
0

Elapsed: 00:00:00.10
SQL>set autotrace on explain statistics
SQL>select a.group_id, a.group_key, a.col2
2 from (select t.group_id, t.group_key, t.col2,
3 ROW_NUMBER() OVER( PARTITION BY GROUP_ID
4 ORDER BY GROUP_KEY ASC NULLS LAST ) RNUM
5 from t
6 where test_anafunc.test_func(GROUP_KEY) = 1
7 and col2 = 10816 ) a
8 where a.RNUM = 1
9 /

GROUP_ID GROUP_KEY COL2
-------------------------------- ---------- ----------
ADDRESSLOCATION_SDOGEOM 2 10816

Elapsed: 00:00:00.11

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=57)
1 0 VIEW (Cost=4 Card=1 Bytes=57)
2 1 WINDOW (SORT)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=27)
4 3 INDEX (RANGE SCAN) OF 'IDX_T_COL2' (NON-UNIQUE) (Cost=1 Card=1)




Statistics
----------------------------------------------------------
35 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
505 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>set autotrace off
SQL>select test_anafunc.get_Count from sys.dual
2 /

GET_COUNT
----------
1

Elapsed: 00:00:00.10



and we said...

You cannot -- it CHANGES the answer. You think your two queries are equivalent, they are NOT.

There is a HUGE difference between


select analytic function
from t
where <condition>


and

select *
from ( select analytic function
from t )
where <condition>


They are NOT even a tiny bit COMPARABLE. Not at all.


This query says:

SQL>select a.group_id, a.group_key, a.col2
2 from (select t.group_id, t.group_key, t.col2,
3 ROW_NUMBER() OVER( PARTITION BY GROUP_ID
4 ORDER BY GROUP_KEY ASC NULLS LAST ) RNUM
5 from t
6 where test_anafunc.test_func(GROUP_KEY) = 1
7 and col2 = 10816 ) a
8 where a.RNUM = 1
9 /


Take the rows from T -- skipping group key 1 (test_func does that) where col23 = 10816.

Then, for all of those rows -- partition by group_id and sort by the group_key -- assign row_number.

Then, keep the first one.

this query:


SQL>
select *
from (select a.group_id, a.group_key, a.col2
from (select t.group_id, t.group_key, t.col2,
ROW_NUMBER() OVER( PARTITION BY GROUP_ID
ORDER BY GROUP_KEY ASC NULLS LAST ) RNUM
from t
where test_anafunc.test_func(GROUP_KEY) = 1 ) a
where col2 = 10816
and a.RNUM = 1
/

says:

find all of the rows after skipping group key 1.
partition that that by group id.
order by group_key and assign row_number.


Now, after that is done, keep only the first row and only keep that IF it has col2 = 10816.


Here is proof they are different -- their logic is TOTALLY and completely and UTTERLY different:

ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table t
2 /

Table dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t( Group_id VARCHAR2(32),
2 Group_Key NUMBER,
3 col2 NUMBER )
4 /

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 'x', 2, 10815 );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 'x', 3, 10816 );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace view test_view as
2 select a.group_id, a.group_key, a.col2
3 from (select t.group_id, t.group_key, t.col2,
4 ROW_NUMBER() OVER( PARTITION BY GROUP_ID
5 ORDER BY GROUP_KEY ASC NULLS LAST ) RNUM
6 from t
7 where test_anafunc.test_func(GROUP_KEY) = 1 ) a
8 where a.RNUM = 1
9 /

View created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from test_view where col2 = 10816
2 /

no rows selected

ops$tkyte@ORA817DEV.US.ORACLE.COM> select *
2 from (
3 select a.group_id, a.group_key, a.col2
4 from (select t.group_id, t.group_key, t.col2,
5 ROW_NUMBER() OVER( PARTITION BY GROUP_ID
6 ORDER BY GROUP_KEY ASC NULLS LAST ) RNUM
7 from t
8 where test_anafunc.test_func(GROUP_KEY) = 1 ) a
9 where a.RNUM = 1
10 )
11 where col2 = 10816
12 /

no rows selected

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.group_id, a.group_key, a.col2
2 from (select t.group_id, t.group_key, t.col2,
3 ROW_NUMBER() OVER( PARTITION BY GROUP_ID
4 ORDER BY GROUP_KEY ASC NULLS LAST ) RNUM
5 from t
6 where test_anafunc.test_func(GROUP_KEY) = 1
7 and col2 = 10816 ) a
8 where a.RNUM = 1
9 /

GROUP_ID GROUP_KEY COL2
-------------------------------- ---------- ----------
x 3 10816


Whether I query the view or a query that has the view inlined -- no data (by definition -- the query shouldn't get any)

Do it your way -- and you get a row. But that is due to the fact that its a wholly different question!

Rating

  (64 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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

Tom Kyte
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.


Tom Kyte
December 04, 2002 - 12:57 pm UTC

(how many people saw this response coming ;)

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96524/c12parti.htm#464767 <code>

The concepts guide has a pretty good writeup on this...

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?

Tom Kyte
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

Tom Kyte
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

Tom Kyte
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?

Tom Kyte
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


Tom Kyte
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


Tom Kyte
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 ?


Tom Kyte
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.



Tom Kyte
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



Tom Kyte
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

Tom Kyte
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



Tom Kyte
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?

Tom Kyte
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?

Tom Kyte
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?

Tom Kyte
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 :-?

Tom Kyte
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?

Tom Kyte
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".

Tom Kyte
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



Tom Kyte
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

Tom Kyte
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.

Tom Kyte
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

Tom Kyte
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

Tom Kyte
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!]

Tom Kyte
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

Tom Kyte
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

Tom Kyte
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.

 

Tom Kyte
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'))

Tom Kyte
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


 

Tom Kyte
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?



Tom Kyte
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
Tom Kyte
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.

Tom Kyte
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
Tom Kyte
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        

Tom Kyte
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> 

Tom Kyte
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
Tom Kyte
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 = 1112

SQL> 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.

Tom Kyte
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



Tom Kyte
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.
Tom Kyte
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!

Tom Kyte
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
Tom Kyte
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

Hi Tom,
Can you please suggest a document which describes ALL the Query transformations by the Optimizer other than this :
http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/optimops.htm#g92116 ?


Tom Kyte
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


Tom Kyte
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?
Tom Kyte
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>
Tom Kyte
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
Tom Kyte
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!
Tom Kyte
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,
Tom Kyte
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

Tom Kyte
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


More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.