SQL>
SQL>
SQL> create table t ( x int, y int );
Table created.
SQL>
SQL> create unique index t_idx on t(x,y);
Index created.
SQL>
SQL> insert into t values ( 1, 1 );
1 row created.
SQL> insert into t values ( 1, NULL );
1 row created.
SQL> insert into t values ( NULL, 1 );
1 row created.
SQL> insert into t values ( NULL, NULL );
1 row created.
SQL>
SQL> analyze index t_idx validate structure;
Index analyzed.
SQL>
SQL> select name, lf_rows from index_stats;
NAME LF_ROWS
------------------------------ ----------
T_IDX 3
SQL>
SQL> insert into t values ( NULL, NULL );
1 row created.
SQL> insert into t values ( NULL, 1 );
insert into t values ( NULL, 1 )
*
ERROR at line 1:
ORA-00001: unique constraint (RNTSOFT.T_IDX) violated
SQL> insert into t values ( 1, NULL );
insert into t values ( 1, NULL )
*
ERROR at line 1:
ORA-00001: unique constraint (RNTSOFT.T_IDX) violated
SQL>
SQL> select x, y, count(*) from t group by x,y having count(*) > 1;
X Y COUNT(*)
---------- ---------- ----------
2
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL> create table t ( x int, y int NOT NULL );
Table created.
SQL> create unique index t_idx on t(x,y);
Index created.
SQL>
SQL> insert into t values ( 1, 1 );
1 row created.
SQL> insert into t values ( NULL, 1 );
1 row created.
SQL>
SQL> begin
2 dbms_stats.gather_table_stats(user,'T');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> set autotrace on
SQL>
SQL> select * from t where x is null;
X Y
---------- ----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 2946670127
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 |
|* 1 | INDEX RANGE SCAN| T_IDX | 1 | 5 | 1 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("X" IS NULL)
Note
-----
- cpu costing is off (consider enabling it)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
456 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> drop table t;
Table dropped.
SQL>