3

The CBO chooses to eliminate the distinct in the 'slow' query - presumably it can tell that the operation isn't needed because of the outer group by.

My questions are:

1) Why does it choose to do so in this case - I can't see a reason from the costings and predicted cardinalities in the plans

2) If it chooses to eliminate the distinct, why not apply the same logic and eliminate the group by?

testbed:

create table t1 as
select rownum product_id, mod(rownum,3)+1 company_id
from dual
connect by rownum<=500;

create table t2 as
select t1.product_id from t1 t1 cross join t1 t12;

create table t3 as
select distinct company_id from t1;

analyze table t1 compute statistics;
analyze table t2 compute statistics;
analyze table t3 compute statistics;

fast (55ms):

select company_id
from t1 
     join t2 using(product_id) 
     join ( select company_id
           from (select company_id from t1 group by company_id) 
                join t3 using(company_id) ) using(company_id)
group by company_id;

slow (5240ms):

select company_id
from t1 
     join t2 using(product_id)
     join ( select company_id 
            from (select distinct company_id from t1) 
                 join t3 using(company_id) ) using(company_id)
group by company_id;

SQLFiddle here.

  • Unrelated, but: you shouldn't be using analyze table any more. The recommended way is to use dbms_stats.gather_table_stats() because that will create different (better?) statistics. – a_horse_with_no_name May 13 '15 at 10:39
3

Output is from a 11.2.0.4.6 Enterprise Edition database on Oracle Linux 7.1 x86-64 platform.

Lets start with question 2 and an easy example.

DISTINCT and GROUP BY are handled differently: the optimizer is able to completely eliminate a DISTINCT under certain circumstances, but it can not do the same with GROUP BY. Here is an example:

create table t4 as
select rownum product_id
from dual
connect by rownum<=5;

exec dbms_stats.gather_table_stats(user, 'T4');

alter session set statistics_level=all;

Note that setting statistics_level to ALL significantly increases the execution time for the original queries in the question.

The 2 queries will be:

select distinct product_id from t4

Plan hash value: 641655586

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     4 (100)|      5 |00:00:00.01 |       |       |          |
|   1 |  HASH UNIQUE       |      |      1 |      5 |     4  (25)|      5 |00:00:00.01 |  2441K|  2441K| 1503K (0)|
|   2 |   TABLE ACCESS FULL| T4   |      1 |      5 |     3   (0)|      5 |00:00:00.01 |       |       |          |
-------------------------------------------------------------------------------------------------------------------

select product_id from t4 group by product_id

Plan hash value: 581042373

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     4 (100)|      5 |00:00:00.01 |       |       |          |
|   1 |  HASH GROUP BY     |      |      1 |      5 |     4  (25)|      5 |00:00:00.01 |  2441K|  2441K|  863K (0)|
|   2 |   TABLE ACCESS FULL| T4   |      1 |      5 |     3   (0)|      5 |00:00:00.01 |       |       |          |
-------------------------------------------------------------------------------------------------------------------

This is what we expect. Full table scan and HASH UNIQUE for DISTINCT, HASH GROUP BY for GROUP BY. Now add a NOT NULL constraint and an index.

create index t4_i1 on t4(product_id);
alter table t4 modify (product_id not null);

select distinct product_id from t4

Plan hash value: 4231414870

-----------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |     2 (100)|      5 |00:00:00.01 |
|   1 |  SORT UNIQUE NOSORT|       |      1 |      5 |     2  (50)|      5 |00:00:00.01 |
|   2 |   INDEX FULL SCAN  | T4_I1 |      1 |      5 |     1   (0)|      5 |00:00:00.01 |
-----------------------------------------------------------------------------------------

select product_id from t4 group by product_id

Plan hash value: 1989519822

-------------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |      1 |        |     1 (100)|      5 |00:00:00.01 |
|   1 |  SORT GROUP BY NOSORT|       |      1 |      5 |     1   (0)|      5 |00:00:00.01 |
|   2 |   INDEX FULL SCAN    | T4_I1 |      1 |      5 |     1   (0)|      5 |00:00:00.01 |
-------------------------------------------------------------------------------------------

