In this demo, we will see how to index null
values. We will also see how the
optimizer changes the explain plan when we index the columns with NULL values
and NOT NULL values.
1.
Create a table with the records
from dba_tables view.
SQL> create table testnull as select
* from dba_tables;
Table created.
2.
Note that the columns PCT_FREE
and PCT_INCREASE has “NULL” values.
SQL> select count(*) from testnull
where pct_free is null;
COUNT(*)
----------
66
SQL> select count(*) from testnull
where pct_increase is null;
COUNT(*)
----------
2775
3.
Let us create an index on
PCT_FREE column
SQL> create index
pctfree_null_idx on testnull(pct_free);
Index created.
4.
Gather the stats for the table
and index (using cascade=true)
SQL> exec
dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'TESTNULL',estimate_percent=>NULL,cascade=>true,method_opt=>'FOR
ALL COLUMNS SIZE 1');
PL/SQL procedure
successfully completed.
5.
Now search for rows with
PCT_FREE has null values.
SQL> select * from testnull where
pct_free is null;
66 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 623426927
------------------------------------------------------------------------------
| Id
| Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
|
0 | SELECT STATEMENT | |
66 | 15906 | 30 (0)| 00:00:01 |
|*
1 | TABLE ACCESS FULL| TESTNULL | 66 | 15906 | 30
(0)| 00:00:01 |
------------------------------------------------------------------------------
As you can see, the index is ignored and
the query is going for full table scan.
6.
Now, let us try to use HINTS
and see whether the query uses index.
SQL> select /* + INDEX(tn,pctfree_null_idx) */ *
from testnull where pct_free is null;
66 rows
selected.
Execution Plan
----------------------------------------------------------
Plan hash
value: 623426927
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 66 | 15906 | 30
(0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TESTNULL | 66 | 15906 | 30 (0)|
00:00:01 |
------------------------------------------------------------------------------
Even using
HINTS, did not work and still going for FTS.
7.
Now, let us create concatenated
indexes with NULL column and NOT-NULL values.
SQL> create
index conc_idx on testnull(pct_free,owner);
Index created.
8.
Now, let us query and see.
SQL> select
* from testnull where pct_free is null;
66 rows
selected.
Execution Plan
----------------------------------------------------------
Plan hash
value: 1448583841
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
66 | 15906 | 10 (0)| 00:00:01 |
| 1 |
TABLE ACCESS BY INDEX ROWID| TESTNULL | 66 | 15906 | 10
(0)| 00:00:01 |
|* 2 |
INDEX RANGE SCAN |
CONC_IDX | 66 | |
2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
We can see that
the query is going for INDEX RANGE SCAN and using the composite index.
9.
Let us drop this index now and
will create a composite index using PCT_FREE and PCT_INCREASE which has null
values in both their columns.
SQL> drop
index conc_idx;
Index dropped.
SQL> create
index conc_idx_nulls on testnull(pct_free,pct_increase) compute statistics;
Index created.
10.
Query now and check.
SQL> select * from testnull where pct_free is
null;
66 rows
selected.
Execution Plan
----------------------------------------------------------
Plan hash
value: 623426927
------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 66 | 15906 | 30
(0)| 00:00:01 |
|* 1 |
TABLE ACCESS FULL| TESTNULL |
66 | 15906 | 30 (0)| 00:00:01 |
------------------------------------------------------------------------------
Still the query goes for FTS .
11.
Again, we will create another
index with just a space tagged at the end.
SQL> create
index conc_idx_i on testnull(pct_free,' ') compute statistics;
Index created.
SQL> select * from testnull where pct_free is
null;
66 rows
selected.
Execution Plan
----------------------------------------------------------
Plan hash
value: 3944464689
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
66 | 15906 | 6 (0)| 00:00:01 |
| 1 |
TABLE ACCESS BY INDEX ROWID| TESTNULL
| 66 | 15906 | 6
(0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | CONC_IDX_I | 66 |
| 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Now, it goes
for Index range scan.
12.
Drop the index with space and create a new concatenated index with any
variable
SQL> drop
index conc_idx_i ;
Index dropped.
SQL> create index conc_idx_i1 on
testnull(pct_free,'i') compute statistics;
Index created.
13.
Query now and see, it will go
for Index scan.
SQL> select
* from testnull where pct_free is null;
66 rows
selected.
Execution Plan
----------------------------------------------------------
Plan hash
value: 1942626316
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 66 | 15906 | 6
(0)| 00:00:01 |
| 1 |
TABLE ACCESS BY INDEX ROWID| TESTNULL
| 66 | 15906 | 6
(0)| 00:00:01 |
|* 2 |
INDEX RANGE SCAN | CONC_IDX_I1
| 66 | |
2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Conclusion
1. Index is not used when we create an index ONLY on NULL columns.
2. Index is used when we create a concatenated index with NULL and NOT
NULL columns.
3. Index is used in the query when we create with just space or any
variable as show in step 12 and 13.
No comments:
Post a Comment