Oracle序列、索引、同义词操作讲解
程序员文章站
2022-06-21 16:41:57
序列可以生成一连串有序的数字，用于生成表的主键
创建序列
create sequence s1;
--**
select * from user_sequences;
sequence...
序列可以生成一连串有序的数字，用于生成表的主键
创建序列
create sequence s1;
--** select * from user_sequences;
sequence_name min_value max_value increment_by c o cache_size last_number ------------------------------ ---------- ---------- ------------ - - ---------- ----------- s1 1 1.0000e+28 1 n n 20 1 elapsed: 00:00:00.01
序列存在两个伪列，一个是currval,一个是nextval，当一个序列创建后第一次被使用的时候，只有nextval
select s1.currval from dual;
select s1.currval from dual
*
error at line 1:
ora-08002: sequence s1.currval is not yet defined in this session
elapsed: 00:00:00.02
select s1.nextval from dual;
nextval
----------
1
elapsed: 00:00:00.01
select s1.currval from dual;
currval
----------
1
elapsed: 00:00:00.00
alter table e1 add(no number(4));
insert into e1(no) values(s1.nextval);
select * from e1;
ename sal comm dname no
---------- ---------- ---------- -------------- ----------
smith 800 research
allen 1600 300 sales
ward 1250 500 sales
jones 2975 research
martin 1250 1400 sales
blake 2850 sales
clark 2450 accounting
scott 3000 research
king 5000 accounting
turner 1500 0 sales
adams 1100 research
james 950 sales
ford 3000 research
miller 1300 accounting
tt 1230 sales
aa 112 sales
2
17 rows selected.
elapsed: 00:00:00.01
修改序列
alter sequence s1 minvalue 2;
sequence altered. elapsed: 00:00:00.02
设置序列的最小值不能超过当前序列的值
alter sequence s1 maxvalue 100;
alter sequence s1 increment by 2 cycle cache 10;
--** select * from user_sequences;
sequence_name min_value max_value increment_by c o cache_size last_number ------------------------------ ---------- ---------- ------------ - - ---------- ----------- s1 2 100 2 y n 10 4 elapsed: 00:00:00.00
删除序列
drop sequence s1;
索引：是用来加速数据访问的一种对象，能加速的操作包括select update delete，但是索引不能对insert进行加速，索引的存在一定会降低insert的效率，索引的使用默认是由oracle优化器自动执行的
索引的创建
自动创建：当建立表的时候有主键约束或唯一键约束的时候，oracle会自动创建该列的索引
手动创建
打开执行计划跟踪
--** set autot trace exp
select * from emp where ename='scott';
elapsed: 00:00:00.00
execution plan
----------------------------------------------------------
plan hash value: 3956160932
--------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
--------------------------------------------------------------------------
| 0 | select statement | | 1 | 38 | 3 (0)| 00:00:01 |
|* 1 | table access full| emp | 1 | 38 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
1 - filter("ename"='scott')
通过观察发现是通过全表扫描的方式查询的数据
create index i_emp_ename on emp(ename);
再执行刚才的查询
select * from emp where ename='scott';
elapsed: 00:00:00.00
execution plan
----------------------------------------------------------
plan hash value: 1237151973
-------------------------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
-------------------------------------------------------------------------------------------
| 0 | select statement | | 1 | 38 | 2 (0)| 00:00:01 |
| 1 | table access by index rowid| emp | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | index range scan | i_emp_ename | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
2 - access("ename"='scott')
什么情况下建立索引：行比较多，列下的取值比较多，可以建立索引
什么情况下不需要建立索引：表比较小，行比较少，表需要大量insert操作
删除索引
drop index i_emp_ename;
同义词：对象的别名
conn system/dba
select * from emp;
select * from emp
*
error at line 1:
ora-00942: table or view does not exist
elapsed: 00:00:00.00
发现在system用户下没有emp这张表，那么我们通过用户模式名+表名的形式访问
select * from oracle.emp;
empno ename job mgr hiredate sal comm deptno
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 smith clerk 7902 17-dec-80 800 20
7499 allen salesman 7698 20-feb-81 1600 300 30
7521 ward salesman 7698 22-feb-81 1250 500 30
7566 jones manager 7839 02-apr-81 2975 20
7654 martin salesman 7698 28-sep-81 1250 1400 30
7698 blake manager 7839 01-may-81 2850 30
7782 clark manager 7839 09-jun-81 2450 10
7788 scott analyst 7566 19-apr-87 3000 20
7839 king president 17-nov-81 5000 10
7844 turner salesman 7698 08-sep-81 1500 0 30
7876 adams clerk 7788 23-may-87 1100 20
7900 james clerk 7698 03-dec-81 950 30
7902 ford analyst 7566 03-dec-81 3000 20
7934 miller clerk 7782 23-jan-82 1300 10
14 rows selected.
elapsed: 00:00:00.01
如果不希望通过输入用户模式名来访问emp这张表，可以建立同义词
私有同义词：建立的同义词只有自己能用
create synonym emp for oracle.emp;
select * from emp;
empno ename job mgr hiredate sal comm deptno
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 smith clerk 7902 17-dec-80 800 20
7499 allen salesman 7698 20-feb-81 1600 300 30
7521 ward salesman 7698 22-feb-81 1250 500 30
7566 jones manager 7839 02-apr-81 2975 20
7654 martin salesman 7698 28-sep-81 1250 1400 30
7698 blake manager 7839 01-may-81 2850 30
7782 clark manager 7839 09-jun-81 2450 10
7788 scott analyst 7566 19-apr-87 3000 20
7839 king president 17-nov-81 5000 10
7844 turner salesman 7698 08-sep-81 1500 0 30
7876 adams clerk 7788 23-may-87 1100 20
7900 james clerk 7698 03-dec-81 950 30
7902 ford analyst 7566 03-dec-81 3000 20
7934 miller clerk 7782 23-jan-82 1300 10
14 rows selected.
elapsed: 00:00:00.00
查看用户下的同义词
--** select * from user_synonyms;
synonym_name table_owner table_name db_link
------------------------------ ------------------------------ -------------------- --------------------
syscatalog sys syscatalog
catalog sys catalog
tab sys tab
col sys col
tabquotas sys tabquotas
sysfiles sys sysfiles
publicsyn sys publicsyn
product_user_profile system sqlplus_product_prof
ile
emp oracle emp
9 rows selected.
公有同义词：所有用户都可以使用
drop synonym emp;
create public synonym emp for oracle.emp;
任何用户都可以通过emp这个同义词访问oracle.emp这张表
私有同义词不能跟现有对象名字重复，而公有同义词可以，在查询的时候优先查询对象，之后才是公有同义词
删除同义词
drop public synonym emp;