NO_EXPAND USE_CONCAT hints

This article focus on two hints:

USE_CONCAT -  The USE_CONCAT hint forces combined OR conditions in the WHERE clause of a query to be transformed into a compound query using the UNION ALL set operator. Generally, this transformation occurs only if the cost of the query using the concatenations is cheaper than the cost without them.

NO_EXPAND - The NO_EXPAND hint prevents the cost-based optimizer from considering OR-expansion for queries having OR conditions or IN-lists in the WHERE clause. Usually, the optimizer considers using OR expansion and uses this method if it decides that the cost is lower than not using it.

NO_EXPAND can be forced by alter session for all statements

ALTER SESSION SET "_no_or_expansion" = TRUE;

Example

Creating and loading fake data to table TEST_TBL

CREATE TABLE test_tbl
(
 id1 NUMBER,
 id2 NUMBER
);

CREATE BITMAP INDEX test_tbl_idx1 ON test_tbl(id1);

CREATE INDEX test_tbl_idx2 ON test_tbl(id2);

INSERT INTO test_tbl
SELECT LEVEL, level
FROM dual
CONNECT BY LEVEL < 100000;
COMMIT;

EXEC dbms_stats.gather_table_stats(USER, 'TEST_TBL');

USE_CONCAT

Using this hint forces to use both indexes b-tree and bitmap. CONCATENATION is word in explain plan that shows usage of concatenation.

EXPLAIN PLAN FOR
SELECT /*+ USE_CONCAT */ id1, id2 
 FROM test_tbl 
 WHERE id1=5 
 OR id1=445 
 OR id2=12;

SELECT * 
 FROM 
TABLE(dbms_xplan.display(
format=>'ALL -ROWS -BYTES -COST -PROJECTION -PREDICATE -NOTE'));

Plan hash value: 3429276532
 
--------------------------------------------------------------------------
| Id | Operation | Name | Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 00:00:01 |
| 1 | CONCATENATION | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED | TEST_TBL | 00:00:01 |
| 3 | INDEX RANGE SCAN | TEST_TBL_IDX2 | 00:00:01 |
| 4 | INLIST ITERATOR | | |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST_TBL | 00:00:01 |
| 6 | BITMAP CONVERSION TO ROWIDS | | |
| 7 | BITMAP INDEX SINGLE VALUE | TEST_TBL_IDX1 | |
--------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
 1 - SEL$1 
 2 - SEL$1_1 / TEST_TBL@SEL$1
 3 - SEL$1_1 / TEST_TBL@SEL$1

NO_EXPAND

This hint is blocking CONCATENATION usage.It’s very useful when your explain plan becomes very large and generation of explain plan can take a lot of time. Above example is not such case it’s too simple example.

EXPLAIN PLAN FOR
SELECT /*+ NO_EXPAND */ id1, id2
FROM (
SELECT /*+ USE_CONCAT */ id1, id2 
 FROM test_tbl 
 WHERE id1=5 
 OR id1=445 
 OR id2=12 );

SELECT * 
 FROM 
 TABLE(dbms_xplan.display( 
FORMAT=>'ALL -ROWS -BYTES -COST -PROJECTION -PREDICATE -NOTE'));

Plan hash value: 1014772754
 
------------------------------------------------------------------------
| Id | Operation | Name | Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST_TBL | 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | |
| 3 | BITMAP OR | | |
| 4 | BITMAP INDEX SINGLE VALUE | TEST_TBL_IDX1 | |
| 5 | BITMAP INDEX SINGLE VALUE | TEST_TBL_IDX1 | |
| 6 | BITMAP CONVERSION FROM ROWIDS | | |
| 7 | INDEX RANGE SCAN | TEST_TBL_IDX2 | 00:00:01 |
------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
 1 - SEL$F5BB74E1 / TEST_TBL@SEL$2

the same effect is for changing settings on session level

ALTER SESSION SET "_no_or_expansion" = TRUE;

EXPLAIN PLAN FOR
SELECT /*+ USE_CONCAT */ id1, id2 
 FROM test_tbl 
 WHERE id1=5 
 OR id1=445 
 OR id2=12 ;

SELECT * 
 FROM 
 TABLE(dbms_xplan.display( 
FORMAT=>'ALL -ROWS -BYTES -COST -PROJECTION -PREDICATE -NOTE'));

Plan hash value: 1014772754
 
------------------------------------------------------------------------
| Id | Operation | Name | Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST_TBL | 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | |
| 3 | BITMAP OR | | |
| 4 | BITMAP INDEX SINGLE VALUE | TEST_TBL_IDX1 | |
| 5 | BITMAP INDEX SINGLE VALUE | TEST_TBL_IDX1 | |
| 6 | BITMAP CONVERSION FROM ROWIDS | | |
| 7 | INDEX RANGE SCAN | TEST_TBL_IDX2 | 00:00:01 |
------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
 1 - SEL$1 / TEST_TBL@SEL$

Have a fun 🙂

Tomasz

Leave a Reply

Your email address will not be published. Required fields are marked *