CAT | RDBMS

10

Dec

what is sequences in rdbms?how to create?

0 Comments | Posted by shubh shah in RDBMS

A sequence is a database item that generates a sequence of integers.
You typically use the integers generated by a sequence to populate a numeric primary key column.
You create a sequence using the CREATE SEQUENCE statement.

syntax:-

CREATE SEQUENCE sequence_name
[START WITH start_num]
[INCREMENT BY increment_num]
[ { MAXVALUE maximum_num | NOMAXVALUE } ]
[ { MINVALUE minimum_num | NOMINVALUE } ]
[ { CYCLE | NOCYCLE } ]
[ { CACHE cache_num | NOCACHE } ]
[ { ORDER | NOORDER } ];

 

  1. The default start_num is 1.
  2. The default increment number is 1.
  3. The absolute value of increment_num must be less than the difference between maximum_num and minimum_num.
  4. minimum_num must be less than or equal to start_num, and minimum_num must be less than maximum_num.
  5. NOMINVALUE specifies the maximum is 1 for an ascending sequence or -10^26 for a descending sequence.
  6. NOMINVALUE is the default.
  7. maximum_num must be greater than or equal to start_num, and maximum_num must be greater than minimum_num.
  8. NOMAXVALUE specifies the maximum is 10^27 for an ascending sequence or C1 for a descending sequence.
  9. NOMAXVALUE is the default.
  10. CYCLE specifies the sequence generates integers even after reaching its maximum or minimum value.
  11. When an ascending sequence reaches its maximum value, the next value generated is the minimum.
  12. When a descending sequence reaches its minimum value, the next value generated is the maximum.
  13. NOCYCLE specifies the sequence cannot generate any more integers after reaching its maximum or minimum value.
  14. NOCYCLE is the default.
  15. CACHE cache_num specifies the number of integers to keep in memory.
  16. The default number of integers to cache is 20.
  17. The minimum number of integers that may be cached is 2.
  18. The maximum integers that may be cached is determined by the formula CEIL(maximum_num – minimum_num)/ABS(increment_num).
  19. NOCACHE specifies no integers are to be stored.
  20. ORDER guarantees the integers are generated in the order of the request.
  21. You typically use ORDER when using Real Application Clusters.
  22. NOORDER doesn’t guarantee the integers are generated in the order of the request.
  23. NOORDER is the default.

 

[del.icio.us] [Digg] [dzone] [Facebook] [Google] [LinkedIn] [Twitter] [Windows Live] [Yahoo!] [Email] More »

No tags Hide

Managed by

MNP INFOTECH

To top