1 sequence
1) automatically generatess unique numbers
is a sharable object
is typically used to create a primary key value
replaces applicaition code
speeds up the efficiency of accessing sequence
create sequence sequence
[increment by n]
[start with n]
[{maxvalue n |nomaxvalue}]
[{minvalue n |nominvalue}]
[{cycle|nocycle}]
[{cache n |nocache}]
create sequence dept_deptin_seq
increment by 10
start with 120
maxvalue 9999
nocache
nocycle
2) Confirming Sequences
verify your sequence values in the user_sequences data dictionary table
select sequence_name,min_value,max_value,increment_by,last_number
from user_sequences;
the last_number display the next available sequence number if nocache is specified
3)nextval and currval Pseudocolumns
--nextval return thee next available sequence value,it return a unique value every time
it si referenced,even for different ueer;
--currval obtains the current sequence value;
--nextval must be issued for that sequence before curval contains a value;
4) Using a Sequence
-- Caching sequence values in the memory give faster access to these values;
-- Gaps in sequence value can occur when
a rollback occurs
b the system crashes
c A sequence us used in another table;
5) alter sequence test increment by 10;
you can change all properties of the sequence except the start with .
6) remove sequence
drop sequence test;
2 index
1) how are indexes created
Automatically : a unique index is created automatically when you create primary key or
unique constraint in a table definition,
Manually: user can create nounique index on column to speed up access to the rows.
create index testindex on autoer(lanme);
2) When to Create an index
ypu should create an index if:
. a column contains a wide range of values
. a column contains a large number of null values
. one or more columns are frequently used together in where clause or a join condition;
. The table is large and most queries are expected to retrieve less than 2 to 4 percent
of the rows;
3) When not to create an index
this usually not worth creating an index if:
. the table is small
. The columns are not often used as a condition in the query.
. Most queries are expected to retrieve more than 2 to 4 percent of the rows in the
table
. the indexed columns are referenced as part of an expression.
4)Confirming indexes
. The user_indexes data dictionary view contains the name of the index and tis uniquess
. the user_ind_columns view contains the index name,the table name,and the column name.
select ic.index_name,ic_column_name,ic.column_position,ic_col_pos,ix.uniqueness
from user_indexed ix,user_ind_columns ic
where ic.index_name=ix.index_name
and ic.table_name='employees';
5)基于函数的索引
. a function-based index is an index based on expressions
. The index expression is built form table columns,constraints,SQL functions and user-
defined functions
create index testindex2
on autors (upper(au_fname));
select * from authors
where upper(au_fname) like 'B%';
6) remoe index
drop index index_name;
3 synonyms
Simplify access to objects by creating a synonym
. Ease referring to a table ownerd by anther user
. Shorten lengthy object names;
create [publi] synonym synonym for object;