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

- Xmanager Remote Desktop login CentOS 6.5 (Linux)

- APF firewall installation and configuration under Linux (Linux)

- PostgreSQL query result area is removed and precision (Database)

- Diagnose and resolve the SSH connection slow (Linux)

- 2 minutes to read large data framework Hadoop and Spark similarities and differences (Server)

- CentOS6 installation configuration rsh (Server)

- Swift notes - let you two hours to learn Swift (Programming)

- Ubuntu 15.04 installation MATE 1.10 (Linux)

- Ubuntu 14.04 installation and configuration environment variable JDK1.8.0_25 (Linux)

- Linux host dual LAN transceiver package ARP problem (Linux)

- Git 2.5 increase in the working tree, triangle improved workflow performance (Linux)

- The basic method RHEL6 (CentOS6) used in the source package compiled RPM: Upgrade OpenSSH articles (Linux)

- Linux NFS service fixed ports and firewall configuration (Linux)

- Linux kernel modules related to the management Comments (Linux)

- Linux create and delete user step (Linux)

- The SVN installation, configuration and start - up under Linux (CentOS 6.5) (Server)

- Lazarus for Raspbian installation (Linux)

- MySQL stored procedures and triggers (Database)

- Python in yield (Programming)

 
         
  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:      
 
- Nginx caching using the official guide (Server)
- Shell script to crawl through AWR SQL Report Problems (Database)
- Java NIO2: Buffer (Programming)
- Oracle11g CRS-0184 Problem Solving (Database)
- Recovery from MySQL master data consistency summary (Database)
- Linux kernel programming parameter passing between modules and function calls (Programming)
- Safety testing Unix and Linux server entry succinctly (Linux)
- VNC connection VMware vSphere ESXi 5.5 (Linux)
- Redis Linux system installation guide (Database)
- Ftp user to create multiple virtual machines to support different access rights Examples (Server)
- RHEL6 install Python and other packages from source (Linux)
- Installation and configuration to compile MySQL 5.6.10 under CentOS 5.9 (Database)
- Docker Private Registry Installation Guide at CentOS6.X (Linux)
- How to upgrade to Oracle 11g Oracle 12c (Database)
- Udev: Device Manager for Linux Fundamentals (Linux)
- Ubuntu 12.04 commonly use shortcuts finishing Share (Linux)
- RMAN backup file is much larger than the size of the database Cause Analysis (Database)
- Oracle 11g contraction table space error ORA-03297: file contains used data beyondrequested RESIZE value (Database)
- Python type way of comparison (Programming)
- Why JavaScript basic types can invoke methods (Programming)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.