The optimizer noticed the index on the relevant column, and because of the NOT NULL constraint it is able to use it avoid sorting the data for producing the unique values, because the data is already sorted in the index. Now add a UNIQUE constraint to this column:

alter table t4 add constraint t4_u1 unique (product_id) using index t4_i1;

select distinct product_id from t4

Plan hash value: 3974767428

---------------------------------------------------------------------------------------
| Id  | Operation        | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |      1 |        |     1 (100)|      5 |00:00:00.01 |
|   1 |  INDEX FULL SCAN | T4_I1 |      1 |      5 |     1   (0)|      5 |00:00:00.01 |
---------------------------------------------------------------------------------------

select product_id from t4 group by product_id

Plan hash value: 1989519822

-------------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |      1 |        |     1 (100)|      5 |00:00:00.01 |
|   1 |  SORT GROUP BY NOSORT|       |      1 |      5 |     1   (0)|      5 |00:00:00.01 |
|   2 |   INDEX FULL SCAN    | T4_I1 |      1 |      5 |     1   (0)|      5 |00:00:00.01 |
-------------------------------------------------------------------------------------------

Notice how Oracle completely skipped SORT/HASH UNIQUE when using DISTINCT, but nothing changed with the GROUP BY query.

Enable tracing the optimizer when running the DISTINCT version:

alter system flush shared_pool;
alter session set events '10053 trace name context forever, level 1';
select distinct product_id from t4;

In the trace file we can see the following:

