Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ When the master key encounter NULL     - CentOS 6.5 install Firefox (Linux)

- Some practical tips Linux (Linux)

- Getting Started with Linux system to learn: how to check memory usage of Linux (Linux)

- Install Ubuntu 14.04 and Windows 8 / 8.1 dual-boot in UEFI mode (Linux)

- Oracle table space rename and delete table space (Database)

- Ubuntu 14.04 running ASP.NET Configuration Mono + Jexus (Server)

- Linux Mint brightness adjustment --xrandr command learning (Linux)

- 14 useful example Linux Sort command (Linux)

- Linux user login ban (Linux)

- Ubuntu update bug fixes Daquan (Linux)

- Oracle Standby Redo Log experiment (Database)

- Nginx high concurrency optimization ideas (Server)

- Nodejs complete installation instructions for Express (Linux)

- Let OS X support NTFS write file (Linux)

- Use XtraBackup to MySQL database online incremental backup and recovery (Database)

- How to Install Foreman under Ubuntu (Server)

- Install Web-based monitoring tool: Linux-Dash (Server)

- Security measures under Unix multi-user operating system (Linux)

- Tmux Getting Start (Linux)

- Analysis of potential problems through custom Orabbix monitoring Oracle (Database)

 
         
  When the master key encounter NULL
     
  Add Date : 2018-11-21      
         
         
         
  Null primary key and seemingly not much matter because the general master key is not null, but the two together, there will be many unexpected situation, saying that the result is not surprising because of the expected range, but if you understand the basic principle of the whole process and reasonable.

We look first to demonstrate the problem.

First, create a table, create a unique index.

SQL> conn n1 / n1
 Connected.
 SQL>
 SQL> select * from cat;

no rows selected

SQL> create table test (x number, y number);

Table created.

SQL> create unique index ind_test on test (x, y);

Index created.

SQL> insert into test values ​​(1,2);

1 row created.
Insert duplicate data again, this would certainly be wrong to throw doubt.

SQL> insert into test values ​​(1,2);
 insert into test values ​​(1,2)
 *
 ERROR at line 1:
 ORA-00001: unique constraint (N1.IND_TEST) violated

Then we started testing null related scenarios.
SQL> insert into test values ​​(1, null);

1 row created.

SQL> insert into test values ​​(null, 1);

1 row created.
Insert two Null values ​​are also possible.

SQL> insert into test values ​​(null, null);

1 row created.
Insert two null values ​​again, or can.
SQL> insert into test values ​​(null, null);

1 row created.
But in turn, insert 1 again, null when it cast the wrong.

SQL> insert into test values ​​(1, null);
 insert into test values ​​(1, null)
 *
 ERROR at line 1:
 ORA-00001: unique constraint (N1.IND_TEST) violated

Similarly, null, Scene 1 is true.
SQL> insert into test values ​​(null, 1);
 insert into test values ​​(null, 1)
 *
 ERROR at line 1:
 ORA-00001: unique constraint (N1.IND_TEST) violated

Insert two null values ​​again.
SQL> insert into test values ​​(null, null);

1 row created.
See the table in the test data as follows:

SQL> select * from test;

        X Y
 ---------- ----------
          12
          1
          1

 


 6 rows selected.
You can see that there are 6 lines. null column values ​​do not see any display.
 In order to identify, we have to print out the rownum.

SQL> select rownum, x, y from test;

    ROWNUM X Y
 ---------- ---------- ----------
          112
          twenty one
          31
          4
          5
          6

6 rows selected.
Test finished null values ​​related, we take a look at the empty string '' case.
 Insert '' when it will throw error.

SQL> insert into test values ​​(1, '');
 insert into test values ​​(1, '')
 *
 ERROR at line 1:
 ORA-00001: unique constraint (N1.IND_TEST) violated

Insert two empty strings, and null the effect is the same.
SQL> insert into test values ​​( '', '');

1 row created.
 null and empty string combination, there is no problem.

SQL> insert into test values ​​(null, '');

1 row created.

SQL> insert into test values ​​( '', '');

1 row created.
Check the data again, the data rows null values ​​increased significantly.

SQL> select rownum, x, y from test;

    ROWNUM X Y
 ---------- ---------- ----------
          112
          twenty one
          31
          4
          5
          6
          7
          8
          9

9 rows selected.

We can further check the length of the null value, use the length ()
 SQL> select rownum, x, length (x), y, length (y) from test;

    ROWNUM X LENGTH (X) Y LENGTH (Y)
 ---------- ---------- ---------- ---------- ----------
          11121
          211
          311
          4
          5
          6
          7
          8
          9

9 rows selected.
You can see the null value corresponding to the length without any display.
 If = to match the empty string, and null effect as not matching.

SQL> select * from test where x = '';

no rows selected
We take a look at the information dump it, dump the result is null null columns

  1 * select rownum, x, y, dump (x) from test
 SQL> /

    ROWNUM X Y DUMP (X)
 ---------- ---------- ---------- -------------------- ----------
          1 1 2 Typ = 2 Len = 2: 193,2
          2 1 Typ = 2 Len = 2: 193,2
          3 1 NULL
          4 NULL
          5 NULL
          6 NULL
          7 NULL
          8 NULL
          9 NULL

9 rows selected.

Feeling null value or a very interesting topic, if a way where xxx is null in the query, it will not take the index scan,
 If the table does not not null constraints, which may involve a full table scan case
 We create a new table a, then there is no constraint on the not null field Object_id

SQL> create table a as select object_id, object_name, object_type from dba_objects;
 Table created.

SQL> desc a
  Name Null? Type
  -------------------------------------------------- --- -------- ------------------------------------
 OBJECT_ID NUMBER
  OBJECT_NAME VARCHAR2 (128)
  OBJECT_TYPE VARCHAR2 (19)

SQL> analyze table a compute statistics;
 Table analyzed.
 Dbms_stats.gather_table_stats;
 SQL> create unique index ind_a on a (object_id);
 Index created.

SQL> set autot traceonly exp

If object_id according to query, it will take the unique scanning.
But if you look at all object_id values, will go full table scan. If you look object_id is null rows found 0.
If you add a not null constraint, it will go a fast full scan.
Null value visible impact index scan is indeed a very great need to aware of this issue in advance of the time to write sql statement.
     
         
         
         
  More:      
 
- Use mdadm tool to create software RAID 0 (Linux)
- Node.js installed on Ubuntu Upstream version (Linux)
- Java learning problems encountered (Programming)
- Ease of use "Explain Shell" script to understand Shell command (Linux)
- Binary search is really easy as you think you do (Programming)
- C ++ pointer of the (error-prone model) (Programming)
- How Mutt mail client to use cipher text password (Linux)
- Via Twitter how open source library to be used anywhere Emoji emoticons (Linux)
- Android start automatically and add and delete a desktop shortcut (Programming)
- Sublime Text 3 shortcuts summary (Linux)
- Lua4.0 interpreter entrance (Programming)
- Clojure programming languages: take full advantage of the Clojure plug-in Eclipse (Programming)
- Linux upgrade Glibc (Linux)
- Linux, set and view environment variables (Linux)
- UNIX how to restrict users by IP Telnet (Linux)
- Distributed File System FastDFS deployment (Server)
- Node.JS different ways to install under Ubuntu 15.04 (Linux)
- Linux kernel source tree to establish load module hello (Linux)
- Applications in Objective-C runtime mechanism (Programming)
- Linux operating system must know the security command (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.