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;