**************************
Query transformations (QT)
**************************
...
Eliminated SELECT DISTINCT from query block SEL$1 (#0)
...
********************************
COST-BASED QUERY TRANSFORMATIONS
********************************
...
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T4"."PRODUCT_ID" "PRODUCT_ID" FROM "BP"."T4" "T4"
...

Note that this is a Query Transformation, but NOT a Cost-based Query Transformation. As you can see from the "Final query" part, the optimizer removed DISTINCT from the query. But there is no such optimization for GROUP BY. DISTINCT is used for retrieving distinct values, but GROUP BY is used for producing aggregates, not just distinct values. Even if the optimizer can skip sorting or hashing the data, it can not skip counting, adding, calculating the average, etc, and this is the important difference, so DISTINCT and GROUP BY are not handled in the same way (even if aggregates are not specified).

Another case of eliminating the DISTINCT part, when it is obviously unnecessary, for example:

alter session set "_complex_view_merging"=false;

This was set to prevent Complex View Merging Transformations like DISTINCT Placement, GROUP BY Placement, Subquery Unnesting appearing and making things complicated.

select distinct product_id from (select distinct product_id from (select distinct product_id from (select distinct product_id from t4)));

Plan hash value: 3974767428

---------------------------------------------------------------------------------------
| Id  | Operation        | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |      1 |        |     1 (100)|      5 |00:00:00.01 |
|   1 |  INDEX FULL SCAN | T4_I1 |      1 |      5 |     1   (0)|      5 |00:00:00.01 |
---------------------------------------------------------------------------------------

select product_id from (select distinct product_id from (select product_id from (select distinct product_id from t4 ) group by product_id)) group by product_id;

Plan hash value: 4029011489

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |      1 |        |     1 (100)|      5 |00:00:00.01 |       |       |          |
|   1 |  HASH GROUP BY         |       |      1 |      5 |     1   (0)|      5 |00:00:00.01 |  2441K|  2441K|  862K (0)|
|   2 |   VIEW                 |       |      1 |      5 |     1   (0)|      5 |00:00:00.01 |       |       |          |
|   3 |    SORT GROUP BY NOSORT|       |      1 |      5 |     1   (0)|      5 |00:00:00.01 |       |       |          |
|   4 |     INDEX FULL SCAN    | T4_I1 |      1 |      5 |     1   (0)|      5 |00:00:00.01 |       |       |          |
------------------------------------------------------------------------------------------------------------------------

select product_id from (select product_id from (select product_id from (select product_id from t4 group by product_id) group by product_id) group by product_id) group by product_id;

Plan hash value: 1970696362

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |      1 |        |     1 (100)|      5 |00:00:00.01 |       |       |          |
|   1 |  HASH GROUP BY         |       |      1 |      5 |     1   (0)|      5 |00:00:00.01 |  2441K|  2441K|  883K (0)|
|   2 |   VIEW                 |       |      1 |      5 |     1   (0)|      5 |00:00:00.01 |       |       |          |
|   3 |    HASH GROUP BY       |       |      1 |      5 |     1   (0)|      5 |00:00:00.01 |  2441K|  2441K|  848K (0)|
|   4 |     VIEW               |       |      1 |      5 |     1   (0)|      5 |00:00:00.01 |       |       |          |
|   5 |      HASH GROUP BY     |       |      1 |      5 |     1   (0)|      5 |00:00:00.01 |  2441K|  2441K|  840K (0)|
|   6 |       VIEW             |       |      1 |      5 |     1   (0)|      5 |00:00:00.01 |       |       |          |
|   7 |        HASH GROUP BY   |       |      1 |      5 |     1   (0)|      5 |00:00:00.01 |  2441K|  2441K|  848K (0)|
|   8 |         INDEX FULL SCAN| T4_I1 |      1 |      5 |     1   (0)|      5 |00:00:00.01 |       |       |          |
------------------------------------------------------------------------------------------------------------------------

Notice how the optimizer completetely eliminated the DISTINCTs in the first and second queries, but not the GROUP BYs.

Unfortunately in these cases, since the DISTINCT elimination happens in subqueries ("views"), this information is not present in the optimizer trace, just like for the original queries in the question.

So now we know that DISTINCT and GROUP BY are handled indeed differently, lets go back to question 1.

To be continued in the next post...

(Both answers together exceed the 30000 characters limit.)

1

Continued from previous post.

So now we know that DISTINCT and GROUP BY are handled indeed differently, lets go back to question 1.

Knowing the above, going back to the original, slower query, this:

select company_id
from t1 
     join t2 using(product_id)
     join ( select company_id 
            from (select distinct company_id from t1) 
                 join t3 using(company_id) ) using(company_id)
group by company_id;

is equivalent to this (DISTINCT removed):

select company_id
from t1 
     join t2 using(product_id)
     join ( select company_id 
            from (select company_id from t1) 
                 join t3 using(company_id) ) using(company_id)
group by company_id;

You can check this by enabling the optimizer trace for both queries, here is the essence from the 2 runs:

select company_id
from t1 
     join t2 using(product_id)
     join ( select company_id 
            from (select distinct company_id from t1) 
                 join t3 using(company_id) ) using(company_id)
group by company_id;

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T3"."COMPANY_ID" "COMPANY_ID" FROM "BP"."T1" "T1","BP"."T2" "T2","BP"."T1" "T1","BP"."T3" "T3" WHERE "T1"."COMPANY_ID"="T3"."COMPANY_ID" AND "T1"."COMPANY_ID"="T3"."COMPANY_ID" AND "T1"."PRODUCT_ID"="T2"."PRODUCT_ID" GROUP BY "T3"."COMPANY_ID"

Plan hash value: 1403596148

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |      1 |        |  1242 (100)|      3 |00:02:13.42 |     390 |       |       |          |
|   1 |  HASH GROUP BY        |      |      1 |      3 |  1242  (91)|      3 |00:02:13.42 |     390 |  1610M|    50M| 1229K (0)|
|*  2 |   HASH JOIN           |      |      1 |     41M|   210  (44)|     41M|00:01:07.27 |     390 |  2440K|  2440K|  956K (0)|
|   3 |    TABLE ACCESS FULL  | T1   |      1 |    500 |     3   (0)|    500 |00:00:00.01 |       2 |       |       |          |
|*  4 |    HASH JOIN          |      |      1 |    250K|   116   (1)|    250K|00:00:02.05 |     388 |  1969K|  1969K| 1581K (0)|
|*  5 |     HASH JOIN         |      |      1 |    500 |     6   (0)|    500 |00:00:00.01 |       4 |  2440K|  2440K|  980K (0)|
|   6 |      TABLE ACCESS FULL| T3   |      1 |      3 |     3   (0)|      3 |00:00:00.01 |       2 |       |       |          |
|   7 |      TABLE ACCESS FULL| T1   |      1 |    500 |     3   (0)|    500 |00:00:00.01 |       2 |       |       |          |
|   8 |     TABLE ACCESS FULL | T2   |      1 |    250K|   110   (1)|    250K|00:00:00.43 |     384 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("COMPANY_ID"="T3"."COMPANY_ID")
   4 - access("T1"."PRODUCT_ID"="T2"."PRODUCT_ID")
   5 - access("T1"."COMPANY_ID"="T3"."COMPANY_ID")

select company_id
from t1 
     join t2 using(product_id)
     join ( select company_id 
            from (select company_id from t1) 
                 join t3 using(company_id) ) using(company_id)
group by company_id;

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T3"."COMPANY_ID" "COMPANY_ID" FROM "BP"."T1" "T1","BP"."T2" "T2","BP"."T1" "T1","BP"."T3" "T3" WHERE "T1"."COMPANY_ID"="T3"."COMPANY_ID" AND "T1"."COMPANY_ID"="T3"."COMPANY_ID" AND "T1"."PRODUCT_ID"="T2"."PRODUCT_ID" GROUP BY "T3"."COMPANY_ID"

Plan hash value: 1403596148

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |      1 |        |  1242 (100)|      3 |00:02:12.94 |     390 |       |       |          |
|   1 |  HASH GROUP BY        |      |      1 |      3 |  1242  (91)|      3 |00:02:12.94 |     390 |  1610M|    50M| 1254K (0)|
|*  2 |   HASH JOIN           |      |      1 |     41M|   210  (44)|     41M|00:01:07.23 |     390 |  2440K|  2440K| 1025K (0)|
|   3 |    TABLE ACCESS FULL  | T1   |      1 |    500 |     3   (0)|    500 |00:00:00.01 |       2 |       |       |          |
|*  4 |    HASH JOIN          |      |      1 |    250K|   116   (1)|    250K|00:00:02.04 |     388 |  1969K|  1969K| 1581K (0)|
|*  5 |     HASH JOIN         |      |      1 |    500 |     6   (0)|    500 |00:00:00.01 |       4 |  2440K|  2440K|  983K (0)|
|   6 |      TABLE ACCESS FULL| T3   |      1 |      3 |     3   (0)|      3 |00:00:00.01 |       2 |       |       |          |
|   7 |      TABLE ACCESS FULL| T1   |      1 |    500 |     3   (0)|    500 |00:00:00.01 |       2 |       |       |          |
|   8 |     TABLE ACCESS FULL | T2   |      1 |    250K|   110   (1)|    250K|00:00:00.44 |     384 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("COMPANY_ID"="T3"."COMPANY_ID")
   4 - access("T1"."PRODUCT_ID"="T2"."PRODUCT_ID")
   5 - access("T1"."COMPANY_ID"="T3"."COMPANY_ID")

As you can see, the final form ("Final query") of 2 queries are the same, their execution plans are also the same. The queries were running for 2 minutes because statistics_level was set to ALL in order to gather plan statistics.

So knowing that the distinct can be eliminated from the query, look at the text of this query:

select company_id
from t1 
     join t2 using(product_id)
     join ( select company_id 
            from (select company_id from t1) 
                 join t3 using(company_id) ) using(company_id)
group by company_id;

There is a subquery ("view" from now on) in this:

  ( select company_id 
    from (select company_id from t1) 
         join t3 using(company_id) ) using(company_id)

The inner view originally contained DISTINCT, and because of that, it was eligible for Complex View Merging. However, as the DISTINCT was eliminated earlier by another query transformation, this view became a simple SPJ (select-project-join) view, and now is eligible for Simple View Merging. Here are some interesting information about view merging:

https://docs.oracle.com/database/121/TGSQL/tgsql_transform.htm#TGSQL209

This part is especially important in this case:

"For certain simple views in which merging always leads to a better plan, the optimizer automatically merges the view without considering cost."

So the as you can see above, the cost of the plan with view merging is 1242. Lets see the cost with view merging disabled:

SQL> set timing on
SQL> alter session set statistics_level=typical;

select company_id
from t1 
     join t2 using(product_id)
     join ( select company_id 
            from (select /*+ no_merge */ distinct company_id from t1) 
                 join t3 using(company_id) ) using(company_id)
group by company_id;

COMPANY_ID
----------
         1
         2
         3

Elapsed: 00:00:00.03

Plan hash value: 3348277023

--------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |       |       |   126 (100)|          |
|   1 |  HASH GROUP BY          |      |     3 |    51 |   126   (7)| 00:00:02 |
|*  2 |   HASH JOIN             |      |   250K|  4150K|   120   (2)| 00:00:02 |
|*  3 |    HASH JOIN            |      |   500 |  6500 |    10  (10)| 00:00:01 |
|*  4 |     HASH JOIN           |      |     3 |    18 |     7  (15)| 00:00:01 |
|   5 |      VIEW               |      |     3 |     9 |     4  (25)| 00:00:01 |
|   6 |       HASH UNIQUE       |      |     3 |     9 |     4  (25)| 00:00:01 |
|   7 |        TABLE ACCESS FULL| T1   |   500 |  1500 |     3   (0)| 00:00:01 |
|   8 |      TABLE ACCESS FULL  | T3   |     3 |     9 |     3   (0)| 00:00:01 |
|   9 |     TABLE ACCESS FULL   | T1   |   500 |  3500 |     3   (0)| 00:00:01 |
|  10 |    TABLE ACCESS FULL    | T2   |   250K|   976K|   110   (1)| 00:00:02 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."PRODUCT_ID"="T2"."PRODUCT_ID")
   3 - access("T1"."COMPANY_ID"="T3"."COMPANY_ID")
   4 - access("from$_subquery$_005"."COMPANY_ID"="T3"."COMPANY_ID")

