OraFAQ Forum: SQL & PL/SQL » Sequence partition clause

Home » SQL & PL/SQL » SQL & PL/SQL » Sequence partition clause (DB12.x (and possibly earlier))

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Sequence partition clause [message #655145]

Tue, 23 August 2016 02:41

John Watson
Messages: 8931
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.

Report message to a moderator

Re: Sequence partition clause [message #655151 is a reply to message #655145]

Tue, 23 August 2016 03:02

Michel Cadot
Messages: 68645
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/.

Report message to a moderator

Re: Sequence partition clause [message #655154 is a reply to message #655151]

Tue, 23 August 2016 03:15

John Watson
Messages: 8931
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.

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 09:00:36 CDT 2024