Question

oracle 表列值唯一 怎么实现

Answer 1

你这个情况，
可以采用
序列号
+
触发器
的方式来处理。
SQL>
CREATE
SEQUENCE
test_sequence2
2
increment
by
1
--
每次递增1
3
start
with
1
--
从1开始
4
nomaxvalue
--
没有最大值
5
minvalue
1
--
最小值=1
6
NOCYCLE;
--
不循环
Sequence
created.
SQL>
CREATE
TABLE
test_create_tab2
(
2
id
INT,
3
val
VARCHAR(10),
4
PRIMARY
KEY
(id)
5
);
Table
created.
SQL>
CREATE
OR
REPLACE
TRIGGER
BeforeTestCreate2Insert
2
BEFORE
INSERT
ON
test_create_tab2
3
FOR
EACH
ROW
4
BEGIN
5
SELECT
test_sequence2.nextval
INTO
:new.id
FROM
dual;
6
END;
7
/
Trigger
created.
SQL>
INSERT
INTO
test_create_tab2(val)
VALUES
('NO
id');
1
row
created.
SQL>
INSERT
INTO
test_create_tab2(id,
val)
VALUES
(1,
'id
no
use');
1
row
created.
SQL>
SELECT
*
FROM
test_create_tab2;
ID
VAL
----------
--------------------
1
NO
id
2
id
no
use

Answer 2

select
count(1)
from
xxx
where
yyy
is
null;
首先
yyyy
是nullable这样做才有意义，
如果是nullable
那么最快的是
建一个索引
create
index
ind1
on
xxx(yyy,1);
如下面的例子：
sql>
set
timing
on;
sql>
sql>
select
count(1)
from
tvol
where
owner
is
null;
count(1)
----------
0
elapsed:
00:00:02.89
sql>
sql>
create
index
ind1
on
tvol(owner,1);
index
created.
elapsed:
00:00:20.73
sql>
select
count(1)
from
tvol
where
owner
is
null;
count(1)
----------
0
elapsed:
00:00:00.00