Storage Indexes - Part III

Nulls are the special case for storage indexes. There is a separate flag in the storage index structure that is used to indicate whether a storage region contains nulls or not. This separate flag make queries looking for nulls or absence of nulls even more efficient that the normal minimum and maximum comparison that are typically done. Here is an example comparing typical performance with special null optimization.

 

c##rajesh@PDB1> select count(*) from big_table;

 

  COUNT(*)

----------

 100000000

 

c##rajesh@PDB1> exec show_space('BIG_TABLE')

Unformatted Blocks .....................               0

FS1 Blocks (0-25) ......................               0

FS2 Blocks (25-50) .....................               0

FS3 Blocks (50-75) .....................               0

FS4 Blocks (75-100).....................               9

Full Blocks ............................       2,024,478

Total Blocks............................       2,028,080

Total Bytes.............................  16,614,031,360

Total MBytes............................          15,844

Unused Blocks...........................               0

Unused Bytes............................               0

Last Used Ext FileId....................              24

Last Used Ext BlockId...................      20,748,032

Last Used Block.........................             512

 

PL/SQL procedure successfully completed.

 

c##rajesh@PDB1>

c##rajesh@PDB1> select s.name,m.value

  2  from v$statname s ,

  3      v$mystat m

  4  where s.statistic# = m.statistic#

  5  and s.name in ('cell physical IO bytes saved by storage index',

  6  'cell physical IO interconnect bytes returned by smart scan' );

 

NAME                                                                    VALUE

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

cell physical IO bytes saved by storage index                               0

cell physical IO interconnect bytes returned by smart scan                  0

 

c##rajesh@PDB1> set timing on

c##rajesh@PDB1> select count(*) from big_table where ID is null;

 

  COUNT(*)

----------

         0

 

Elapsed: 00:00:00.27

c##rajesh@PDB1> select s.name,m.value

  2  from v$statname s ,

  3      v$mystat m

  4  where s.statistic# = m.statistic#

  5  and s.name in ('cell physical IO bytes saved by storage index',

  6  'cell physical IO interconnect bytes returned by smart scan' );

 

NAME                                                                    VALUE

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

cell physical IO bytes saved by storage index                     16584597504

cell physical IO interconnect bytes returned by smart scan                  0

 

Elapsed: 00:00:00.00

c##rajesh@PDB1>

 

In this example you can see that retrieval of few nulls was extremely fast, this is because there is no possibility that any storage region that does not contain a null value will have to be read. So no false positives will slow down this query. Notice also the amount of IO saved by the null query is almost equal to the size of the table.