Database Reference
In-Depth Information
begin with the letter
A
, for the
c1
and
c2
columns, respectively. Hence, a range scan is possible. The third and forth
queries retrieve all rows that contain the letter
A
in any position for the
c1
and
c2
columns, respectively. Hence, a full
index scan is performed:
SELECT /*+ index(t i_c1) */ * FROM t WHERE
c1 LIKE 'A%'
--------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 119 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 119 |
|* 2 |
INDEX RANGE SCAN
| I_C1 | 1 | 119 |
--------------------------------------------------------------
2 - access("C1" LIKE 'A%')
filter("C1" LIKE 'A%')
SELECT /*+ index(t i_c2) */ * FROM t WHERE
c2 LIKE 'A%'
---------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 108 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 108 |
| 2 | BITMAP CONVERSION TO ROWIDS| | 1 | 108 |
|* 3 |
BITMAP INDEX RANGE SCAN
| I_C2 | 1 | 108 |
---------------------------------------------------------------
3 - access("C2" LIKE 'A%')
filter(("C2" LIKE 'A%' AND "C2" LIKE 'A%'))
SELECT /*+ index(t i_c1) */ * FROM t WHERE c1 LIKE '
%A%
'
--------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1921 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1921 |
|* 2 |
INDEX FULL SCAN
| I_C1 | 1 | 1921 |
--------------------------------------------------------------
2 - filter(("C1" LIKE '%A%' AND "C1" IS NOT NULL))
SELECT /*+ index(t i_c2) */ * FROM t WHERE c2 LIKE '
%A%
'
---------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1846 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 1846 |
| 2 | BITMAP CONVERSION TO ROWIDS| | 1 | 1846 |
|* 3 |
BITMAP INDEX FULL SCAN
| I_C2 | 1 | 1846 |
---------------------------------------------------------------
