oracle查缺补漏

发表于 分类于

SQL 窗口

PL/SQL Developer 工具的 SQL 窗口只能执行 SQL 和 PLSQL，执行命令使用命令窗口。

分页查询

https://www.youtube.com/watch?v=BHzgsKi3dI4&list=PLD3Xyx6ef38yPjePP9e0yfIwX4lGOcb3w&index=28

复合索引

建立时的字段顺序和条件中的字段顺序相同才会走索引。

反向建索引

当某字段的值连续增长时，构建标准索引会形成歪脖子树，查询性能不理想。反向建索引，可以让索引的值变得不规则，从而使索引树均匀分布。

1
create index 索引名称 on 表名(列名) reverse;

image-20220309194826259

同义词

1
create [public] SYNONYM 同义词名称 for object

public 是可选的，不加就是私有同义词，只能被当前用户使用。object 可以是表、试图、序列等要创建同义词的对象名称。

为表 T_OWNERS 创建（私有）同义词，名称为 OWNERS，语句：

1
create synonym OWNERS for T_OWNERS;

使用同义词

1
select * from OWNERS;

视图

主要用来简化数据操作。

视图提供了一个简单而有效的安全机制，可以定制不同用户对数据的访问权限。比如不希望一张表的敏感信息被他人查看，可以只赋予视图权限，而且只读。

提供向后兼容性：视图使用户能够在表的架构更改时为表创建向后兼容接口。比如改表字段导致查询无法使用，可以使用视图查询。

1
2
3
4
5
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name
    [(alias[, alias]...)]
AS subquery
     [WITH CHECK OPTION [CONSTRAINT constraint]]
     [WITH READ ONLY]

alias：为视图产生的列定义的别名；

subquery ：一条完整的SELECT语句，可以在该语句中定义别名；

WITH CHECK OPTION ：插入或修改的数据行必须满足视图定义的约束；

WITH READ ONLY ：默认可以通过视图对基表执行增删改操作，但是有很多在基表上的限制(比如：基表中某列不能为空，但是该列没有出现在视图中，则不能通过视图执行insert操作)，WITH READ ONLY说明视图是只读视图，不能通过该视图进行增删改操作。现实开发中，基本上不通过视图对表中的数据进行增删改操作。

键保留表：把主键保留下来的那个表。通过视图查询的数据中，主键来自哪个表，哪个表就是键保留表，只能修改键保留表中的数据，而不能修改其他表的数据。

聚合统计的视图是没有键保留表的，这种视图都是只读的。

物化视图

https://www.modb.pro/db/52367

https://www.youtube.com/watch?v=mo61cAiV0HE&list=PLD3Xyx6ef38yPjePP9e0yfIwX4lGOcb3w&index=45 

1
2
3
4
5
6
7
8
CREATE METERIALIZED VIEW view_name
[BUILD IMMEDIATE | BUILD DEFERRED]
REFRESH [FAST|COMPLETE|FORCE]
[
ON [COMMIT | DEMAND] | START WITH (START_TIME) NEXT (NEXT_TIME)
]
AS
subquery

会生成一个表。

参数详解

  • build immediate 在创建物化视图时就生成数据，此为默认值。
  • build deferred 创建时不生成数据。当配合 commit 使用时，第一次必须手动刷新，后面才会自动刷新。

刷新模式：

  • commit 自动刷新，当基表有事务提交时刷新物化视图。
  • demand 手动刷新。

刷新方法：

  • fast 采用增量刷新，只刷新自上次刷新以后进行的修改。前提是视图中用到的表必须都有物化视图日志，且建立的物化视图必须有基表的ROWID或主键字段（可以使用别名），物化视图会根据它确定需要更新的数据行。
  • complete 对整个物化视图进行完全刷新。
  • force 自动选择。在刷新时会去判断是否可以进行快速刷新，如果可以则采用 FAST 方式，否则采用 COMPLETE 的方式。

刷新时间（可选）：

  • start_time 指定起始时间。
  • next_time 下次开始时间，起始到下次的间隔时间即改表的循环时间。

手动刷新

物化视图生成的表数据

1
2
3
begin
	DBMS_MVIEW.refresh('VIEW_NAME', 'C'); -- 这是oracle封装的存储过程，有两个参数。
end;

也可以使用命令刷新，打开命令窗执行：

1
EXEC DBMS_MVIEW.refresh('VIEW_NAME', 'C');

物化视图日志

此日志记录了基表发生的变化（增、删、改），物化视图用记录来刷新数据行，刷新完成后会自动删除日志记录，所以若要看到效果可以将物化视图定义为手动刷新（demand），当基表变化是查看日志表，手动刷新后，日志表数据被删除。

1
create materialized view log on 物化视图使用的表名 with rowid(或者主键); -- 根据rowid更新物化视图

会生成一张表，默认物化视图日志的表名格式：MLOG$_表名。

备份和恢复

使用imp命令和exp命令对oracle数据库进行导入导出操作

整库导入导出

https://www.youtube.com/watch?v=_zOp8S4uQQ4&list=PLD3Xyx6ef38yPjePP9e0yfIwX4lGOcb3w&index=15

按用户和表导入导出

https://www.youtube.com/watch?v=2BoHiT_ymC4&list=PLD3Xyx6ef38yPjePP9e0yfIwX4lGOcb3w&index=16