Without merging the view, the cost is 126. So Oracle could have chosen not to transform the query, use the plan with cost 126, but instead of it, it merged the view without examining the cost, and chosen a plan with a cost 10 times higher = 1242. The above quoted sentence from the documentation explains this behavior.

If you check the execution plan of the original query, you can see the lack of view merging - VIEW operation in the execution plan and "from$_subquery$_005" in the predicates part:

Plan hash value: 54875574

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |      1 |        |   126 (100)|      3 |00:00:02.31 |     390 |       |       |          |
|   1 |  HASH GROUP BY          |      |      1 |      3 |   126   (7)|      3 |00:00:02.31 |     390 |    11M|  3254K| 1246K (0)|
|*  2 |   HASH JOIN             |      |      1 |    250K|   120   (2)|    250K|00:00:01.91 |     390 |  1969K|  1969K| 1606K (0)|
|*  3 |    HASH JOIN            |      |      1 |    500 |    10  (10)|    500 |00:00:00.01 |       6 |  2440K|  2440K|  978K (0)|
|*  4 |     HASH JOIN           |      |      1 |      3 |     7  (15)|      3 |00:00:00.01 |       4 |  2440K|  2440K|  980K (0)|
|   5 |      VIEW               |      |      1 |      3 |     4  (25)|      3 |00:00:00.01 |       2 |       |       |          |
|   6 |       HASH GROUP BY     |      |      1 |      3 |     4  (25)|      3 |00:00:00.01 |       2 |  2441K|  2441K|  717K (0)|
|   7 |        TABLE ACCESS FULL| T1   |      1 |    500 |     3   (0)|    500 |00:00:00.01 |       2 |       |       |          |
|   8 |      TABLE ACCESS FULL  | T3   |      1 |      3 |     3   (0)|      3 |00:00:00.01 |       2 |       |       |          |
|   9 |     TABLE ACCESS FULL   | T1   |      1 |    500 |     3   (0)|    500 |00:00:00.01 |       2 |       |       |          |
|  10 |    TABLE ACCESS FULL    | T2   |      1 |    250K|   110   (1)|    250K|00:00:00.38 |     384 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."PRODUCT_ID"="T2"."PRODUCT_ID")
   3 - access("T1"."COMPANY_ID"="T3"."COMPANY_ID")
   4 - access("from$_subquery$_005"."COMPANY_ID"="T3"."COMPANY_ID")

