1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63
|
Connected to Oracle9i Enterprise Edition Release 9.2.0.5.0
Connected as mni
SQL>
SQL> drop table test_nr
2 /
Table dropped
SQL> create table test_nr (a number, b number)
2 /
Table created
SQL> insert into test_nr values(null, null)
2 /
1 row inserted
SQL> insert into test_nr values(null, null)
2 /
1 row inserted
SQL> insert into test_nr values(0,0)
2 /
1 row inserted
SQL> insert into test_nr values(0,0)
2 /
1 row inserted
SQL> insert into test_nr select level,level from dual connect by level < 100
2 /
99 rows inserted
SQL> commit
2 /
Commit complete
SQL> Select a, count(*)
2 from test_nr
3 group by a
4 having count(*) > 1
5 /
A COUNT(*)
---------- ----------
0 2
2
SQL> create unique index ix_test_nr on test_nr (decode(a,0,NULL,a))
2 /
Index created
SQL> |