A database object is anything that has a name and a defined structure. Other database objects that are used in Oracle 10g are sequences, indexes, and synonyms.
- A sequence will generate sequential integers that can be used to assist with internal controls or serve as primary keys for tables.
- A database index serves the same basic purpose as an index in a book.
- A synonym is a nick name that is given to an object that may have a complex name. There are two types of synonyms, one being a private, which is used only by an individual on objects they own, and the other being public, which is used by others to access another’s database objects.
Sequences
The sequence will generate integers that are unique and can be used as a primary key. When using sequences, they have no correlation between the assigned number and the entity that it represents. Sequences are not assigned to a specific coumn or table. They are independent which means they can be used by different users to generate values.
CREATE SEQUENCE sequencename
[ INCREMENT BY value ]
[ START WITH value ]
[ { MAXVALUE value | NOMAXVALUE } ]
[ { MINVALUE value | NOMINVALUE } ]
[ { CYCLE | NOCYCLE } ]
[ { ORDER | NOORDER } ]
[ { CACHE value | NOCACHE } ];
When naming a sequence, the suffix for a sequence is _seq. Creating a sequence is as simple as calling the keywords and giving the sequence a name. The other options are defaulted by oracle. The INCREMENT BY and START WITH are both defaulted to one.
Continuing with the MINVALUE and MAXVALUE, if you are incrementing then the MINVALUE clause is not necessary. As you decrement, logic would say that you do not need the MAXVALUE clause. However, this is not the case. The MAXVALUE and START value are going to be the same in a situation where you decrement. If the MINVALUE and MAXVALUE are not specified then Oracle will use the NOMINVALUE and NOMAXVALUE. The highest value that a sequence can be increased to is 10^27 and the lowest value that you can decrement to is 10^26.
Typically the CYCLE and NOCYCLE would be used in conjunction with a MAXVALUE or MINVALUE clause is used. As soon as Oracle hits the max or min, it will start over if the CYCLE clause is used or generate an error if the NOCYCLE clause is used and the numbers are used up.
ORDER and NOORDER is used when you want the numbers to be return in the same order they were given out in. If this is not explicitly defined, then the NOORDER will be used.
Generating sequence values can be slow. By caching precreated numbers this helps the database from slowing down. By default Oracle will create 20 cached values. When these numbers are in memory they are already generated. Therefore by already being generated they can not be changed. If the system crashes, or users do not use the values, those values are lost.
After you create a sequence successfully, you can go back to check to make sure that the sequence was created successfully Oracle can be queried to verify the sequence:
SELECT object_name
FROM user_objects
WHERE object_type = ‘SEQUENCE’;
This will list the name(s) of the sequences that were created. To get information about sequences that are in Oracle execute the following SQL statement:
SELECT *
FROM user_sequences;
The above statement will display the name of the sequences, minimum values, maximum values, what it’s incremented by, if it cycles, ordered, the cache size and the last number. The last number is the number that will be used first.
Using The Sequence Values
To us the sequence values you must can use one of the two pseudocolumns NEXTVAL and CURRVAL. As I mentioned above when you query the database to view the attributes of a sequence, the value in the ‘LAST_NUMBER’ is going to be the number that will be used in the following query as an order number:
CREATE SEQUENCE orders_order#_seq
MINVALUE 1000;
INSERT INTO ORDERS( order#, customer#, orderdate, shipdate, shipstreet, shipcity, shipstate, shipzip)
VALUES(orders_order#_seq.nextval, 1000, ’21-Feb-07’, NULL, ‘12345 First Street’, ‘Milwaukee’, ‘WI’, 53225);
If the insert statement is the first insert using the orders_order#_seq sequence, the value in the order# column will have ‘1000’ in it. After the insert statement is done executing, the number that will be used for NEXTVAL will be 1001. If we were to look at the CURRVAL right after executing the insert statement we will see that it’s value is 1000. When a user logs into Oracle, the CURRVAL column has no value in it.
Altering a Sequence
Changes can be made to a sequence by using the ALTER SEQUENCE command. Any changes to a sequence will affect the newly generated numbers and ignore all previously generated numbers. The only clause that can not be changed is the START WITH clause. If this clause needs to be changed then the sequence must be dropped and recreated.
ALTER SEQUENCE sequencename
[ INCREMENT BY value ]
[ { MAXVALUE value | NOMAXVALUE } ]
[ { MINVALUE value | NOMINVALUE } ]
[ { CYCLE | NOCYCLE } ]
[ { ORDER | NOORDER } ]
[ { CACHE value | NOCACHE } ];
To drop a sequence it’s very straight foreward:
DROP SEQUENCE sequencename;
Working with Indexes
An index is a database object that stores a map of a column values and the ROWID of matching table rows. Creating indexes can be done implicitly or explicitly. Oracle creates an index whenever a primary key or unique constrain is created for a column. Using indexes cause the system to slow down because when a row is inserted, updated, or deleted, Oracle not only has to modify the table of which that column was located in, but also the index table.
CREATE UNIQUE INDEX indexname
ON tablename(columnname) ;
This will create an unique index on a column. Say if you wanted to have a users table. The column name of user_name is indexed and all values in the column are unique. By default when an index is created in ascending order. In order to change the order of the values to descending order:
CREATE INDEX indexname
ON tablename( columnname [ DESC ] );
As Oracle searches through the indexed columns, any NULL values are ignored. When you search for a NULL value in an index, the whole table will be scanned. Indexes can also include multiple columns. The maximum number of columns that can be indexed at one time is 32.
CREATE INDEX indexname
ON tablename( col1, col2, … );
Another time of index is a Bitmap index. This is useful for improving queries on olumns that aren’t selected a lot, low cardinality, or have a small number of values. The way that this index works is by using a two dimensional array. A bitmap index comes in handy when performing compound conditions such as AND and OR.
CREATE BITMAP INDEX indexname
ON tablename(columname);
Index Organized Tables (IOT) which is a structure that stores the contents of the entire table in a B-tree index with rows sorted in the primary key value order. By using this kind of index search and ordering can be improved. The major advantage of this is that only one object is needed to house the index and data value. Each row in an IOT must have a primary key because it uses that as the row identifier. Creating a IOT, you do this when you execute the
CREATE TABLE with an ORGANIZATION INDEX clause.
CREATE TABLE tablename
( col1, col2, ...)
ORGANIZATION INDEX
To verify that an index was created you can use the data dictionary to make sure everything was done properly.
SELECT table_name, index_name, index_type
FROM user_indexes
WHERE table_name = ExampleTable;
In order to alter an index you must drop it and recreate it. Once an index has been created it's set in stone. To drop an index issue the following command:
DROP INDEX indexname;
Synonyms
When a table name is fairly long and descriptive, it's hard to remember the name. This is where synonyms come into play. You don't have to use the full name of an object when you create a synonym for it.
CREATE [ PUBLIC ] SYNONYM synonymname
FOR objectname;
The synonymname is another name for objectname. PUBLIC comes into play when you want the synonymname to be available to other users. When a public synonym is created only the DBA can remove it. Dropping a Synonym is much like dropping an index.
DROP [ PUBLIC ] SYNONYM synonymname;