If we force view merging for this query:

explain plan for 
select company_id
from t1 
     join t2 using(product_id) 
     join ( select /*+ merge */ company_id
           from (select /*+ merge */ company_id from t1 group by company_id) 
                join t3 using(company_id) ) using(company_id)
group by company_id;

select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1480121551

-------------------------------------------------------------------------------------
| Id  | Operation               | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |     3 |     9 |  1242  (91)| 00:00:15 |
|   1 |  HASH GROUP BY          |           |     3 |     9 |  1242  (91)| 00:00:15 |
|   2 |   VIEW                  | VM_NWVW_1 |     7 |    21 |  1242  (91)| 00:00:15 |
|   3 |    HASH GROUP BY        |           |     7 |   119 |  1242  (91)| 00:00:15 |
|*  4 |     HASH JOIN           |           |    41M|   675M|   210  (44)| 00:00:03 |
|   5 |      TABLE ACCESS FULL  | T1        |   500 |  1500 |     3   (0)| 00:00:01 |
|*  6 |      HASH JOIN          |           |   250K|  3417K|   116   (1)| 00:00:02 |
|*  7 |       HASH JOIN         |           |   500 |  5000 |     6   (0)| 00:00:01 |
|   8 |        TABLE ACCESS FULL| T3        |     3 |     9 |     3   (0)| 00:00:01 |
|   9 |        TABLE ACCESS FULL| T1        |   500 |  3500 |     3   (0)| 00:00:01 |
|  10 |       TABLE ACCESS FULL | T2        |   250K|   976K|   110   (1)| 00:00:02 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("COMPANY_ID"="T3"."COMPANY_ID")
   6 - access("T1"."PRODUCT_ID"="T2"."PRODUCT_ID")
   7 - access("T1"."COMPANY_ID"="T3"."COMPANY_ID")

