Oracle序列、索引、同义词操作讲解

序列可以生成一连串有序的数字,用于生成表的主键

创建序列

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;
(0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