oracle 071,Oracle_071_lesson_p13

创建序列、同义词、索引

create sequence

CREATE SEQUENCE [ schema. ] sequence

[ { START WITH|INCREMENT BY } integer

| { MAXVALUE integer | NOMAXVALUE }

| { MINVALUE integer | NOMINVALUE }

| { CYCLE | NOCYCLE }

| { CACHE integer | NOCACHE }

| { ORDER | NOORDER }

];

CREATE SEQUENCE dept_deptid_seq

START WITH 280

INCREMENT BY 10

MAXVALUE 9999

NOCACHE

NOCYCLE;

INSERT INTO departments(department_id,

department_name, location_id)

VALUES (dept_deptid_seq.NEXTVAL,

'Support', 2500);

SELECT dept_deptid_seq.CURRVAL

FROM dual;

NEXTVAL 下一个值， CURRVAL 当前值

第一次先执行NEXTVAL，才能对CURRVAL取值。

select detpno.currval from dual;

取值只跟当前会话有关。

示例：

CREATE SEQUENCE s1 START WITH 1;

CREATE TABLE emp (a1 NUMBER DEFAULT s1.NEXTVAL NOT NULL, a2 VARCHAR2(10));

INSERT INTO emp (a2) VALUES (‘john');

INSERT INTO emp (a2) VALUES (‘mark');

SELECT * FROM emp;

Caching sequence values in memory gives faster access to those values.

Gaps in sequence values can occur when(序列不连续有以下3种情况):

1、A rollback occurs

2、The system crashes 如：中断oracle , ps -ef |grep smon -> kill -9 进程号 。 shutdown immediate 关闭数据库

3、A sequence is used in another table

修改序列 alter sequence

ALTER SEQUENCE dept_deptid_seq

INCREMENT BY 20

MAXVALUE 999999

NOCACHE

NOCYCLE;

DROP SEQUENCE dept_deptid_seq;

start with 起始值不能改

循环到最大值时，起始值再从1开始

要想改起始值，只能删除序列重建

DESCRIBE user_sequences

SELECT sequence_name, min_value, max_value,

increment_by, last_number

FROM user_sequences;

查看序列信息： select * from user_sequences;

监控last_number和最大值的差距，差距越小越要注意 ，特别是有序列作为外键时,很重要。

synonyms 同义词

创建同义词

CREATE [PUBLIC] SYNONYM synonym

FOR object;

DESCRIBE user_synonyms;

SELECT *

FROM user_synonyms;

示例：

create synonym test for v$SESSION;

只有别名不会分配存储单元，默认为私有。

加public 则是全局公有。

示例：

create public synonym test2 for emp;

create departments for hr.department ;

truncate table emp; 删除表的行内容，表结构还存在。

drop synony emp; 删除同义词。

创建索引 create index

1、是用户对象

2、提高数据库性能

3、减少I/O

4、依赖于表

5、增删改表的时候，系统自动更新索引

自动会创建索引，在主键和唯一键时

Automatically: A unique index is created automatically when you define a PRIMARY KEY or UNIQUE constraint in a table definition.

手动建索引

Manually: You can create unique or nonunique index on columns to speed up access to the rows.

create index emp_last_name_idx

on table (column1,column2.......);

DROP INDEX index;

drop index emp_last_name_idx;

alter index emp_last_name_idx invisible ; 索引不可见；

alter index emp_last_name_idx visible; 索引可见；

CREATE TABLE NEW_EMP

(employee_id NUMBER(6) PRIMARY KEY USING INDEX

(CREATE INDEX emp_id_idx ON NEW_EMP(employee_id)),

first_name VARCHAR2(20),

last_name VARCHAR2(25));

SELECT INDEX_NAME, TABLE_NAME

FROM USER_INDEXES

WHERE TABLE_NAME = 'NEW_EMP';

CREATE INDEX emp_id_name_ix1

ON employees(employee_id, first_name);

ALTER INDEX emp_id_name_ix1 INVISIBLE;

CREATE BITMAP INDEX emp_id_name_ix2

ON employees(employee_id, first_name);

DESCRIBE user_indexes;

DESCRIBE user_ind_columns;

SELECT index_name, column_name,table_name FROM user_ind_columns WHERE index_name = 'LNAME_IDX';