| |
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. |
|
|
|