创建序列、同义词、索引
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';
