Database Objects in Oracle:
There are five data base objects covered in this course: table, view, index, sequence, and
synonym. As you know, the table is the basic unit of storage that contains records called rows
and fields called columns. A view is a logical entity that contains data from one or more
tables. An index is generated for a column to improve the search and retrieval of data or
to prevent duplication of key values. A sequence is the automatic generating of primary key
values. A synonym is used to give an object an alternative name.
SEQUENCE:
The sequence generator can be used to generate data for columns that are unique numbers in
sequence. An obvious application for the generator is the creation of primary keys. The
sequence generator allows you to set a value to increment by, a value to start with, a maximum
and minimum value and to set the option to cycle or nocycle and to cache or nocache. These
options will be discussed more as we look at examples. In the first example, I created a
sequence called id_no. This sequence will start with 1000, and increment by 1. The default
minimum value will be 1 and the maximum value will be 1999. The NOCACHE means that oracle will
not keep a certain number of values in memory during processing and the nocycle default option
means that when the maxvalue is reached, additional values will not be generated. Nocycle is
recommended when you are creating primary keys because of purging old rows. Notice in the
listings below, that last_number shows the next available number. Since the sequence has not
been used, the starting value is the next available number.
SQL CODE:
SQL> CREATE SEQUENCE id_no
2 INCREMENT BY 1
3 START WITH 1000
4 MAXVALUE 1999
5 NOCACHE
6 NOCYCLE;
Sequence created.
SQL> DESC USER_SEQUENCES;
Name Null? Type
------------------------------- -------- ----
SEQUENCE_NAME NOT NULL VARCHAR2(30)
MIN_VALUE NUMBER
MAX_VALUE NUMBER
INCREMENT_BY NOT NULL NUMBER
CYCLE_FLAG VARCHAR2(1)
ORDER_FLAG VARCHAR2(1)
CACHE_SIZE NOT NULL NUMBER
LAST_NUMBER NOT NULL NUMBER
SQL> SELECT * FROM USER_SEQUENCES;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ --------- --------- ------------ - - ---------- -----------
ID_NO 1 1999 1 N N 0 1000
Now that the sequence has been created, it can be used in tables. NEXTVAL can be used to
retrieve the next available sequence value and CURRVAL can be used to retrieve the current
sequence value. The way this works is when you use nextval a new sequence number is generated
and the current one is then placed in currval. From this explanation you can see that currval
does not contain a number before nextval is used.
In the example below, I first created a table called TESTSEQ to use in testing the sequence
object. Then I inserted data into the table. Note that when I inserted into ID, I said id_no
which is the name of the sequence followed by .NEXTVAL. At this point nextval has been used
so currval now contains a number - as you can see, they are both 1001.
SQL CODE:
SQL> CREATE TABLE TESTSEQ
2 (id VARCHAR2(4), name VARCHAR2(20), amt NUMBER);
Table created.
SQL> DESC testseq;
Name Null? Type
------------------------------- -------- ----
ID VARCHAR2(4)
NAME VARCHAR2(20)
AMT NUMBER
SQL> INSERT INTO testseq
2 VALUES(id_no.NEXTVAL, 'John Doe', 500);
1 row created.
SQL> SELECT * FROM testseq;
ID NAME AMT
---- -------------------- ---------
1000 John Doe 500
SQL> SELECT id_no.CURRVAL, id_no.NEXTVAL
2 FROM sys.dual;
CURRVAL NEXTVAL
--------- ---------
1001 1001
Note that I just messed up the sequence system by doing a select on nextval to show the
results. When I put things in the table, it will restart with 1002. If I had simply
referenced currval, I would not have messed up the table - the value of currval! Note also
that things like rollbacks also can mess up the system, once a number is used, it is used.
Now I am going to drop both the table and the sequence. As you can see, I need to deal with
them separately. This means the sequence number is not directly tied to the table and
therefore can infact be used with any table or multiple tables. Needless to say multiple
tables can lead to more gap problems when nextval is used. You can only view the next
available sequence if the sequence was created with nocache and you are looking at the
user_seqeunces table.
SQL CODE:
SQL> SELECT * FROM testseq;
ID NAME AMT
---- -------------------- ---------
1000 John Doe 500
1002 Ann Page 250
1003 Susan Smith 750
SQL> DROP TABLE testseq;
Table dropped.
SQL> SELECT * FROM USER_SEQUENCES;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ --------- --------- ------------ - - ---------- -----------
ID_NO 1 1999 1 N N 0 1004
SQL> DROP SEQUENCE ID_NO;
Sequence dropped.
A sequence can be altered using ALTER SEQUENCE, however only future sequence numbers are
altered and the alter cannot effect the start with option, or change the maxvalue to below
the current sequence number.
Below, I am starting another example. First, I created a new table and a new sequence.
Now, I am filling the table.
SQL CODE:
SQL> CREATE SEQUENCE dept_no
2 INCREMENT BY 2
3 START WITH 2
4 MAXVALUE 12;
Sequence created.
SQL> INSERT INTO testseq1
2 VALUES(dept_no.NEXTVAL, 'CIS' , 15000);
1 row created.
SQL> INSERT INTO testseq1
2 VALUES(dept_no.NEXTVAL, 'Payroll', 10000);
1 row created.
SQL> SELECT * FROM testseq1;
DEPT DEPTNAME BUDGET
---- ------------------------- ---------
2 CIS 15000
4 Payroll 10000
Now I am adding some more records to the table and then showing the results below. As you
can see, when I tried to add a record that would take me past the maximum value, I ran into
problems. I now need to alter the table to change the maxvalue.
SQL CODE:
SQL> SELECT * FROM testseq1;
DEPT DEPTNAME BUDGET
---- ------------------------- ---------
2 CIS 15000
4 Payroll 10000
6 Acct Pay 11000
8 Acct Pay 9000
10 Inventory 20000
12 Training 15000
6 rows selected.
SQL> INSERT INTO testseq1
2 VALUES(dept_no.NEXTVAL, 'Personnel', 6000);
INSERT INTO testseq1
*
ERROR at line 1:
ORA-08004: sequence DEPT_NO.NEXTVAL exceeds MAXVALUE and cannot be instantiated
SQL> ALTER SEQUENCE dept_no
2 MAXVALUE 100;
Sequence altered.
SQL> INSERT INTO testseq1
2 VALUES(dept_no.NEXTVAL, 'Personnel', 6000);
1 row created.
SQL> SELECT * FROM testseq1;
DEPT DEPTNAME BUDGET
---- ------------------------- ---------
2 CIS 15000
4 Payroll 10000
6 Acct Pay 11000
8 Acct Pay 9000
10 Inventory 20000
12 Training 15000
14 Personnel 6000
7 rows selected.
INDEXES:
Remember that when you define a primary key or a unique key a unique index is automatically
created to meet this definition. Non unique indexes are created using CREATE INDEX and there
purpose is to improve processing efficiency.
Indexes should be created on columns that are frequently referred to in the WHERE clause or in
a relational join condition, columns that have a lot of different values or that contain a lot
of null values, or where tables are very large and most queries will retrieve less than 2-4% of
the rows. A counter indication for creating indexes is when the table is frequently updated.
You can create an index on one column or multiple columns. Below I created two indexes, one
on department name alone and one on budget and deptname. I then showed the two indexes from
user_indexes and the columns used in the indexes from user_ind_columns. Finally, I dropped
the budget_deptname_idx index.
SQL CODE:
SQL> CREATE INDEX deptname_idx
2 ON testseq1(deptname);
Index created.
SQL> CREATE INDEX budget_deptname_idx
2 ON testseq1(budget, deptname);
Index created.
SQL> SELECT INDEX_NAME, UNIQUENESS FROM USER_INDEXES WHERE TABLE_NAME = 'TESTSEQ1';
INDEX_NAME UNIQUENES
------------------------------ ---------
BUDGET_DEPTNAME_IDX NONUNIQUE
DEPTNAME_IDX NONUNIQUE
SQL> SELECT INDEX_NAME, COLUMN_NAME FROM USER_IND_COLUMNS
2 WHERE TABLE_NAME = 'TESTSEQ1';
INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------
BUDGET_DEPTNAME_IDX BUDGET
BUDGET_DEPTNAME_IDX DEPTNAME
DEPTNAME_IDX DEPTNAME
SQL> DROP INDEX BUDGET_DEPTNAME_IDX;
Index dropped.
SYNONYMS:
Synonyms create another name for an object. If you want access to a table owned by another
user, you have to put the name of the user followed by a period followed by the table name.
If you use a synonym you can simply refer to that name. When creating a synonym, the database
administrator can use the DBA privileges to make the synonym public so it is available to all
users. As you can see, I am working as scott/tiger and do not have these privileges.
SQL CODE:
SQL> CREATE SYNONYM ts1
2 FOR testseq1;
Synonym created.
SQL> DROP SYNONYM ts1;
Synonym dropped.
SQL> CREATE PUBLIC SYNONYM ts1
2 FOR testseq1;
CREATE PUBLIC SYNONYM ts1
*
ERROR at line 1:
ORA-01031: insufficient privileges