Pushing predicates - Analytic functions in Views

From: Gokul <gokulkumar.gopal_at_gmail.com>
Date: Tue, 24 Mar 2009 12:21:20 -0700 (PDT)
Message-ID: <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 Received on Tue Mar 24 2009 - 14:21:20 CDT