oracle 序列问题
刘，您好：在序列中有cache参数和order参数，我的问题是如果为noorder，在内存中cache的序列按照一定顺序分配的还是随机分配数的？如下是我的操作过程，为什么object_id会是连续的呢？是否与nextval有关系？请你帮忙解答一下，谢谢。
SQL>
SQL> create sequence emp_seq
2 start with 1
3 increment by 1
4 minvalue 1
5 nomaxvalue
6 cache 100
7 noorder;
Sequence created.
SQL> insert into emp_text (owner,object_name,object_type) select owner,object_name,object_type from dba_objects where rownum<10000;
9999 rows created.
SQL> SELECT * FROM EMP_TEXT WHERE ROWNUM<20;
OWNER OBJECT_NAME OBJECT_TYPE OBJECT_ID
------------------------------ ------------------------------ ------------------------------ ------------
PUBLIC V$MAP_LIBRARY SYNONYM
SYS V_$MAP_FILE VIEW
PUBLIC V$MAP_FILE SYNONYM
SYS V_$MAP_FILE_EXTENT VIEW
PUBLIC V$MAP_FILE_EXTENT SYNONYM
SYS V_$MAP_ELEMENT VIEW
PUBLIC V$MAP_ELEMENT SYNONYM
SYS V_$MAP_EXT_ELEMENT VIEW
PUBLIC V$MAP_EXT_ELEMENT SYNONYM
SYS V_$MAP_COMP_LIST VIEW
PUBLIC V$MAP_COMP_LIST SYNONYM
OWNER OBJECT_NAME OBJECT_TYPE OBJECT_ID
------------------------------ ------------------------------ ------------------------------ ------------
SYS V_$MAP_SUBELEMENT VIEW
PUBLIC V$MAP_SUBELEMENT SYNONYM
SYS V_$MAP_FILE_IO_STACK VIEW
PUBLIC V$MAP_FILE_IO_STACK SYNONYM
SYS V_$SQL_REDIRECTION VIEW
PUBLIC V$SQL_REDIRECTION SYNONYM
SYS V_$SQL_PLAN VIEW
PUBLIC V$SQL_PLAN SYNONYM
19 rows selected.
SQL>
SQL>
SQL> COMMIT;
Commit complete.
SQL> select dbms_metadata.get_ddl('SEQUENCE','EMP_SEQ') FROM DUAL;
DBMS_METADATA.GET_DDL('SEQUENCE','EMP_SEQ')
--------------------------------------------------------------------------------
CREATE SEQUENCE "AMY"."EMP_SEQ" MINVALUE 1 MAXVALUE 99999999999999999999999
99999 INCREMENT BY 1 START WITH 1 CACHE 100 NOORDER NOCYCLE
1 row selected.
SQL> declare
cursor emp_cursor is
2 3 select * from emp_text for update;
4 v_object_name emp_text%rowtype;
5 begin
6 open emp_cursor;
7 loop
fetch emp_cursor
8 9 into v_object_name;
10 if emp_cursor%found then
11 update emp_text
12 set object_id =
13 (emp_seq.nextval)
14 where object_name = v_object_name.object_name;
15 end if;
16 exit when emp_cursor%notfound;
17 end loop;
18 close emp_cursor;
19 end;
20 /
PL/SQL procedure successfully completed.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM EMP_TEXT WHERE ROWNUM<20;
OWNER OBJECT_NAME OBJECT_TYPE OBJECT_ID
------------------------------ ------------------------------ ------------------------------ ------------
PUBLIC V$MAP_LIBRARY SYNONYM 1
SYS V_$MAP_FILE VIEW 2
PUBLIC V$MAP_FILE SYNONYM 3
SYS V_$MAP_FILE_EXTENT VIEW 4
PUBLIC V$MAP_FILE_EXTENT SYNONYM 5
SYS V_$MAP_ELEMENT VIEW 6
PUBLIC V$MAP_ELEMENT SYNONYM 7
SYS V_$MAP_EXT_ELEMENT VIEW 8
PUBLIC V$MAP_EXT_ELEMENT SYNONYM 9
SYS V_$MAP_COMP_LIST VIEW 10
PUBLIC V$MAP_COMP_LIST SYNONYM 11
OWNER OBJECT_NAME OBJECT_TYPE OBJECT_ID
------------------------------ ------------------------------ ------------------------------ ------------
SYS V_$MAP_SUBELEMENT VIEW 12
PUBLIC V$MAP_SUBELEMENT SYNONYM 13
SYS V_$MAP_FILE_IO_STACK VIEW 14
PUBLIC V$MAP_FILE_IO_STACK SYNONYM 15
SYS V_$SQL_REDIRECTION VIEW 16
PUBLIC V$SQL_REDIRECTION SYNONYM 17
SYS V_$SQL_PLAN VIEW 18
PUBLIC V$SQL_PLAN SYNONYM 19
Sequences with CACHE and ORDER
Use these settings when ordered sequences are required and gaps are tolerated
This combination performs better than NOCACHE type sequences
Each instances caches the same set of numbers in the row cache
The synchronization of the sequence next value is done using the lock value in an SV instance lock on the sequence resource managed by LCK0
When the requesting LCK0 process receives the lock grant, it can forward the lock value (i.e. new sequence number) to the foreground process (requestor).
Sequences with CACHE and NOORDER
Use these settings when ordered sequences are NOT required and gaps are tolerated
This combination performs better than CACHE with ORDER type sequences and provides best performance in RAC
Each instances caches its own range of numbers in the row cache
Caches are not Synchronised
noorder is for RAC only
noorder 只针对rac实例。 单实例看不出效果。 它是用来控制所有实例的取值否是有序的。 Liu Maclean(刘相兵 发表于 2014-1-20 21:44 static/image/common/back.gif
Sequences with CACHE and ORDER
Use these settings when ordered sequences are required and gaps are ...
额。
谢谢，怪不得在序列产生递增主键时会出现热块征用采用散列分区全局索引或是方向键索引，另外还建议sequence使用noorder呢。 背向天堂 发表于 2014-1-20 21:47 static/image/common/back.gif
noorder 只针对rac实例。 单实例看不出效果。 它是用来控制所有实例的取值否是有序的。 ...
谢谢，我在测试一下。
页:
[1]