With standard edition, I would look at using "partition views", which was the predecessor to partitioning. It takes more effort to setup and maintain, but the optimizer is smart enough to avoid tables it does not need to
SQL> create table t1 as select * from dba_objects where object_id between 0 and 20000;
Table created.
SQL> create table t2 as select * from dba_objects where object_id between 20001 and 40000;
Table created.
SQL> create table t3 as select * from dba_objects where object_id between 40001 and 60000;
Table created.
SQL>
SQL> alter table t1 add constraint ck1 check (object_id between 0 and 20000);
Table altered.
SQL> alter table t2 add constraint ck2 check (object_id between 20001 and 40000);
Table altered.
SQL> alter table t3 add constraint ck3 check (object_id between 40001 and 60000);
Table altered.
SQL>
SQL> create or replace view t as
2 select * from t1
3 union all
4 select * from t2
5 union all
6 select * from t3
7 ;
View created.
SQL>
SQL> set autotrace traceonly explain
SQL> select * from t
2 where object_id < 30;
Execution Plan
----------------------------------------------------------
Plan hash value: 3867803903
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 28 | 7224 | 331 (1)| 00:00:01 |
| 1 | VIEW | T | 30 | 3668 | 101 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | TABLE ACCESS FULL | T1 | 28 | 3388 | 101 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
|* 5 | TABLE ACCESS FULL| T2 | 1 | 140 | 115 (0)| 00:00:01 |
|* 6 | FILTER | | | | | |
|* 7 | TABLE ACCESS FULL| T3 | 1 | 140 | 117 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("OBJECT_ID"<30)
4 - filter(NULL IS NOT NULL)
5 - filter("OBJECT_ID"<30)
6 - filter(NULL IS NOT NULL)
7 - filter("OBJECT_ID"<30)
You can see from the FILTER lines 4 and 6, then we never actually visit tables T2 and T3 because they cannot possibly contain rows for that object_id range.