I've got a client who won't pay for real partitioning, but has a single monster table on a data warehouse that needs to be broken into pieces for optimal performance of queries.
What I would like to do is manually partition the table, create a union all join view that sits on top, then have oracle prune the execution plans of queries to allow me to only hit the tables I need to to fulfill the result set.
-- Create table
create table a_tbl
(
col1 varchar2(20),
col2 varchar2(20),
col3 varchar2(20),
col4 varchar2(20)
)
tablespace AAA_DATA;
-- Create/Recreate check constraints
alter table a_tbl
add constraint a_tbl_restrict
check (col1='aaa');
create table b_tbl
(
col1 varchar2(20),
col2 varchar2(20),
col3 varchar2(20),
col4 varchar2(20)
)
tablespace AAA_DATA;
-- Create/Recreate check constraints
alter table b_tbl
add constraint b_tbl_restrict
check (col1='bbb');
create table c_tbl
(
col1 varchar2(20),
col2 varchar2(20),
col3 varchar2(20),
col4 varchar2(20)
)
tablespace AAA_DATA;
-- Create/Recreate check constraints
alter table c_tbl
add constraint c_tbl_restrict
check (col1='ccc');
alter table A_TBL
add constraint a_tbl_pk primary key (COL1, COL2)
using index
tablespace AAA_INDEX;
alter table B_TBL
add constraint b_tbl_pk primary key (COL1, COL2)
using index
tablespace AAA_INDEX;
alter table C_TBL
add constraint c_tbl_pk primary key (COL1, COL2)
using index
tablespace AAA_INDEX;
create view abc_v as
select * from a_tbl
union all
select * from b_tbl
union all
select * from c_tbl;
When I execute a query against the view that should eliminate two of the tables, I don't see the pruning happening.
select * from abc_v
where col1 = 'aaa' and col2 < '100'
order by col2
SELECT STATEMENT, GOAL = ALL_ROWS 3 1 48
SORT ORDER BY 3 1 48
VIEW TAS ABC_V 2 1 48
UNION-ALL
TABLE ACCESS BY INDEX ROWID TAS A_TBL 4 2 96
INDEX RANGE SCAN TAS A_TBL_PK 3 2
FILTER
TABLE ACCESS BY INDEX ROWID TAS B_TBL 2 1 48
INDEX RANGE SCAN TAS B_TBL_PK 2 1
FILTER
TABLE ACCESS BY INDEX ROWID TAS C_TBL 2 1 48
INDEX RANGE SCAN TAS C_TBL_PK 2 1
Am I doing something that just isn't possible without "true" partitioning? Or did I set it up wrong in some way?
Thanks!
Cory Aston