The test_data table I am using in my query can be generated as per the below code:
DROP TABLE test_data;
CREATE TABLE test_data (
random_value NUMBER,
random_string VARCHAR2(20),
binary_number NUMBER,
binary_string VARCHAR2(20)
);
DELETE FROM test_data;
COMMIT;
INSERT INTO test_data
SELECT
dbms_random.value() random_value,
dbms_random.string('x',20) random_string,
CASE
WHEN ROWNUM < 500000 THEN 1
ELSE 0
END
binary_number,
CASE
WHEN ROWNUM < 500000 THEN 'YNTRFKC3M081ZVYF4WPR'
ELSE 'TV2XN199BSSGDI547AC1'
END
binary_string
FROM
dual
CONNECT BY
level <= 1000000;
COMMIT;
If I add a couple of bitmap indexes to columns binary_number and binary_string (I was told they were useful when the number of distinct values << the number of rows in a column), they are not used in the execution plan that Oracle generates. What could be the reason behind this?
DROP INDEX ix_td1;
DROP INDEX ix_td2;
CREATE BITMAP INDEX ix_td1 ON
test_data (
binary_number
);
CREATE BITMAP INDEX ix_td2 ON
test_data (
binary_string
);
EXEC dbms_stats.gather_table_stats(user,'TEST_DATA');
This is the explain plan:
EXPLAIN PLAN
FOR
SELECT
*
FROM
test_data t
WHERE
binary_number = 1
AND binary_string = 'YNTRFKC3M081ZVYF4WPR';
SELECT
*
FROM
TABLE ( dbms_xplan.display(format => 'ALL OUTLINE') );
Plan hash value: 1706400376
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 526K| 32M| 2716 (1)| 00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL| TEST_DATA | 526K| 32M| 2716 (1)| 00:00:01 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Outline Data
-------------
/*
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "T"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("BINARY_STRING"='YNTRFKC3M081ZVYF4WPR' AND "BINARY_NUMBER"=1)
filter("BINARY_STRING"='YNTRFKC3M081ZVYF4WPR' AND "BINARY_NUMBER"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T"."RANDOM_VALUE"[NUMBER,22], "T"."RANDOM_STRING"[VARCHAR2,20],
"BINARY_NUMBER"[NUMBER,22], "BINARY_STRING"[VARCHAR2,20]
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this statement
Even if I force the query to use the indexes, they Oracle resists to use them in favour of a FTS.
SELECT /* index(ix_td1) index(ix_td2) */
*
FROM
test_data t
WHERE
binary_number = 1
AND binary_string = 'YNTRFKC3M081ZVYF4WPR';
CodePudding user response：
The database is probably doing the right thing here. Bitmap indexes are great for (a) "finding a needle in a haystack", ie, getting a small number of rows from a large one, and (b) aggregations where we do not need to visit the table.
Example
SQL>
SQL> CREATE TABLE test_data (
2 random_value NUMBER,
3 random_string VARCHAR2(20),
4 binary_number NUMBER,
5 binary_string VARCHAR2(20)
6 );
Table created.
SQL>
SQL> INSERT /* APPEND */ INTO test_data
2 SELECT
3 dbms_random.value() random_value,
4 dbms_random.string('x',20) random_string,
5 CASE
6 WHEN ROWNUM < 500000 THEN 1
7 ELSE 0
8 END
9 binary_number,
10 CASE
11 WHEN ROWNUM < 500000 THEN 'YNTRFKC3M081ZVYF4WPR'
12 ELSE 'TV2XN199BSSGDI547AC1'
13 END
14 binary_string
15 from
16 ( select 1 FROM dual CONNECT BY level <= 1000 ),
17 ( select 1 FROM dual CONNECT BY level <= 1000 );
1000000 rows created.
SQL>
SQL>
SQL> COMMIT;
Commit complete.
SQL>
SQL> CREATE BITMAP INDEX ix_td1 ON
2 test_data (
3 binary_number
4 );
Index created.
SQL>
SQL> CREATE BITMAP INDEX ix_td2 ON
2 test_data (
3 binary_string
4 );
Index created.
SQL>
SQL> EXEC dbms_stats.gather_table_stats(user,'TEST_DATA');
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace traceonly explain
SQL> SELECT *
2 FROM test_data t
3 WHERE binary_number = 1
4 AND binary_string = 'YNTRFKC3M081ZVYF4WPR';
Execution Plan
----------------------------------------------------------
Plan hash value: 1706400376
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 250K| 15M| 2723 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_DATA | 250K| 15M| 2723 (1)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("BINARY_NUMBER"=1 AND
"BINARY_STRING"='YNTRFKC3M081ZVYF4WPR')
SQL>
SQL> set autotrace traceonly explain
SQL> SELECT count(*)
2 FROM test_data t
3 WHERE binary_number = 1
4 AND binary_string = 'YNTRFKC3M081ZVYF4WPR';
Execution Plan
----------------------------------------------------------
Plan hash value: 214576717
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 24 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 24 | | |
| 2 | BITMAP CONVERSION COUNT | | 250K| 5859K| 24 (0)| 00:00:01 |
| 3 | BITMAP AND | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE| IX_TD1 | | | | |
|* 5 | BITMAP INDEX SINGLE VALUE| IX_TD2 | | | | |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("BINARY_NUMBER"=1)
5 - access("BINARY_STRING"='YNTRFKC3M081ZVYF4WPR')
SQL>
SQL> SELECT *
2 FROM test_data t
3 WHERE binary_number = 2
4 AND binary_string = 'ASASAS';
Execution Plan
----------------------------------------------------------
Plan hash value: 4022380120
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 1 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST_DATA | 1 | 65 | 1 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | IX_TD1 | | | | |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("BINARY_STRING"='ASASAS')
3 - access("BINARY_NUMBER"=2)
SQL>
Looking at your first query - see the Rows estimate? 250K. This means that we'll very quickly find 250,000 entries in the bitmap indexes, but then we'll need to do 250,000 table row lookups to locate those rows. That's incredibly expensive, which is why we opt for the full scan.