Composite index with constants in Oracle

The original knowledge of the index only knows that the index can be based on one or more columns, the B-tree index does not contain NULL, but in some cases we need to find some data through the where column name is NULL, when the database uses the full table scan because it cannot use the index, resulting in inefficient execution, At this point we can solve this problem by using a composite index containing constants.

The following experiment begins:

First set up the test table

Sys@orcl>create table Test_objects nologging as select RowNum id,a.* from Dba_objects a where 1=2;

Table created.

Insert 5 million data:

Sys@orcl>declare

L_CNT number;

L_rows number:=&1;

Begin

Insert/*+ Append */into test_objects select rownum,a.* from Dba_objects A;

L_cnt:=sql%rowcount;

Commit

while (l_cnt<l_rows)

Loop

Insert/*+ Append */into test_objects select Rownum+l_cnt,

Owner,object_name,subobject_name,

OBJECT_ID,DATA_OBJECT_ID,

Object_type,created,last_ddl_time,

Timestamp,status,temporary,

Generated,secondary

From Test_objects

where rownum<=l_rows-l_cnt;

L_cnt:=l_cnt+sql%rowcount;

Commit

End Loop;

End 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20-21

22/

Enter value for 1:5 million

Old 3:l_rows number:=&1;

New 3:l_rows number:=5000000;

Pl/sql procedure successfully completed.

Establish a generic B-tree index on the object_id column:

Sys@orcl>create index idx_oid_test_objects on test_objects (object_id);

Index created.

Collect table information:

Sys@orcl>exec dbms_stats.gather_table_stats (' SYS ', ' test_objects ');

Pl/sql procedure successfully completed.

Test whether the index is working properly:

Sys@orcl>select Count (*) from test_objects where object_id=52457;

COUNT (*)

----------

99

Execution Plan

----------------------------------------------------------

Plan Hash value:3877533889

--------------------------------------------------------------------------------

----------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |

Time |

--------------------------------------------------------------------------------

----------

| 0 |                      SELECT STATEMENT |     |     1 |     5 | 3 (0) |

00:00:01 |

|  1 |                      SORT AGGREGATE |     |     1 |            5 | |

|

|* 2 | INDEX RANGE scan|    idx_oid_test_objects |   99 |     495 | 3 (0) |

00:00:01 |

--------------------------------------------------------------------------------

----------

predicate information (identified by Operation ID):

---------------------------------------------------

2-access ("object_id" =52457)

Statistics

----------------------------------------------------------

1 Recursive calls

0 db Block gets

3 Consistent gets

0 physical Reads

0 Redo Size

411 Bytes sent via sql*net to client

385 bytes received via sql*net from client

2 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

1 rows processed

You can see that the index works, so let's get to the point of what happens when object_id is null in the Where condition:

Sys@orcl>select Count (*) from test_objects where object_id is null;

COUNT (*)

----------

0

Execution Plan

----------------------------------------------------------

Plan Hash value:3799704240

--------------------------------------------------------------------------------

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time

|

--------------------------------------------------------------------------------

---

| 0 |              SELECT STATEMENT |     |     1 | 5 | 16612 (2) | 00:03:2

0 |

|  1 |              SORT AGGREGATE |     |     1 |            5 | |

|

|* 2 | TABLE ACCESS full|     test_objects |     1 | 5 | 16612 (2) | 00:03:2

0 |

--------------------------------------------------------------------------------

---

predicate information (identified by Operation ID):

---------------------------------------------------

2-filter ("object_id" is NULL)

Statistics

----------------------------------------------------------

1 Recursive calls

0 db Block gets

74808 consistent gets

74730 Physical Reads

904 Redo Size

410 Bytes sent via sql*net to client

385 bytes received via sql*net from client

2 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

1 rows processed

Because the B-tree index does not record NULL, only full table scans can be used. Generated a lot of consistent gets below we'll create a composite index with constants and query execution:

Sys@orcl>create index idx_oid2_test_objects on test_objects (object_id,1);

Index created.

Sys@orcl>select Count (*) from test_objects where object_id is null;

COUNT (*)

----------