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);
SELECTdept_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 进程号 。 shutdownimmediate 关闭数据库
3、A sequence is used in another table
修改序列>
ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE;
DROP SEQUENCE dept_deptid_seq;
startwith起始值不能改
循环到最大值时，起始值再从1开始
要想改起始值，只能删除序列重建
DESCRIBE user_sequences
SELECTsequence_name, min_value, max_value,
increment_by, last_number
FROM user_sequences;
查看序列信息： select*fromuser_sequences;
监控last_number和最大值的差距，差距越小越要注意 ，特别是有序列作为外键时,很重要。
synonyms同义词
创建同义词
CREATE SYNONYM synonym
FOR object;
DESCRIBE user_synonyms;
SELECT *
FROM user_synonyms;
示例：
createsynonymtestforv$SESSION;
只有别名不会分配存储单元，默认为私有。
加public则是全局公有。
示例：
createpublicsynonymtest2foremp;
createdepartmentsfor hr.department ;
truncatetableemp;删除表的行内容，表结构还存在。
dropsynonyemp;删除同义词。
创建索引createindex
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.
createindexemp_last_name_idx
ontable (column1,column2.......);
DROP INDEX index;
dropindexemp_last_name_idx;
alterindex emp_last_name_idxinvisible ;索引不可见；
alterindexemp_last_name_idxvisible;索引可见；
CREATE TABLE NEW_EMP
(employee_id NUMBER(6) PRIMARY KEY USING INDEX
(CREATE INDEX emp_id_idx ON NEW_EMP(employee_id)),
first_nameVARCHAR2(20),
last_name VARCHAR2(25));
SELECT INDEX_NAME, TABLE_NAME
FROM USER_INDEXES
WHERETABLE_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_nameFROM user_ind_columnsWHEREindex_name = 'LNAME_IDX';
页:
[1]