Oracle Database - Sequence

Card Puncher Data Processing

About

This page is about the management of a relational sequence in Oracle.

A highly scalable sequence because it's:

  • sorted.
  • non-blocking ID generator.
SELECT MY_SEQ.NEXTVAL FROM DUAL;
CREATE SEQUENCE MY_SEQ INCREMENT BY 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 CACHE 20;

Primary Key Trigger

Primary Key Trigger

create or replace TRIGGER "TRIGGER_NAME" BEFORE INSERT ON "TABLE_NAME" FOR EACH ROW
BEGIN 
    IF :new.PK_ID IS NULL THEN
		select SEQUENCE_NAME.nextval into :new.PK_ID from dual;
	END;
END;

Bad Practice

With a max

INSERT INTO gfx_suggestion (suggestion, suggestion_id, timestamp, 
suggestion_type_fl,
name, email, business_unit_key) select 'test suggestion. RJ 04/19/01', 
max(suggestion_id)+1, sysdate, 'T', 'Ron Jennings' , '[email protected]', '5' from 
gf_suggestion

When two people insert at about the same time with a max function, they both get the SAME suggestion_id.





Discover More
Sorting Quicksort Anim
Algorithm - Complexity (Big O)

Complexity is based off of how the algorithm scales for very large n. Constants and lower-order terms are dropped. For example: will execute in constant time a model that takes time has complexity,...
Card Puncher Data Processing
Oracle Database - Objects

Database object. Each database object is considered to either be: a schema object (ie owned by a user) or a “non-schema object”. See for plsql object type: Object in different namespace are...
Card Puncher Data Processing
Oracle Database - Reverse Key Indexes

They are BTree indexes whereby the bytes in the key are “reversed”. Reverse key indexes can be used to obtain a more even distribution of index entries throughout an index that is populated with increasing...
Card Puncher Data Processing
Oracle Database - Schema Object

The schema Objects are logical structures of data stored physically in data file and categorized in a schema. private synonym Database objects that are owned by a user are schema...
Data System Architecture
Relational Table - Sequence

A sequence is a auxiliary relational data structure that maintains a sequence. Relational sequences are generally used to create a generated primary key via an incremental number. They may also be...



Share this page:
Follow us:
Task Runner