Still I can’t believe that perhaps somebody noticed this
horrible behavior of CBO. I observed
this in 10.2.0.5 and 11.2.0.4 and even in 12.1.0.1, that sometimes the INDEX
RANGE SCAN or INDEX UNIQUE SCAN execution plan step has no cost (zero cost)
leading the CBO to choose NESTED LOOP over HASH JOIN.
rajesh@PDB1> create table t1 as select * from all_objects;
Table created.
rajesh@PDB1> create table t2 as select * from all_objects;
Table created.
rajesh@PDB1> alter table t2 add constraint t2_pk
2 primary key(object_id);
Table altered.
rajesh@PDB1> exec dbms_stats.gather_Table_Stats(user,'T1');
PL/SQL procedure successfully completed.
rajesh@PDB1> exec dbms_stats.gather_Table_Stats(user,'T2');
PL/SQL procedure successfully completed.
rajesh@PDB1> set autotrace traceonly explain
rajesh@PDB1> select count(*)
2 from t1, t2
3 where t1.object_id = t2.object_id ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1249090617
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
1 | 10 | 425
(2)| 00:00:01 |
| 1 | SORT AGGREGATE |
| 1 | 10 | | |
| 2 | NESTED LOOPS |
| 89729 | 876K| 425
(2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 89729 |
438K| 421 (1)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN| T2_PK | 1 |
5 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 -
access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
rajesh@PDB1> set autotrace off
rajesh@PDB1>
You can see there is no cost associated to step id 4. The CBO then
thinks that doing and index unique scan 89729 times costs nothing.
When hinted for Hash join, CBO estimates a cost of 619, which is
correct. Of course 425 is better than 619, then the CBO goes incorrectly for
nested loop.
rajesh@PDB1> set autotrace traceonly explain
rajesh@PDB1>
rajesh@PDB1> select /*+ use_hash(t1,t2) */ count(*)
2 from t1, t2
3 where t1.object_id = t2.object_id ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2219068312
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes |TempSpc| Cost (%CPU)| Time
|
----------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
1 | 10 | |
619 (1)| 00:00:01 |
| 1 | SORT AGGREGATE |
| 1 | 10 |
| | |
|* 2 | HASH JOIN | | 89729 | 876K|
1496K| 619 (1)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| T2_PK | 89730 | 438K|
| 52 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T1
| 89729 | 438K| |
421 (1)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
rajesh@PDB1> set autotrace off
But in reality Hash joins is better than Nested loops.
rajesh@PDB1> set autotrace traceonly explain statistics
rajesh@PDB1>
rajesh@PDB1> select count(*)
2 from t1, t2
3 where t1.object_id = t2.object_id ;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1249090617
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
1 | 10 | 425
(2)| 00:00:01 |
| 1 | SORT AGGREGATE |
| 1 | 10 | | |
| 2 | NESTED LOOPS |
| 89729 | 876K| 425
(2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 89729 |
438K| 421 (1)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN|
T2_PK | 1 | 5 |
0 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 -
access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2881 consistent gets
1510 physical reads
0 redo size
544 bytes sent via SQL*Net to client
544 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
rajesh@PDB1>
rajesh@PDB1> select /*+ use_hash(t1,t2) */ count(*)
2 from t1, t2
3 where t1.object_id = t2.object_id ;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2219068312
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes |TempSpc| Cost (%CPU)| Time
-------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
1 | 10 | |
619 (1)| 00:00:0
| 1 | SORT AGGREGATE |
| 1 | 10 |
| |
|* 2 | HASH JOIN | | 89729 | 876K|
1496K| 619 (1)| 00:00:0
| 3 | INDEX FAST FULL SCAN| T2_PK | 89730 | 438K|
| 52 (0)| 00:00:0
| 4 | TABLE ACCESS FULL | T1
| 89729 | 438K| |
421 (1)| 00:00:0
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1706 consistent gets
1510 physical reads
0 redo size
544 bytes sent via SQL*Net to client
544 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
rajesh@PDB1>
rajesh@PDB1> set autotrace off
rajesh@PDB1>
Primary key with non-unique
indexes : Still the same
rajesh@PDB1> alter table t2 drop constraint t2_pk;
Table altered.
rajesh@PDB1> alter table t2 add constraint t2_pk
2 primary key(object_id)
3 using index ( create index t2_ind on
t2(object_id) );
Table altered.
rajesh@PDB1>
rajesh@PDB1> set autotrace traceonly explain statistics
rajesh@PDB1>
rajesh@PDB1> select count(*)
2 from t1, t2
3 where t1.object_id = t2.object_id ;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1573195610
------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
1 | 10 | 425
(2)| 00:00:01 |
| 1 | SORT AGGREGATE |
| 1 | 10 | | |
| 2 | NESTED LOOPS |
| 89729 | 876K| 425
(2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 89729 |
438K| 421 (1)| 00:00:01 |
|* 4 | INDEX RANGE SCAN |
T2_IND | 1 | 5 |
0 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 -
access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2926 consistent gets
1509 physical reads
0 redo size
544 bytes sent via SQL*Net to client
544 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
rajesh@PDB1>
rajesh@PDB1> select /*+ use_hash(t1,t2) */ count(*)
2 from t1, t2
3 where t1.object_id = t2.object_id ;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1404746403
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
1 | 10 | |
623 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | |
1 | 10 | | | |
|* 2 | HASH JOIN | | 89729 | 876K|
1496K| 623 (1)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| T2_IND | 89730 | 438K|
| 56 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T1
| 89729 | 438K| |
421 (1)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1719 consistent gets
1509 physical reads
0 redo size
544 bytes sent via SQL*Net to client
544 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1
rows processed
rajesh@PDB1>
rajesh@PDB1> set autotrace off
Only with Unique index (no-constraint)
: still the same
rajesh@PDB1> alter table t2 drop constraint t2_pk;
Table altered.
rajesh@PDB1> drop index t2_ind;
Index dropped.
rajesh@PDB1> create unique index t2_ind on t2(object_id);
Index created.
rajesh@PDB1>
rajesh@PDB1> set autotrace traceonly explain statistics
rajesh@PDB1>
rajesh@PDB1> select count(*)
2 from t1, t2
3 where t1.object_id = t2.object_id ;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2560841438
------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
1 | 10 | 425
(2)| 00:00:01 |
| 1 | SORT AGGREGATE |
| 1 | 10 | | |
| 2 | NESTED LOOPS |
| 89729 | 876K| 425
(2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 89729 |
438K| 421 (1)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN| T2_IND | 1 |
5 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
4 -
access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2881 consistent gets
1509 physical reads
0 redo size
544 bytes sent via SQL*Net to client
544 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0
sorts (memory)
0 sorts (disk)
1 rows processed
rajesh@PDB1>
rajesh@PDB1> select /*+ use_hash(t1,t2) */ count(*)
2 from t1, t2
3 where t1.object_id = t2.object_id ;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1404746403
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes |TempSpc| Cost (%CPU)| Time
|
-----------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
1 | 10 | |
619 (1)| 00:00:01 |
| 1 | SORT AGGREGATE |
| 1 | 10 |
| |
|
|* 2 | HASH JOIN | | 89729 | 876K|
1496K| 619 (1)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| T2_IND | 89730 | 438K|
| 52 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T1
| 89729 | 438K| |
421 (1)| 00:00:01 |
-----------------------------------------------------------------------------------------
---------------------------------------------------
2 -
access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1706 consistent gets
1509 physical reads
0 redo size
544 bytes sent via SQL*Net to client
544 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
rajesh@PDB1>
rajesh@PDB1> set autotrace off
With non-unique index and
unique constraint : still the same
rajesh@PDB1> drop index t2_ind;
Index dropped.
rajesh@PDB1> create index t2_ind on t2(object_id);
Index created.
rajesh@PDB1> alter table t2 add constraint t2_unq
unique(object_id)
2 using index t2_ind ;
Table altered.
rajesh@PDB1>
rajesh@PDB1> set autotrace traceonly explain statistics
rajesh@PDB1>
rajesh@PDB1> select count(*)
2 from t1, t2
3 where t1.object_id = t2.object_id ;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1573195610
------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
1 | 10 | 425
(2)| 00:00:01 |
| 1 | SORT AGGREGATE |
| 1 | 10 | | |
| 2 | NESTED LOOPS |
| 89729 | 876K| 425
(2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 89729 |
438K| 421 (1)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IND | 1 |
5 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 -
access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2926 consistent gets
1509 physical reads
0 redo size
544 bytes sent via SQL*Net to client
544 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
rajesh@PDB1>
rajesh@PDB1> select /*+ use_hash(t1,t2) */ count(*)
2 from t1, t2
3 where t1.object_id = t2.object_id ;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1404746403
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes |TempSpc| Cost (%CPU)| Time
|
-----------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
1 | 10 | |
623 (1)| 00:00:01 |
| 1 | SORT AGGREGATE |
| 1 | 10 |
| | |
|* 2 | HASH JOIN | | 89729 | 876K|
1496K| 623 (1)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| T2_IND | 89730 | 438K|
| 56 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T1
| 89729 | 438K| |
421 (1)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1719 consistent gets
1509 physical reads
0 redo size
0 redo size
544 bytes sent via SQL*Net to client
544 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
rajesh@PDB1>
rajesh@PDB1> set autotrace off
Unique constraint as deferrable:
looks fine
rajesh@PDB1>
rajesh@PDB1> alter table t2 drop constraint t2_unq;
Table altered.
rajesh@PDB1> drop index t2_ind;
Index dropped.
rajesh@PDB1> alter table t2 add constraint t2_unq
2 unique(object_id) deferrable ;
Table altered.
rajesh@PDB1>
rajesh@PDB1> set autotrace traceonly explain statistics
rajesh@PDB1>
rajesh@PDB1> select count(*)
2 from t1, t2
3 where t1.object_id = t2.object_id ;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3356950652
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes |TempSpc| Cost (%CPU)| Time
|
-----------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
1 | 10 | |
623 (1)| 00:00:01 |
| 1 | SORT AGGREGATE |
| 1 | 10 |
| | |
|* 2 | HASH JOIN |
| 89729 | 876K| 1496K|
623 (1)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| T2_UNQ | 89730 | 438K|
| 56 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T1
| 89729 | 438K| |
421 (1)| 00:00:01 |
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 db block gets
1719 consistent gets
1509 physical reads
0 redo size
544 bytes sent via SQL*Net to client
544 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
rajesh@PDB1>
rajesh@PDB1> select /*+ use_hash(t1,t2) */ count(*)
2 from t1, t2
3 where t1.object_id = t2.object_id ;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3356950652----------------------------------------------------------
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes |TempSpc| Cost (%CPU)| Time
|
-----------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
1 | 10 | |
623 (1)| 00:00:01 |
| 1 | SORT AGGREGATE |
| 1 | 10 |
| | |
|* 2 | HASH JOIN | | 89729 | 876K|
1496K| 623 (1)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| T2_UNQ | 89730 | 438K|
| 56 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T1
| 89729 | 438K| |
421 (1)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Statistics
----------------------------------------------------------
0
recursive calls
0 db block gets
1719 consistent gets
1509 physical reads
0 redo size
544 bytes sent via SQL*Net to client
544 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
rajesh@PDB1>
rajesh@PDB1> set autotrace off
rajesh@PDB1>
Primary key constraint as deferrable:
looks fine
rajesh@PDB1> alter table t2 drop constraint t2_unq;
Table altered.
rajesh@PDB1> alter table t2 add constraint t2_pk
2 primary key(object_id) deferrable;
Table altered.
rajesh@PDB1>
rajesh@PDB1> set autotrace traceonly explain statistics
rajesh@PDB1>
rajesh@PDB1> select count(*)
2 from t1, t2
3 where t1.object_id = t2.object_id ;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3356950652
-----------------------------------------------------------------------------------------
| Id | Operation | Name
| Rows | Bytes |TempSpc| Cost
(%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
1 | 10 | |
623 (1)| 00:00:01 |
| 1 | SORT AGGREGATE |
| 1 | 10 |
| | |
|* 2 | HASH JOIN | | 89729 | 876K|
1496K| 623 (1)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| T2_UNQ | 89730 | 438K|
| 56 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T1
| 89729 | 438K| |
421 (1)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1719 consistent gets
1509 physical reads
0 redo size
544 bytes sent via SQL*Net to client
544 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
rajesh@PDB1>
rajesh@PDB1> select /*+ use_hash(t1,t2) */ count(*)
2 from t1, t2
3 where t1.object_id = t2.object_id ;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3356950652
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes |TempSpc| Cost (%CPU)| Time
|
-----------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
1 | 10 | |
623 (1)| 00:00:01 |
| 1 | SORT AGGREGATE |
| 1 | 10 |
| | |
|* 2 | HASH JOIN | | 89729 | 876K|
1496K| 623 (1)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| T2_UNQ | 89730 | 438K|
| 56 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T1
| 89729 | 438K| |
421 (1)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1719 consistent gets
1509 physical reads
0 redo size
544 bytes sent via SQL*Net to client
544 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
rajesh@PDB1>
rajesh@PDB1> set autotrace off
To sum up: CBO assumes zero cost for accessing an index (by UNIQUE SCAN
or RANGE SCAN) when uniqueness on the same column is enforced (by
non-deferrable pk/unique constraint or unique index).
