Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ When the master key encounter NULL     - MySQL monitoring tool -Innotop (Database)

- PHP generates a random password several ways (Programming)

- Ten to improve the efficiency of the Linux bash tricks (Linux)

- Plasma 5.4 How to install on Kubuntu 15.04 (Linux)

- Debian 8 Jessie install LAMP server tutorial (Server)

- Java object serialization and deserialization (Programming)

- Linux Network Programming - signal blocking and shielding (block, unblock) (Programming)

- How to Install Puppet in the Ubuntu 15.04 (Server)

- Connect to the Oracle Database Help class (Database)

- Examples of Python any parameters (Programming)

- Zabbix monitors the status of TCP connections (Server)

- Download Manager uGet 2.0 installed in Debian, Ubuntu, Linux Mint and Fedora (Linux)

- Ubuntu users Steam controller does not work solutions (Linux)

- Install the system cleaning software under Linux: BleachBit 1.4 (Linux)

- CentOS 6.5 platform offline compile and install PHP5.6.6 (Server)

- SecureCRT remote connection Ubuntu fails to solve the case (Linux)

- Binding unofficial Google Drive and Ubuntu 14.04 LTS (Linux)

- Install Java on RHEL6 (Linux)

- MongoDB start under Linux (Database)

- Oracle 12C truncate table cascade (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:      
 
- Linux system security mechanisms to share (Linux)
- Ubuntu system grub repair method (Linux)
- 10 easy to use Linux utilities Recommended (Linux)
- Depth understanding of the TCP protocol (Database)
- Vim custom color (Linux)
- Linux operation and maintenance engineers face questions Intermediate (Linux)
- C language programming entry - Common operators (Programming)
- SSL VPN SSL VPN access to security websites patron (Linux)
- Linux kernel source tree to establish load module hello (Linux)
- Oracle Automatic Diagnostic Repository (Automatic Diagnostic Repository, ADR) (Database)
- Use PuTTY to access the virtual machine Linux under Windows xp (Programming)
- Linux NIC configuration (Linux)
- Let you Fun Ubuntu desktop eleven weapon (Linux)
- Upgrade to Linux Mint 16 petra Mint 17 Qiana (Linux)
- Recovery from MySQL master data consistency summary (Database)
- MySQL high availability cluster fragmentation of deployment uses Fabric (Database)
- Video editing captions under Linux (Linux)
- Using Linux command line and execute PHP code (Programming)
- Hunk / Hadoop: Performance Best Practices (Server)
- Java Builder mode (Programming)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.