some database object

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;

posted on 2006-09-29 11:31 康文 阅读(184) 评论(0)  编辑  收藏 所属分类: 数据库


只有注册用户登录后才能发表评论。


网站导航:
 
<2006年9月>
272829303112
3456789
10111213141516
17181920212223
24252627282930
1234567

导航

统计

常用链接

留言簿(1)

随笔分类

随笔档案

文章档案

搜索

最新评论

阅读排行榜

评论排行榜