Schema

数据库中一个对象的合集称为一个schema，它的名字和拥有这些对应的用户名相同。比如 scott用户和它下面的表统称为一个schema。

表 - 段（Segment）

数据库表是一个逻辑概念，段是表的物理化存储。

oracle中只要分配了存储空间的对象都可以称为段对象。

通常情况下，一个表会对应一个段对象，例如：

  1. create table test_table(
  2.     my_column number
  3. );

该语句会在逻辑上创建一个数据库表，在物理上存储时为一个段对象。

特殊情况：

  1. create table lob_table(
  2.     my_column number primary key,
  3.     clob_column clob
  4. );

该语句在逻辑上创建了一个表，但是会创建4个段：

  • lob_table表对应的段对象

  • 主键索引对象对应的段对象

  • clob字段对应的段对象

    oracle中，clob字段内容没有和表的其他字段在一起，而是单独为clob对象，对应的也有一个段对象

  • clob对象的索引对应的段对象

oracle存储的逻辑关系：

  • oracle最底层真正存放数据的是数据文件（data file）
  • 数据文件中，数据存放的最小单位是块（block）。通常一个block为8k，可以进行配置
  • 一组block组成一个extent，方便存储空间的管理
  • 多个extend组成段（segment），同一个段中的几个extent可以位于不同的数据文件上

查看占用的段大小：

  1. select * from user_segments;

表 - 分区（partition）

根据一定条件（例如年份、月份）将表进行表分区，对于要删除的分区数据可以直接drop掉整个分区，而不必再使用delete语句。

分区最主要的是为了方便数据的管理，并不一定能提高SQL查询的效率。

创建分区表示例：

  1. create table test_partition(id int)
  2. -- 按范围分区
  3. partition by range(id)
  4. (
  5.     partition p1 values less than(5),  -- p1分区存储小于5的数据
  6.     partition p2 values less than(10), -- p2分区存储小于10的数据
  7.     partition pmax values less than(maxvalue) -- 剩下的存入p3分区
  8. );

按分区查询表中数据：

  1. -- 查询p1分区的数据
  2. select * from test_partition partition(p1);
  3. -- 查询整个表
  4. select * from test_partition;

删除分区：

  1. -- 删除p1分区
  2. alter table test_partition drop partition p1;

索引

目的：用于加快数据的访问

缺点：占据额外空间，影响DML操作的效率，增删操作时，除了要修改表中数据，还需要维护索引的键值对

Oracle中，索引按数据的组织方式分为：

  • B-tree B树索引

    主键等不容易重复的

  • Bitmap 位图索引

    码值等重复度高的

  • TEXT 全文索引

    类比lucen

查看SQL时大致的资源消耗情况：

  1. -- 打开追踪
  2. SQL> set autotrace trace exp stat;
  3. -- 关闭追踪
  4. SQL> set autotrace off;

视图

视图只是一段代码，不占用空间，不是段对象。

视图允许增删改DML操作，但需要满足特定的条件。

物化视图

物化视图，也称为实体化视图。和普通视图不同，物化视图是一个段对象，会占用空间。

作用：将查询的结果集保存下来，用于后续的查询，提高查询效率。

用途：提高查询效率、远程数据复制

物化视图的创建：

  1. create materialized view mv_employee
  2. as 
  3. select * from employee where personname like 'R__' order by personname desc;

package包

一个包里可包含多个存储过程、函数、触发器，方便管理。

dbms_stats：用来做数据库分析。

例如对某张表做表分析：

  1. SQL> exec dbms_stats.gather_table_stats(user,'employee');

sequence

为业务提供一个序列号。

特点：唯一性、不保证连续性。

不保证连续性的意思：例如设置的sequence的缓存为20，当前缓存中的序列号为40-60，当前分配的序列号为47。此时如果数据库宕机，重启后再分配的序列号将是61，而不是48。

synonym同义词

提供对象的一个别名。

使不同用户下对象的引用变得方便。

例如：想要使用test用户去操作scott用户的emp表，正常情况下需要使用scott.emp的形式，很不方便。此时可以为scott.emp创建一个同义词，直接操作该同义词即可。

  1. -- 查看当前用户
  2. SQL> show user;
  3. -- 创建同义词 sy_emp=scott.emp
  4. SQL> create synonym sy_emp for scott.emp;
  5. -- 直接操作sy_emp即可自动转换成scott.emp
  6. SQL> select * from sy_emp;

通过dblink连接远程数据库表时，也可以创建同义词进行简写：

  1. -- 创建dblink
  2. SQL> create database link myLink connect to mydata indetified by tiger using 'orcl';
  3. -- 正常访问此dblink下scott用户emp表
  4. SQL> select * from scott.emp@mylink;
  5. -- 创建同义词
  6. SQL> create synonym sy_mylink for scott.emp@mylink;
  7. -- 使用同义词查询
  8. SQL> select * from sy_mylink;

dblink数据库链

database link：

  • 用于数据库之间的数据访问和操作
  • 由Oracle保证数据访问和操作的事务性（使用的Oracle分布式事务机制）

创建dblink：

  1. create database link myLink connect to mydata indetified by tiger using 'orcl';

表空间 - 逻辑存储对象

逻辑上：表空间 —> 表、索引、分区等

物理上：表空间 —> 段 —> extent—> block —> data file

创建表空间：

从9i开始，可以配置 db_create_file_dest参数，之后创建表空间时便可不指定datafile/tempfile文件路径。数据文件会使用 db_create_file_dest参数中配置的默认路径。

  1. SQL> show parameter db_create;
  3. NAME                                 TYPE        VALUE
  4. ------------------------------------ ----------- ------------------------------
  5. db_create_file_dest                  string
  6. db_create_online_log_dest_1          string
  7. db_create_online_log_dest_2          string
  8. db_create_online_log_dest_3          string
  9. db_create_online_log_dest_4          string
  10. db_create_online_log_dest_5          string
  12. -- 配置db_create_file_dest参数
  13. SQL> alter system set db_create_file_dest='D:\PC_Document\Oracle_Workspace\tablespace';
  14. -- 直接创建表空间，不指定表空间数据文件，此时便在db_create_file_dest默认路径下生成表空间文件
  15. SQL> create tablespace myTableSpace;

为了方便数据的管理，一般会把数据表空间、索引表空间分开。有时也会根据业务情况再把数据细分到不同表空间中。