SEQUENCES and CLUSTERS, PSUEDO COLUMNS

S E Q U E N C E S

The sequence generator can be used to automatically generate sequence numbers for rows in a table. For example, you can use sequence generator to produce unique primary keys. In order to generate sequence number automatically you must define a sequence using create sequence statement. The create sequence statement has the following syntax.

Syntax :
Create Sequence SeqName
Increment by n,
Start with n,
Minvalue n,
Maxvalue n,
Cycle/Nocycle;

Here startwith specifies the value to be generated by the sequence when sequence was called for the first time. Increment by specifies the difference between the values generated by the sequence i.e. the increment for the old value, generated by the sequence to generate new value. Minvalue specifies the minimum value to be generated by the sequence. In general startwith and minvalue are same. Maxvalue specifies the maximum number to be generated by the sequence. Nocycle option specifies that the sequence will end when the value generated by the sequence equals to maxvalue. When nocycle option is used and sequence reaches maxvalue, then generating sequence numbers using that sequence is not possible. Cycle option specifies that when sequence reaches its maxvalue and user calls the sequence again, sequence will continue generating numbers starting from minvalue. It means that sequence will never end.

Example : Create Sequence SeqDno
Increment by 10
Start with 10
Minvalue 10
Maxvalue 100
NoCycle;





Generating Sequence Numbers

Nextval : The pseudo column “Nextval” is used to extract successive sequence number. When you refer to next value a new sequence number is generated. Nextval is mostly useful in DML statement.

Syntax : SequenceName.Nextval
Example : Insert into DEPT values(SeqDno.NEXTVAL, ‘ACCOUNTING’, ‘DALLAS’);

Currval : The pseudo column “Currval” is used to refer to a sequence number that has just been generated. When you refer to Nextval for a given sequence, then the newly generated sequence number is placed in Currval.

Syntax : SequenceName.Currval
Example : Select SeqDno.Currval from Dual;

Rules For Using Nextval And Currval

Nextval and Currval may be used within

The select clause of a Select Statement.
The values list of an insert statement.
The set clause of an update statement.
Outermost select of a subquery.

Nextval and Currval may not be used within

Within the select list of a view.
With the keyword distinct.
With the Order by, Group by or Having clauses of a select statement.
With the set operators Union, Intersect, and Minus.
Within a subquery.





Altering A Sequence :

Syntax: Alter Sequence SeqName
Increment by n
Minvalue n
Maxvalue n
Cycle/Nocycle

In Alter Sequnce command it is not possible to alter the startwith option specified during sequence creation. Alter Sequence command does not effect the numbers that are already generated by the sequence but effects only the values that are going to be generated by the sequence.

Deleting A Sequence :

Syntax : Drop Sequence SeqName;
Example : Drop Sequence DnoSeq;
====================================================================
P S E U D O C O L U M N S


Pseudo columns are the columns that are not present in a table but appears to the user as if they are the columns of a table. Pseudo columns in SQL are as follows.

Sysdate
NextVal
CurrVal
User : Displays the current user name.
UID : Oracle assigns a unique ID to every user during creation of user. The pseudo column UID is used to get the unique ID assigned by Oracle to the current user.
ROWID : Oracle assigns a unique rowid to every row inserted in to a table. The pseudo column ROWID is used to get the unique ID assigned to the row of a table.
ROWNUM : Oracle assigns a number to every row of a table during its selection. The pseudo column ROWNUM is used to get the unique row number assigned by Oracle to the row of a table.
LEVEL : Level pseudo column is used to get the level number of a row during tree walking.

====================================================================

C L U S T E R S

Clustering is a method of storing tables that are intimately related and often joined together into the same area on disk. The Cluster key is the column or columns by which tables are usually joined in a query.

Creating A Cluster : A cluster is created by using the following syntax.

Syntax : Create CLUSTER CLUSTERNAME
(ColName Datatype(Size), ColName Datatype(size),….);

Example : Create Cluster STU_CLU (Sno Number(5));

After creating the cluster, a table can be created within that cluster as follows.

Create Table STUDENT
(Sno Number(5) Primary key,
Sname Varchar2(10),
Fee Number(10)
) CLUSTER STU_CLU(Sno);

To place tables on desired cluster we have to specify the cluster name on which we want to place the cluster at the end of create table statement after closing the parantheses. Along with cluster name we must also specify the cluster key column. In the above create table statement, student table is placed on cluster with name stu_clu. Like this you can place all related tables of student on same cluster. This will improve the database performance during data retrieval from these tables because all tables are stored in the same area on disk.

DML operations are not possible on a table that is created by using cluster option until an index is created on that cluster. Index is created on a cluster by using the following syntax.

Syntax : Create Index Indexname On Cluster Clustername
Example : Create Index STUCLU_IDX on Cluster STU_CLU;


Deleting A Cluster :

Syntax : Drop cluster clustername;
Example : Drop Cluster STU_CLU;

Note : Oracle will not allow the deletion of clusters that contain tables. Hence to delete a cluster we must first delete all tables created on that cluster.

O N D E L E T E C A S C A D E

We have created two tables Student and Marks by giving primary key constraint to Sno column of Student table and foreign key constraint on sno column of marks table that refers the Sno column of Student table. Now the student table is called as “Master Table” and Marks table is called as “Detailed Table”. Now if we want to delete a row from student table, which is having a dependent row or rows in Marks table will generate an error.

If we want to delete the dependent rows in foreign key table automatically when user is deleting the corresponding primary key row then “ON DELETE CASCADE” option is specified at the end of foreign key of Marks table.
Example :

Create Table Marks
( Sno Number(5) Constraint Sno_Fk References Student(Sno) On Delete Cascade,
M1 Number(5),
M2 Number(5),
Tot Number(6));