Oracle数据库对象
视图物化视图 序列同义词索引注:以下数据库对象中,物化视图、序列、同义词为Oracle数据库特有
视图
含义:视图是一种数据库对象,是从一个或者多个数据表或视图中导出的虚表,视图所对应的数据并不真正地存储在视图中,而是存储在所引用的数据表中,视图的结构和数据是对数据表进行查询的结果。
使用视图的优点:
简化数据操作:视图可以简化用户处理数据的方式。着重于特定数据:不必要的数据或敏感数据可以不出现在视图中。视图提供了一个简单而有效的安全机制,可以定制不同用户对数据的访问权限。提供向后兼容性:视图使用户能够在表的架构更改时为表创建向后兼容接口。
语法:
CREATE [OR REPLACE] [FORCE] VIEW view_name
AS SELECT语句
[WITH CHECK OPTION ]
[WITH READ ONLY]
--创建简单视图create view view_owners1 asselect * from t_owners where ownertypeid=1;--查询简单视图select * from view_owners1 where addressid=1;--修改视图数据update view_owners1 set name='齐天' where id=1;commit;select * from t_owners;--待检查约束的视图create view view_address2 asselect * from t_address where areaid = 2with check option;select * from view_address2;--无法修改成功的语句,因为该视图的条件是areaid=2update view_address2 set areaid=3 where id = 4;--只读视图create or replace view view_owners1 asselect * from t_owners where ownertypeid=1with read only;--修改只读视图数据 不能修改成功update view_owners1 set name='大圣' where id = 1 ;--创建带错误的视图create force view view_test asselect * from t_test;--只是强行保存了语句,还是不能使用select * from view_test;--复杂视图-多表关联create or replace view view_owners asselect ow.id 业主编号, ow.name 业主名称,ot.name 业主类型 from t_owners ow,t_ownertype otwhere ow.ownertypeid = ot.id;--查询复杂视图 (多表关联)select * from view_owners where 业主类型='居民';--修改复杂视图的数据 可以修改成功update view_owners set 业主名称='齐天大圣' where 业主编号=4;--不能修改成功update view_owners set 业主类型='商业' where 业主编号=4;--键保留表 :把主键保留的那个表 才可以修改数据--聚合统计的复杂视图drop view view_account;create view view_accountsum asselect year,month ,sum(money) money from t_account group by year , monthorder by year, month;select * from view_accountsum where year = '' and month = '03';--不能修改update view_accountsum set money = 10000 where year='' and month = '03';
物化视图
含义:视图是一个虚拟表(也可以认为是一条语句),基于它创建时指定的查询语句返回的结果集。每次访问它都会导致这个查询语句被执行一次。为了避免每次访问都执行这个查询,可以将这个查询结果集存储到一个物化视图(也叫实体化访问都执行这个查询,可以将这个查询结果集存储到一个物化视图(也叫实体化视图)。
物化视图与普通的视图相比的区别是物化视图是建立的副本,它类似于一张表,需要占用存储空间。而对一个物化视图查询的执行效率与查询一个表是一样的。比普通视图的查询效率要快。
语法:
CREATE METERIALIZED VIEW view_name
【BUILD IMMEDIATE | BUILD DEFERRED】默认立即生成数据
REFRESH 【 FAST | COMPLETE | FORCE】默认FORCE刷新模式
【ON [COMMIT | DEMAND ] 默认手动提交 |
START WITH (start_time) NEXT(next_time)】
AS
查询语句
START WITH:自定自动刷新的起点时间
NEXT:指定自动刷新的间隔,start with and next合起来确定下次刷新的时间,如果在忽略了sart with ,只有next,那么刷新时间依据物化视图创建的时间和next来确定下次刷新的时间,如果只有start with 没有next,那么就会刷新一次。
注意:创建增量刷新的物化视图,必须:
创建物化视图中涉及表的物化视图日志。在查询语句中,必须包含所有表的 rowid ( 以 rowid 方式建立物化视图日志)或主键。
--物化视图 提高查询效率,但会增加空间占用--COMPLETE 完全刷新 把原来的物化视图删除,再重新查询一遍--FAST增量更新 在原有视图上增加更新,有条件限制--FORCE (缺省)自动选择 自动选择完全还是增量更新--ON COMMIT 在基表做提交操作时刷新物化视图--ON DEMAND (缺省)手动刷新--创建手动刷新的物化视图create materialized view mv_address1 as select ad.id,ad.name,ar.name arname from t_address ad,t_area arwhere ad.areaid = ar.id;drop materialized view mv_address1;--查询物化视图select * from mv_address1;--向基表插入数据insert into t_address values(9,'东八儿',2,2);commit;--查询基表数据select * from t_address;--此时物化视图没有添加刚刚在基表中添加的数据--执行下列语句来进行刷新 PL/SQLbegin--此语句是Oracle内置的存储过程,C是完全刷新DBMS_MVIEW.refresh('MV_ADDRESS1','C');end;/*命令行执行以下语句也能刷新SQL> EXEC DBMS_MVIEW.refresh('mv_address1','c')PL/SQL procedure successfully completed*/--创建自动刷新的物化视图 --基表发生commit操作自动刷新物化视图create materialized view mv_address2 refresh on commitasselect ad.id,ad.name,ar.name arname from t_address ad,t_area arwhere ad.areaid = ar.id; --查询物化视图select * from mv_address2;--向基表中插入数据insert into t_address values(10,'南八儿',2,2);commit;--创建时不生成数据的物化视图create materialized view mv_address3 build deferredrefresh on commitasselect ad.id,ad.name,ar.name arname from t_address ad,t_area arwhere ad.areaid = ar.id; --查询物化视图select * from mv_address3;insert into t_address values(11,'北八儿',2,2);commit;--因为创建时没数据,第一次必须手动执行刷新,以后会自动刷新beginDBMS_MVIEW.refresh('MV_ADDRESS3','C');end;--创建增量刷新的物化视图--前提是必须创建物化视图日志:记录基表发生了哪些变化,用这些记录去更新物化视图--with后面可以是主键或者rowidcreate materialized view log on t_address with rowid;create materialized view log on t_area with rowid;--创建物化视图中的语句中,必须有基表的ROWID或主键create materialized view mv_address4 refresh fastasselect ad.rowid adrowid , ar.rowid arrowid,ad.id,ad.name,ar.name arname from t_address ad,t_area arwhere ad.areaid = ar.id;select * from mv_address4;--插入数据insert into t_address values(12,'西九儿',2,2);commit;insert into t_address values(13,'西十儿',2,2);commit;--删除数据delete from t_address where id = 12;commit;--手动刷新,刷新后就就看不见物化视图日志的内容了,也就是将日志中的内容转到了物化视图中.begindbms_mview.refresh('mv_address4','c'); end;
序列
含义:序列是 ORACLE 提供的用于产生一系列唯一数字的数据库对象 。
创建简单序列语法:create sequence 序列名称
通过序列的伪列来访问序列的值
NEXTVAL
返回序列的下一个值
CURRVAL
返回序列的当前值
注意:我们在刚建立序列后,无法提取当前值,只有先提取下一个值时才能再次提取当前值。
创建复杂序列语法:
CREATE SEQUENCE sequence //创建序列名称
[INCREMENT BY n] //递增的序列值是n 如果n是正数就递增,如果是负数就递减 默认是1
[START WITH n] //开始的值,递增默认是minvalue 递减是maxvalue [{MAXVALUE n | NOMAXVALUE}] //最大值
[{MINVALUE n | NOMINVALUE}] //最小值
[{CYCLE | NOCYCLE}] //循环/不循环
[{CACHE n | NOCACHE}];//分配并存入到内存中
--以下语句由WATERUSER用户执行--创建简单序列create sequence seq_test;--查询序列的下一个值select seq_test.nextval from dual;--查询序列的当前值select seq_test.currval from dual;--有最大值的非循环序列create sequence seq_test1maxvalue 20;--超过最大值会报错select seq_test1.nextval from dual;create sequence seq_test2increment by 10start with 10maxvalue 100;select seq_test2.nextval from dual;create sequence seq_test3increment by 10start with 10minvalue 5maxvalue 100;--最小值作用没体现,还是从10开始select seq_test3.nextval from dual;--循环的序列--一次循环的值的个数要>缓存的值的个数(默认缓存20个)drop sequence seq_test4;create sequence seq_test4increment by 10start with 10minvalue 10maxvalue 210cycle;--多一个就行create sequence seq_test5increment by 10start with 10minvalue 10maxvalue 201cycle;select seq_test5.nextval from dual;create sequence seq_test6increment by 10start with 10minvalue 10maxvalue 100cyclecache 5;--一次缓存的数有多少 , cache值*增长值--一次循环的值不能小于 , 一次缓存的数--不循环的话就无上述约束create sequence seq_test7increment by 10start with 10minvalue 10maxvalue 100cache 20;----序列不真的属于任何一张表,但是可以逻辑和表做绑定。----序列:默认从1开始,依次递增,主要用来给主键赋值使用。----dual:伪表,只是为了补全语法,没有任何意义。create sequence s_person;select s_person.nextval from dual;----添加一条记录insert into person (pid, pname) values (s_person.nextval, '小明');commit;select * from person;
同义词
含义:同义词实质上是指定方案对象的一个别名。
同义词允许基对象重命名或者移动,这时,只需对同义词进行重定义,基于同义词的应用程序可以继续运行而无需修改。
语法:
create [public] SYNONYM 同义词名字 for object;
object 表示表, 视图 ,序列等我们要创建同义词的对象的名称。
--创建私有同义词create synonym owner for t_owners;select * from owner;--创建公有同义词create public synonym owners2 for t_owners;select * from owners2;--收费员表create table t_ttt(id number,name varchar(30) --varchar是varchar2的同义词);--以下语句由SYSTEM用户执行--私有同义词其他用户不能直接访问select * from owner;--因为有DBA权限所以可以调wateruser用户来访问select * from wateruser.owner;select * from wateruser.t_owners;--公有同义词 可以直接访问select * from owners2;
索引
索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低
i/o 次数从而提高数据访问性能 。
普通索引:
create index 索引名称 on 表名 列名
唯一索引:
create unique index 索引名称 on 表名 列名
复合索引:
create index 索引名称 on 表名 (列名 列名…)
反向键索引:
create index 索引名称 on 表名 列名 reverse
应用场景:当某个字段的值为连续增长的值,如果构建标准索引,会形成歪脖子树。这样会增加查询的层数,性能会下降。建立反向键索引,可以使索引的值变得不规则,从而使索引树能够均匀分布。
位图索引:
create bitmap index 索引名称 on 表名 列名
使用场景:位图索引适合创建在低基数列上
位图索引不直接存储ROWID ,而是存储字节位到 ROWID 的映射
优点:减少响应时间,节省空间占用
--创建普通索引create index index_owners_name on t_owners(name);select * from t_owners where name='张哲';--性能测试 创建一个表,向表中插入100万条记录create table t_indextest(id number,name varchar2(30)); beginfor x in 1..1000000 loopinsert into t_indextest values(x,'aa'||x);end loop;commit;end;--创建索引create index index_test on t_indextest(name);--最短时间0.047s select * from t_indextest where id=766664;--最短时间0.016s select * from t_indextest where name='aa766664';--获取rowid AAAM3lAAGAAAAjSACQselect rowid,t.* from t_indextest t where name='aa766664';--用rowid查询 0.015select * from t_indextest where rowid='AAAM3lAAGAAAAjSACQ';--创建唯一索引 同时也创建了唯一约束--如果该字段有重复数据就不能创建create unique index index_owners_meter on t_owners(watermeter);--创建复合索引 一棵树 查询顺序和建索引的顺序一样create index index_owners_ah on t_owners(addressid,housenumber);select * from t_owners where addressid=1 and housenumber='1-3';--方向键索引,用于原本有顺序的数据,将其顺序打乱再索引到树上,防止数太深。--位图索引 在低基数列上建--每个值建一个位图 --只能用=查询 不能用> < like等的范围查询 但是也能查出结果,不过没用到索引create bitmap index 索引名 on 表名(列名);