Home » SQL & PL/SQL » SQL & PL/SQL » Sequence partition clause (DB12.x (and possibly earlier))
Sequence partition clause [message #655145] Tue, 23 August 2016 02:41 Go to next message
John Watson
Messages: 8558
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 #655151 is a reply to message #655145] Tue, 23 August 2016 03:02 Go to previous messageGo to next message
Michel Cadot
Messages: 67879
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

For the moment it is something not documented.
Here's a Franck Pachot's article about it: http://blog.dbi-services.com/oracle-partitioned-sequences-a-future-new-feature-in-12c/.

Re: Sequence partition clause [message #655154 is a reply to message #655151] Tue, 23 August 2016 03:15 Go to previous message
John Watson
Messages: 8558
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.
Previous Topic: Need to correlate and reconcile two data sets with different number of records
Next Topic: Union of 4 different columns in Single Row
Goto Forum:
  


Current Time: Sat Jun 12 21:03:42 CDT 2021