Sequence partition clause [message #655145] |
Tue, 23 August 2016 02:41 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Can anyone point me to a description of the PARTITION clause of CREATE SEQUENCE? I can't find it in the docs, and I've no idea what it is for. Examples:
orclz>
orclz> --first, the default - I know how that works:
orclz> create sequence s1;
Sequence created.
orclz> select s1.nextval from dual;
NEXTVAL
----------------------------------------
1
orclz> select dbms_metadata.get_ddl('SEQUENCE','S1') from dual;
DBMS_METADATA.GET_DDL('SEQUENCE','S1')
--------------------------------------------------------------------------------
CREATE SEQUENCE "SCOTT"."S1" MINVALUE 1 MAXVALUE 99999999999999999999999999
99 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE NOPARTITION
orclz> --but I don't understand the rest:
orclz> create sequence s2 partition;
Sequence created.
orclz> select s2.nextval from dual;
NEXTVAL
----------------------------------------
4103920000000000000000000000000001
orclz> select dbms_metadata.get_ddl('SEQUENCE','S2') from dual;
DBMS_METADATA.GET_DDL('SEQUENCE','S2')
--------------------------------------------------------------------------------
CREATE SEQUENCE "SCOTT"."S2" MINVALUE 1 MAXVALUE 99999999999999999999999999
99 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE PARTITION 100000000
orclz> create sequence s3 partition 1000;
Sequence created.
orclz> select s3.nextval from dual;
NEXTVAL
----------------------------------------
550000000000000000000000000001
orclz> select dbms_metadata.get_ddl('SEQUENCE','S3') from dual;
DBMS_METADATA.GET_DDL('SEQUENCE','S3')
--------------------------------------------------------------------------------
CREATE SEQUENCE "SCOTT"."S3" MINVALUE 1 MAXVALUE 99999999999999999999999999
99 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE PARTITION 1000
orclz> It isn't something I particlarly need to know - but I am curious. Thank you for any insight.
|
|
|
|
Re: Sequence partition clause [message #655154 is a reply to message #655151] |
Tue, 23 August 2016 03:15 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Sussed! I'm sure Franck is right (he usually is):orclz>
orclz> conn scott/tiger
Connected.
orclz> select s3.nextval from dual;
NEXTVAL
-----------------------------------
70000000000000000000000000035
orclz> select s3.nextval from dual;
NEXTVAL
-----------------------------------
70000000000000000000000000036
orclz> conn scott/tiger
Connected.
orclz> select s3.nextval from dual;
NEXTVAL
-----------------------------------
1180000000000000000000000000037
orclz> conn scott/tiger
Connected.
orclz>
It reminds of a situation I had to deal with a few years ago. It was a RAC for a cell phone company: really high load concurrent inserts, with dreadful contention between the instances. I used hash partitioning, with the instance number as the partitioning key and prefix for the primary key. That meant we could get an affinity of partitions to each instance, reducing the interconnect traffic hugely.
|
|
|