We can see the cost would be 1242, and the internal view VM_NWVW_1 appeared as a result of a Cost-based Query Transformation. Because of the GROUP BY, this can be only Complex View Merging and Cost-based Query Transformation, where the cost is not ignored, that is why Oracle did not choose this plan.

So knowing that DISTINCT can be eliminated more easily, even without Cost-Based Query Transformations, and that we do not need aggregate results in this query, it would be better to use the double DISTINCT version:

select distinct company_id
from t1 
     join t2 using(product_id) 
     join ( select company_id
           from (select distinct company_id from t1) 
                join t3 using(company_id) ) using(company_id)
;

Plan hash value: 3914171991

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name            | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                 |      1 |        |   126 (100)|      3 |00:00:02.34 |       |       |          |
|   1 |  HASH UNIQUE            |                 |      1 |      3 |   126   (7)|      3 |00:00:02.34 |    11M|  3254K|  746K (0)|
|*  2 |   HASH JOIN             |                 |      1 |    250K|   120   (2)|    250K|00:00:01.93 |  1969K|  1969K| 1602K (0)|
|*  3 |    HASH JOIN            |                 |      1 |    500 |    10  (10)|    500 |00:00:00.01 |  2440K|  2440K|  978K (0)|
|*  4 |     HASH JOIN           |                 |      1 |      3 |     7  (15)|      3 |00:00:00.01 |  2440K|  2440K|  986K (0)|
|   5 |      VIEW               | VW_DTP_09DF12B3 |      1 |      3 |     4  (25)|      3 |00:00:00.01 |       |       |          |
|   6 |       HASH UNIQUE       |                 |      1 |      3 |     4  (25)|      3 |00:00:00.01 |  2441K|  2441K|  716K (0)|
|   7 |        TABLE ACCESS FULL| T1              |      1 |    500 |     3   (0)|    500 |00:00:00.01 |       |       |          |
|   8 |      TABLE ACCESS FULL  | T3              |      1 |      3 |     3   (0)|      3 |00:00:00.01 |       |       |          |
|   9 |     TABLE ACCESS FULL   | T1              |      1 |    500 |     3   (0)|    500 |00:00:00.01 |       |       |          |
|  10 |    TABLE ACCESS FULL    | T2              |      1 |    250K|   110   (1)|    250K|00:00:00.38 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."PRODUCT_ID"="T2"."PRODUCT_ID")
   3 - access("T1"."COMPANY_ID"="T3"."COMPANY_ID")
   4 - access("ITEM_1"="T3"."COMPANY_ID")

