I need the list of unique values for group_name across tables T1 and T2. Both tables are partitioned and contains hundreds of millions of records. The column that I'm interested in has a local bitmap index with up-to-date statistics. There are roughly 500 unique values for this column. (See test case).
Given this information, the most efficient way to find the unique values would appear to be: Find the 500 or so unique values from T1, then find the 500 or so unique values from T2, and then deduplicate the list. So that translates into this query:
select distinct group_name from t1 union
select distinct group_name from t2;
However, the actual execution generated by Oracle is something like this:
SELECT
SORT UNIQUE <-- 500 records
UNION-ALL <-- 1,430,000,000 records
BITMAP INDEX FFS T1 <-- 1,300,000,000 records
BITMAP INDEX FFS T2 <-- 130,000,000 records
So the optimizer seems to have re-written the query to something like the below query, effectively skipping the unique operation from the intermediate steps:
select distinct group_name
from (select group_name from t1 union all -- No Unique here
select group_name from t2)
Here is my actual question: Without re-writing the query, can I force the following execution plan using hints only? i.e the way my original query was actually written?
SELECT
SORT UNIQUE <-- 500
UNION-ALL <-- 1000
HASH UNIQUE <-- 500 <-- Reduce early
BITMAP INDEX FFS T1 <-- 1,300,000,000
HASH UNIQUE <-- 500 <-- Reduce early
BITMAP INDEX FFS T2 <-- 130,000,000
Here is a test case that creates the two tables above.
create table t1(id number, group_name number, payload varchar2(100)) nologging partition by hash(id) partitions 4;
create table t2(id number, group_name number, payload varchar2(100)) nologging partition by hash(id) partitions 4;
insert /*+ append */ all
into t1
into t2
select rownum as id
,mod(rownum, 500) as group_name
,lpad('x', 100, 'x') as payload
from dual connect by level <= 1e6;
create bitmap index t1_bx on t1(group_name) nologging local;
create bitmap index t2_bx on t2(group_name) nologging local;
I'm using Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Edit:
Incidentally, while preparing the test case I found two ways around the problem, but I would still like to know if there is a way to /*+ hint */ my way out of the problem.
select group_name from t1 group by group_name union
select group_name from t2 group by group_name;
with v1 as(select /*+ materialize */distinct group_name from t1)
,v2 as(select /*+ materialize */distinct group_name from t2)
select group_name from v1 union
select group_name from v2;