11 | 12 | 2017
Latest Articles
Popular Articles

Database Design

Not Null constraints consideration

User Rating:  / 0
PoorBest 

Not Null Constraints

Sometimes we wonder whether a table column really needs the not null attribute yes or no.

Can application developers not handle integrity them self ?

Why would we make the life difficult for them by adding foreign and not null constraints ?

Answer : The not null column attribute can have a significant impact on performance.

Let us demonstrate with an example.

Assume table wip_wo, a table with 40 columns, off course with a primary key and a column eq_wo_60 which is not the primary key but used to join with the primary key of another huge table, used in some other queries.

The table has about 320.000 rows.

Let us create an index on that column

DB:UTF8 / USER:MY_UTF > create index ix_eq_wo_60 on wip_wo (eq_wo_60) compute statistics;

 

DB:UTF8 / USER:MY_UTF > alter table wip_wo modify eq_wo_60 null;
Table altered.

 

DB:UTF8 / USER:MY_UTF > set autotrace traceonly


DB:UTF8 / USER:MY_UTF > select distinct eq_wo_60 from wip_wo;
118629 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2267436933

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

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

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

| 0 | SELECT STATEMENT | | 122K | 716K | | 4147 (2)| 00:00:50 |

| 1 | HASH UNIQUE | | 122K | 716K | 5224K | 4147 (2)| 00:00:50 |

| 2 | TABLE ACCESS FULL | WIP_WO| 320K | 1877K | | 3496 (1)| 00:00:42 |

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

Statistics
----------------------------------------------------------
580 recursive calls
0 db block gets
18213 consistent gets
18010 physical reads
0 redo size
1748285 bytes sent via SQL*Net to client
87373 bytes received via SQL*Net from client
7910 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
118629 rows processed

 

What would have been the sql plan if the column would have had the not null attribute ?

DB:UTF8 / USER:MY_UTF > alter table wip_wo modify eq_wo_60 not null;
Table altered.
DB:UTF8 / USER:MY_UTF > set autotrace traceonly
DB:UTF8 / USER:MY_UTF > select distinct eq_wo_60 from wip_wo;
118629 rows selected.

Execution Plan

----------------------------------------------------------
Plan hash value: 1740468086

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

| 0 | SELECT STATEMENT | | 122K| 716K| | 799 (6)| 00:00:10 |

| 1 | HASH UNIQUE | | 122K| 716K| 5224K| 799 (6)| 00:00:10 |
| 2 | INDEX FAST FULL SCAN| IX_EQ_WO_60 | 320K| 1877K| | 147 (4)| 00:00:02 |

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


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
739 consistent gets
730 physical reads
0 redo size
1748285 bytes sent via SQL*Net to client
87373 bytes received via SQL*Net from client
7910 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
118629 rows processed

 

 

By telling to oracle the column is mandatory the optimizer knows every table row has its associated index entry. Previously the index was not used because the optimizer did not know only by accessing the index segment it had the required info. Once the optimizer has this piece of information index access is chosen in this example since it is far more efficient. We notice in the explain plan the hash unique access is apparently not entirely done in  memory, temp space is used. However we never can avoid sorts, joins, ... need temp segments I wonder whether we can avoid temp segments in this particular case. Sorts and hashing is done in the sort area and hash area, if the workarea_size_policy init parameter hasn' t been changed, meaning if the workarea_size_policy = auto then the sizing of the sort area and hash area is done with the init parameter pga_aggregate_target. This is a system wide parameter, every session gets a part of the configured pga pool

DB:UTF8 / USER:MY_UTF > show parameter workarea

NAME TYPE VALUE
------------------------------------ ----------- ---------
workarea_size_policy string AUTO

DB:UTF8 / USER:MY_UTF > show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ---------
pga_aggregate_target big integer 200M

Let us take a look how big the index is and then try to avoid using the temp tablespace.

DB:UTF8 / USER:MY_UTF > select leaf_blocks * 8192/(1024*1024)
from user_indexes where index_name='IX_EQ_WO_60';
LEAF_BLOCKS*8192/(1024*1024)
----------------------------
5.625
DB:UTF8 / USER:MY_UTF > alter session set workarea_size_policy=manual;
Session altered.
DB:UTF8 / USER:MY_UTF > alter session set sort_area_size=10000000;

Session altered.
DB:UTF8 / USER:MY_UTF > set autotrace traceonly
DB:UTF8 / USER:MY_UTF > select distinct eq_wo_60 from wip_wo;

118629 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1740468086

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

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

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

| 0 | SELECT STATEMENT | | 122K| 716K| 178 (22)| 00:00:03 |

| 1 | HASH UNIQUE | | 122K| 716K| 178 (22)| 00:00:03 |

| 2 | INDEX FAST FULL SCAN| IX_EQ_WO_60 | 320K| 1877K| 145 (4)| 00:00:02 |

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

Ok we are convinced the not null attribute has sense, but what if it is not mandatory not all the columns can have the not null attribute, can they ? Assume we have 5, only 5 rows without a value for the column eq_wo_60.

DB:UTF8 / USER:MY_UTF > alter table wip_wo modify eq_wo_60 null;
Table altered.

DB:UTF8 / USER:MY_UTF > update wip_wo set eq_wo_60 = null where rownum < 6;
5 rows updated.
DB:UTF8 / USER:MY_UTF > commit;

Commit complete.

Which sql we use to find the rows without value for eq_wo_60 ?

DB:UTF8 / USER:MY_UTF > select rowid from wip_wo where eq_wo_60 is null;

Execution Plan
----------------------------------------------------------
Plan hash value: 5369894

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 3496 (1)| 00:00:42 |
|* 1 | TABLE ACCESS FULL| WIP_WO | 1 | 11 | 3496 (1)| 00:00:42 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("EQ_WO_60" IS NULL)

Since null values are not indexed it is not a surprise our index ix_eq_wo_60 hasn' t been used. However, what if we assure every table row has its index entry, this by creating our index a little different.

DB:UTF8 / USER:MY_UTF > drop index ix_eq_wo_60 ;

Index dropped.

DB:UTF8 / USER:MY_UTF > create index ix_eq_wo_60 on wip_wo ('A',eq_wo_60) compress compute statistics;

Index created.

DB:UTF8 / USER:MY_UTF > set autotrace traceonly explain;
DB:UTF8 / USER:MY_UTF > select rowid from wip_wo a where eq_wo_60 is null;

Execution Plan
----------------------------------------------------------
Plan hash value: 2101015319

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

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

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

| 0 | SELECT STATEMENT | | 1 | 18 | 144 (1)| 00:00:02 |

|* 1 | INDEX FULL SCAN | IX_EQ_WO_60 | 1 | 18 | 144 (1)| 00:00:02 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("EQ_WO_60" IS NULL)
filter("EQ_WO_60" IS NULL)

With this workaround - which I did not find myself - we can assure every table row, rowid entry has its index entry ( rowid stored in leaf blocks ), and take benefit of it. (only use this if really needed )

More about this ?

http://tkyte.blogspot.com/2006/01/something-about-nothing.html