Note: runtime with statistic_level=typical is around 0,04 ms:

SQL> alter session set statistics_level=typical;

Session altered.

Elapsed: 00:00:00.00

SQL> select distinct company_id
from t1
     join t2 using(product_id)
     join ( select company_id
           from (select distinct company_id from t1)
                join t3 using(company_id) ) using(company_id)
;

COMPANY_ID
----------
         1
         2
         3

Elapsed: 00:00:00.04

Note that this results another execution plan because of the Distinct Placement Cost-Based Transformation (VW_DTP_... internal view).

Another solution is to prevent Simple View Merging with the /*+ no_merge */ as shown earlier.

However, on 12c (12.1.0.2.3, Oracle Linux x86-64), view merging works quite well, because the database chooses HASH JOIN SEMI instead of HASH JOIN, and execution time is as fast as the other variants:

Plan hash value: 1674031258

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |      1 |        |   121 (100)|      3 |00:00:01.53 |       |       |          |
|   1 |  HASH GROUP BY        |      |      1 |      3 |   121   (3)|      3 |00:00:01.53 |  2442K|  2442K| 1265K (0)|
|*  2 |   HASH JOIN SEMI      |      |      1 |    500 |   120   (2)|    500 |00:00:01.53 |  1969K|  1969K| 1514K (0)|
|*  3 |    HASH JOIN          |      |      1 |    500 |     9   (0)|    500 |00:00:00.01 |  2440K|  2440K|  989K (0)|
|*  4 |     HASH JOIN SEMI    |      |      1 |      3 |     6   (0)|      3 |00:00:00.01 |  2440K|  2440K|  988K (0)|
|   5 |      TABLE ACCESS FULL| T3   |      1 |      3 |     3   (0)|      3 |00:00:00.01 |       |       |          |
|   6 |      TABLE ACCESS FULL| T1   |      1 |    500 |     3   (0)|      3 |00:00:00.01 |       |       |          |
|   7 |     TABLE ACCESS FULL | T1   |      1 |    500 |     3   (0)|    500 |00:00:00.01 |       |       |          |
|   8 |    TABLE ACCESS FULL  | T2   |      1 |    250K|   110   (1)|    249K|00:00:00.38 |       |       |          |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."PRODUCT_ID"="T2"."PRODUCT_ID")
   3 - access("T1"."COMPANY_ID"="T3"."COMPANY_ID")
   4 - access("COMPANY_ID"="T3"."COMPANY_ID")

SQL> alter session set statistics_level=typical;

Session altered.

SQL> set timing on

SQL> select company_id
from t1
     join t2 using(product_id)
     join ( select company_id
           from (select company_id from t1 group by company_id)
                join t3 using(company_id) ) using(company_id)
  group by company_id;

COMPANY_ID
----------
         1
         2
         3

Elapsed: 00:00:00.02
0

It is not due to any difference in Oracle's treatment of DISTINCT and GROUP BY. If we replace GROUP BY in the second query with a DISTINCT in SELECT we shall get similar performance/ plan cost as the first one

I think, if we are using same clause in both the inner and outer queries with a natural join then Oracle parser is able to recognize that it will need to do same operation twice and is able to merge the operation into a single operation.

Thus if we look at the plan of the first query the GROUP BY operation takes place once after the TABLE scans are completed, resulting in lesser cost and execution time.

  • sqlfiddle.com/#!4/90b2b/2 seems to prove this - using distinct on both levels makes the query as fast as using group by twice, only mixing them gives the bad plan – jkavalik May 13 '15 at 10:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.