Sequence, Identity and Auto Increment

[nnani]

Hello All,

I wanted to understand the usage of these three functionalities in Vertica
1. Sequence
2. Identity
3. Auto Increment

I know the difference between them, the coumentation says, They are are type of sequence object
If so, Like we create sequences, can we also create identity, Auto Increment

I want to have a unique column for my table while loading through COPY utility.

I used sequences for this, but it says the cache value is 250 K, so what if my records are more then 250k while loading.
How do we implement Auto Increment and Identity?
Which is the best solution for my scenario.

[BoMBaY]

Question: If so, Like we create sequences, can we also create identity, Auto Increment
Answer: Yes, you can. For each column you can choose a type of the sequence objects that you want to use. But each table can contain only one auto_increment or identity column. A table can contain both an auto_increment or identity column, and a named sequence, as in the next example, illustrating a table with both types of sequences:

Code: Select all

create table test2 (id integer NOT NULL UNIQUE, middle integer default nextval('my_seq'), next int, last auto_increment);

CREATE TABLE

--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Question: I want to have a unique column for my table while loading through COPY utility.
I used sequences for this, but it says the cache value is 250 K, so what if my records are more then 250k while loading.
Answer: When you create a sequence object, the CACHE parameter controls the sequence efficiency, by determining the number of sequence values each node maintains during a session. The default cache value is 250K, meaning that each node reserves 250,000 values for each sequence per session.
HP Vertica distributes a session across all nodes. After you create a sequence, the first time a node executes a NEXTVAL() function as part of a SQL statement, the node reserves its own cache of sequence values. The node then maintains that set of values for the current session. Other nodes executing a NEXTVAL() function will also create and maintain their own cache of sequence values cache.
NOTE: If any node consumes all of its sequence values, HP Vertica must perform a catalog lock to obtain a new set of values. A catalog lock can be costly in terms of database performance, since certain activities, such as data inserts, cannot occur until HP Vertica releases the lock.

--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Question: How do we implement Auto Increment and Identity?
Answer: column-constraint

Syntax

[ CONSTRAINT constraint-name ] {
...[ NULL | NOT NULL ]
...| PRIMARY KEY
...| REFERENCES table-name [( column [ , ... ] )]
...| UNIQUE
...[ DEFAULT default ]
...[ AUTO_INCREMENT ]
...[ IDENTITY [ ( cache ) | ( start, increment[, cache ] ) ] ]
}

AUTO_INCREMENT - Creates a table column whose values are automatically generated by the database. The initial value of an AUTO_INCREMENT column is always 1. You cannot specify a different initial value.
IDENTITY [ ( cache ) | ( start, increment[, cache ] ) ] - Specifies a column whose values are automatically generated by the database. You can use IDENTITY columns as primary keys. IDENTITY column parameters are evaluated in order as follows:
One parameter: Evaluated as cache, indicates the number of unique numbers each node allocates per session. cache must be a positive integer. Default: 250,000. Minimum: 1.
Two parameters: Evaluated as start, increment.

start specifies the number at which to start the IDENTITY column. Default: 1.

increment specifies how much to increment the value from the previous row's value. Default: 1.
Three parameters: Evaluated as start, increment, cache.

Note: You cannot change the value of an IDENTITY column once the table exists.

Each time you add a row to the table, HP Vertica increments the column value by 1. You cannot change the value of an AUTO_INCREMENT column.

The following example uses the IDENTITY column-constraint to create a table with an ID column that has an initial value of 1. It is incremented by 1 every time a row is inserted.

Code: Select all

=> CREATE TABLE Premium_Customer(

     ID IDENTITY(1,1), 

     lname VARCHAR(25),

     fname VARCHAR(25), 

     store_membership_card INTEGER

   );

=> INSERT INTO Premium_Customer (lname, fname, store_membership_card ) 

   VALUES ('Gupta', 'Saleem', 475987);

Confirm the row you added and see the ID value:

Code: Select all

=> SELECT * FROM Premium_Customer;

 ID | lname | fname  | store_membership_card

----+-------+--------+-----------------------

  1 | Gupta | Saleem |                475987

(1 row)

The following example uses the AUTO_INCREMENT column-constraint to create a table with an ID column that automatically increments every time a row is inserted.

Code: Select all

=> CREATE TABLE Premium_Customer(

     ID AUTO_INCREMENT, 

     lname VARCHAR(25),

     fname VARCHAR(25), 

     store_membership_card INTEGER

   );

=> INSERT INTO Premium_Customer (lname, fname, store_membership_card ) 

   VALUES ('Gupta', 'Saleem', 475987);

Confirm the row you added and see the ID value:

Code: Select all

=> SELECT * FROM Premium_Customer;

 ID | lname | fname  | store_membership_card

----+-------+--------+-----------------------

  1 | Gupta | Saleem |                475987

(1 row)

Now add two rows:

Code: Select all

=> INSERT INTO Premium_Customer (lname, fname, store_membership_card)

   VALUES ('Lee', 'Chen', 598742);

=> INSERT INTO Premium_Customer (lname, fname, store_membership_card)

   VALUES ('Brown', 'John', 642159);

=> SELECT * FROM Premium_Customer;

 ID | lname | fname  | store_membership_card

----+-------+--------+-----------------------

  1 | Gupta | Saleem |                475987

  2 | Lee   | Chen   |                598742

  3 | Brown | John   |                642159

(3 rows)

--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Question: Which is the best solution for my scenario.
Answer: It depend on what you want.

Do you want to set initial cache? Named Sequence, Identity (Auto-Increment always cache value 250K)
Do you want to define start value? Named Sequence, Identity (Auto-Increment always start at 1)
Do you want to specify increment unit? Named Sequence, Identity (Auto-Increment always increment 1)
Do you want to create as standalone object? Named Sequence
Do you want to create as column constraint? Identity, Auto-Increment
Do you want it exists only as part of table? Identity, Auto-Increment
Do you want to use expressions? Named Sequence
Do you want it unique across table? Named Sequence
Do you want to change parameters? Named Sequnnce
Do you want to set to increment or decrement? Named Sequnnce
Do you want to specify minimum value? Named Sequnnce
Do you want to specify maximum value? Named Sequnnce

--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hope this helps.