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
Excelent, very goodi, thanks.