Nullable Column of Table
Sambhav, November 24, 2015 - 11:54 am UTC
Hi Chris,
Thanks a lot for your answer.
Well for mandatory value columns we have already created Not null constraints.
For some null-able columns is it better to give some default value(Let say -1 or 'NA' for example) ? If yes then what are the advantage of this.
Is it impacting performance in anyway?
November 24, 2015 - 12:43 pm UTC
And train your developers
Hemant K Chitale, November 25, 2015 - 8:37 am UTC
Remember to train your developers in identifying NULLs and anticipating NULLs.
SQL>create table hkc_t_1 (id number, data varchar2(5));
Table created.
SQL>insert into hkc_t_1 values (1, 'ABC');
1 row created.
SQL>insert into hkc_t_1 values (2, 'DEF');
1 row created.
SQL>insert into hkc_t_1 (id) values (3);
1 row created.
SQL>select * from hkc_t_1 where data != 'ABC';
ID DATA
---------- -----
2 DEF
1 row selected.
SQL>
Why doesn't the query return the row with ID=3 ?
November 25, 2015 - 9:35 am UTC
Agreed, three valued logic does take some work to get used to.
Hi...
Sambhav777@gmail.com, November 25, 2015 - 4:25 pm UTC
Hi Hemant ,
Thanks for your post.we were aware of these concepts.
I asked this question w.r.t performance .
~Sambhav
November 25, 2015 - 4:50 pm UTC
How will it affect performance? It depends. Using magic values can affect the optimizer's cardinality estimates:
http://www.oracle.com/technetwork/issue-archive/2012/12-nov/o62asktom-1867739.html The bigger is still that developers need to know what the magic values are and write code to avoid them. And what happens if someone enters "NA" as a real value? See the XXXXXXX and NOPLATE stories above.