SQL & PL/SQL

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