Re: Pushing predicates - Analytic functions in Views

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 24 Mar 2009 19:27:26 -0000
Message-ID: <N7-dnWXYi--GqVTUnZ2dnUVZ8sqdnZ2d_at_bt.com>

"Gokul" <gokulkumar.gopal_at_gmail.com> wrote in message news:3140ddf0-01e4-4b07-8c04-bd922c9e5b1b_at_g19g2000yql.googlegroups.com...
> One of the views used in my application has analytic functions in
> them.
> The base tables in the view are kind of big. About 102 million, 86
> million, 17 million joined.
>
> The view is not performant with the analytic function. If I remove the
> analytic function, the
> it runs fine. I have checked stats, all gathered.
>
> An example below. Is there a way out ?
>
> SQL> select * from v$version
>
> BANNER
> ----------------------------------------------------------------
> Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
> PL/SQL Release 10.2.0.3.0 - Production
> CORE 10.2.0.3.0 Production
> TNS for Solaris: Version 10.2.0.3.0 - Production
> NLSRTL Version 10.2.0.3.0 - Production
>
>
> create table t1 as select * from all_objects;
>
> create index t1_idx on t1(object_id);
>
> select * from (
> select object_id,
> object_name,
> status
> from t1
> ) where object_id = 3988;
>
>
> PLAN_TABLE_OUTPUT
> --------------------------------------------------------------------------------------
> --------------------------------------------------------------------------------------
> Plan hash value: 1720721055
>
> --------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost
> (%CPU)| Time |
> --------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 78 | 2730 | 2
> (0)| 00:00:01 |
> | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 78 | 2730 | 2
> (0)| 00:00:01 |
> |* 2 | INDEX RANGE SCAN | T1_IDX | 31 | | 1
> (0)| 00:00:01 |
> --------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 2 - access("OBJECT_ID"=3988)
>
> 14 rows selected.
>
>
> WITH ANALYTIC FUNCTION
> ----------------------
>
> select * from (
> select object_id,
> object_name,
> status,
> row_number() over (order by status) rn
> from t1
> ) where object_id = 3988
>
>
> SQL> select * from table(dbms_xplan.display);
>
> PLAN_TABLE_OUTPUT
> ------------------------------------------------------------------------------------------
> ------------------------------------------------------------------------------------------
> Plan hash value: 2602759161
>
> ------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes |TempSpc| Cost
> (%CPU)| Time |
> ------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 7842 | 367K| | 105
> (2)| 00:00:02 |
> |* 1 | VIEW | | 7842 | 367K| | 105
> (2)| 00:00:02 |
> | 2 | WINDOW SORT | | 7842 | 268K| 760K| 105
> (2)| 00:00:02 |
> | 3 | TABLE ACCESS FULL| T1 | 7842 | 268K| | 29
> (0)| 00:00:01 |
> ------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 1 - filter("OBJECT_ID"=3988)
>
> 15 rows selected.
>
> Rgds,
> Gokul

Oracle can push the predicate if pushing the predicate will never make a difference to the answer.

In your example, there is no guarantee that pushing the predicate will give you the same answer - so it can't be pushed. There will be other examples where pushing will occur because it is safe.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Received on Tue Mar 24 2009 - 14:27:26 CDT