Union Query - force hash unique for each query

Viewed 129

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;
0 Answers
Related