Do this to see what I am seeing with my tests
First, create a test table and populate it with some rows.
Code:
create table t( Group_id VARCHAR2(32),
Group_Key NUMBER,
col2 NUMBER );
Populate it with some rows.
Code:
insert into t
select object_name,
ROW_NUMBER() OVER( partition by object_name
order by object_id ),
object_id
from all_objects
where rownum<=25000;
insert into t
select Group_id || '1',
Group_Key,
col2 * -1
from t;
commit;
Create indexes on two of the columns and compute statistics.
The GROUP_ID column will be used in the PARTITION clause of the analytic function.
The COL2 column will be used to show the inefficient results.
Code:
create index IDX_T_GRPID on T(GROUP_ID);
create index IDX_T_COL2 on T(COL2);
analyze table t compute statistics;
Create a package that will be used to keep track of the number of rows processed by the analytic function. It will also be used to reject a subset of the rows processed by the analytic function.
Code:
create or replace package test_anafunc is
function test_func( x NUMBER ) return number;
function get_Count return number;
procedure reset_count;
end;
/
create or replace package body test_anafunc is
p_Count NUMBER(38);
function test_func( x NUMBER ) return number is
begin
p_Count := p_Count + 1;
if( x > 1 ) then
return 1;
end if;
return 0;
end test_func;
function get_Count return number is
begin
return p_Count;
end get_Count;
procedure reset_count is
begin
p_Count := 0;
end reset_Count;
begin
p_Count := 0;
end;
/
Define the view.
Code:
create or replace view test_view as
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 a.RNUM = 1;
Here are the actual tests.
Test 1: Query using column that is in the PARTITION clause.
You should replace the string 'TRACE' with the name of some object in your database.
The result will be to only see one row from table T for that name, the row having GROUP_KEY=2.
The execution plan will show the use of the index on the GROUP_ID column.
The TEST_ANAFUNC.GET_COUNT calls will show the number of rows processed by the analytic function. For my test case, this was 7 because I had seven rows in table T having GROUP_ID='TRACE'.
This seems to show that the predicate was pushed the way I wanted.
Code:
heath@MY_DB>set timing on
heath@MY_DB>set autotrace on explain statistics
heath@MY_DB>select * from test_View where group_id = 'TRACE'
2 /
GROUP_ID GROUP_KEY COL2
-------------------------------- ---------- ----------
TRACE 2 7942
Elapsed: 00:00:00.10
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
----------------------------------------------------------
0 recursive calls
0 db block gets
3 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
1 sorts (memory)
0 sorts (disk)
1 rows processed
heath@MY_DB>set autotrace off
heath@MY_DB>select test_anafunc.get_Count from sys.dual
2 /
GET_COUNT
----------
7
Test 2: Query using column that is NOT in the PARTITION clause.
You should replace the number 10816 with the actual object id of some object in your database that was put into table T.
The result will be to only see one row from table T for that name, the row having GROUP_KEY=2.
The execution plan will show a FULL TABLE SCAN, not the use of the index on the COL2 column.
The TEST_ANAFUNC.GET_COUNT calls will show the number of rows processed by the analytic function. For my test case, this was 49684, the total number of rows in table T.
This seems to show that the predicate was not pushed the way I wanted.
Code:
heath@MY_DB>execute test_anafunc.reset_Count
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.10
heath@MY_DB>/
GET_COUNT
----------
0
Elapsed: 00:00:00.20
heath@MY_DB>set autotrace on explain statistics
heath@MY_DB>select * from test_view where col2 = 10816
2 /
GROUP_ID GROUP_KEY COL2
-------------------------------- ---------- ----------
TABLE_X 2 10816
Elapsed: 00:00:04.97
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=497 Bytes=28329)
1 0 VIEW (Cost=40 Card=497 Bytes=28329)
2 1 WINDOW (SORT)
3 2 TABLE ACCESS (FULL) OF 'T' (Cost=36 Card=497 Bytes=13419)
Statistics
----------------------------------------------------------
8 recursive calls
11 db block gets
236 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
0 sorts (memory)
1 sorts (disk)
1 rows processed
heath@MY_DB>set autotrace off
heath@MY_DB>select test_anafunc.get_Count from sys.dual
2 /
GET_COUNT
----------
49684
Elapsed: 00:00:00.20
Contrast this to what I wanted to happen behind the scenes.
Code:
heath@MY_DB>execute test_anafunc.reset_Count
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.10
heath@MY_DB>/
GET_COUNT
----------
0
Elapsed: 00:00:00.20
heath@MY_DB>set autotrace on explain statistics
heath@MY_DB>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
-------------------------------- ---------- ----------
TABLE_X 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
----------------------------------------------------------
0 recursive calls
0 db block gets
3 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
1 sorts (memory)
0 sorts (disk)
1 rows processed
heath@MY_DB>set autotrace off
heath@MY_DB>select test_anafunc.get_Count from sys.dual
2 /
GET_COUNT
----------
1
Elapsed: 00:00:00.11