Oracle 数据库基础
Oracle 基础知识
Oracle 简介
Oracle 是一个数据库管理系统， 是 Oracle 公司的核心产品。 Oracle 在信息管理系统、企业数据处理、Internet 及电子商务等领域使用非常广泛。其在数据安全性与完整性控制方面的优越性能，以及跨操作系统，跨硬件平台的数据互操作能力，使得越来越多的用户将 Oracle 作为应用数据的处理系统。与 SOL Server 相同，两者均是关系数据库，均支持 SQL 92 标准，但 Oracle 是目前最流行的数据库，它占有最大的市场份额.安全性更高，可为大型数据库提供更好的支持。
本课程使用的 Oracle 数据库版本是 Oracle 11g，是基于 “客户端/服务器” (Client/Server) 系统结构。
Oracle数据库的主要特点：
- 支持多用户，大事务量的事务处理。
- 在保持数据安全性和完整性方面性能优越。
- 支持分布式数据处理。 将分布在不同物理位置的数据库用通信网络连接起来，在分布式数据库管理系统的控制下，组成一个逻辑上统的数据库， 完成数据处理任务。
- 具有可移植性。 Oracle 可以在 Windows， Linux 等多个操作系统平台上使用，而 SQL Server 只能在 Windows 平台上运行。
Oracle 基本概念
-
数据库
这里的数据库不是通常情况下我们所说的数据库，而是 Oracle 的一个专业名词。它是磁盘上存储数据的集合，在物理上表现为数据文件、日志文件和控制文件等，在逻辑上以表空间形式存在。使用时，必须首先创建数据库，然后才能使用 Oracle 。可以在安装 Oracle 软件的同时创建数据库，也可以在安装后单独创建数据库。 -
全局数据库名
全局数据库名是用于区分一个数据库的标识，在安装数据库、创建新数据库、创建控制文件修改数据库结构、利用 RMAN 备份时都需要使用。它由数据库名称和域名构成，类似网络中的域名使数据库的命名在整个网络环境中唯一。例如，在一个网络中有两个数据库，数据库名都是 orcl，若这两个数据库之间要建立连接，则必须使用不同的域名加以区分。例如，“orcl .prd .com”，其 "prd .com” 为域名。 -
数据库实例
每个启动的数据库都对应一个数据库实例，由这个实例来访问数据库中的数据。如果把数据库简单地理解为硬盘上的文件，具有永久性，则数据库实例就是通过内存共享运行状态的一组服务器后台进程。 -
表空间
每个 Oracle 数据库都是由若干个表空间构成的，用户在数据库中建立的所有内容都被存储到表空间中。一个表空间可以由多个数据文件组成，但一个数据文件只能属于一个表空间。与数据文件这种物理结构相比，表空间属于数据库的逻辑结构。在每个数据库中，都有一个名为 SYSTEM 的表空间，即系统表空间，还会有 SYSAUX、TEMP、UNDO、USERS 等表空间，这些都是在创建数据库时自动创建的。管理员可以创建自定义的表空间并分配给指定用户，也可以为表空间增加或删除数据文件。 -
数据文件
通常．数据文件的扩展名是 .dbf，足用于仔储数据厍数据的文件，如存储数据库表中的记录、索引、存储过程、视图、数据字典定义等。对于数掂库操作中产生的一些临时数据，以及为保证事务重做所必需的数据也有专门的数据文件负责存储。
一个数据文件中可能存储很多个表的数据，而一个表的数据也可能存放在多个数据文件中，即数据库表和数据文件不存在一对一的关系。 -
控制文件
通常．控制文件的扩展名是 .ctl，是一个二进制文件。控制文件中存储的信息很多，其中包括数据文件和日志文件的名称和位置。控制文件是数据库启动及运行所必需的文件。当 Oracle 读写数据时．要根据控制文件的信息查找数据文件。
由于控制文件的重要性，因此一个数据库至少要有一个以上的控制文件，Oracle 11g 默认包含三个控制文件。各个控制文件内容相同，可以避免因为一个控制文件的损坏而无法启动数据库。 -
日志文件
通常，日志文件的扩展名是 .log，它记录了数据的所有更改信息，并提供了一种数据恢复机制。确保在系统崩溃或其他意外出现后重新恢复数据库。在 Oracle 数据库中，日志文件是成组使用的，每个日志文件组可以有一个或多个日志文件。在工作过程中，多个日志文件组之间循环使用，当一个日志文件组写满后，会转向下一个日志文件组。 -
模式和模式对象
模式是数据库对象（如表、索引等，也称模式对象）的集合。Oracle 会为每一个数据库用户创建一个模式，此模式为当前用户所拥有，和用户具有相同的名称。
Oracle 数据类型
字符数据类型
- CHAR数据类型
当需要固定长度的字符串时，使用 CHAR 数据类型。这种数据类型的列长度可以是1~2000字节如果在定义时未指明大小，则素认其占用1字节， 如果用户输入的值小于指定的长度，则数据库用空格填充至固定长度。如果用户输入的值大于指定的长度，则数据库返回错误报告。 - VARCHAR2 数据类型
VARCHAR2 数据类型支持可变长度的字符串。该数据类型的大小为 1~4000 字节在定义该数据类型时，应指定其大小。与 CHAR 数据类型相比，使用 VARCHAR2 数据类型可以节省磁盘空间，例如，有一个列被定义为 VARCHAR2 数据类型.且大小为 30 字节。 如果用户输入 10 字节的字符，则该行中的列长度将只是 10 字节.而不是 30 字节。如果是 CHAR 数据类型，它将占用 30 字节因为剩余部分 Oracle 会以空格填充。 - NCHAR 数据类型
NCHAR，即国家字符集，使用方法和 CHAR 相同。如果开发的项目需要国际化，那么数据类型选择 NCHAR 数据类型。NCHAR 和 CHAR 的区别在于 NCHAR 用来存储 Unicode 字符集类型，即双字节字符数据。例如，我们定义 CHAR(1) 和 NCHAR(1) 类型的两个字段，字段长度为 1 字节和 1 个字符(2字节)，分别插入 ’a’ 和 ’a’ 是没有问题的，但是占用的字节数分别是 1 和 2 ,如果分别插入 “的” 和 “的”。 则前者无法正常插入，而后者可以。
NVARCHAR2 与 NCHAR 类似，在使用上，NVARCHAR2 存储需要国际化的可变长字符串。
数值数据类型
NUMBER 数据类型可以存储正数、负数、零、定点数和精度为 38 位的浮点数。该数据类型的格式为：
NUMBER(p，s)
colnum_name NUMBER(p) {定点数}
colnum_name NUMBER(p,s) {浮点数}
其中，p为精度，表示数字的有效位数，在 1~38 之间。有效位数:从左边第一个不为0的数算起，小数点和负号不计入有效位数。s为范围，表示小数点右边数字的位数，它在 -84 ~ +127之间。
规则:首先精确到小数点右边 s 位，并四舍五入。如果精确后值的有效位数 ≤p ，则正确;否则报错。
日期时间数据类型
日期时间数据类型用于存储日期值和时间值。
- DATE数据类型
DATE数据类型用于存储表中的日期和时间数据。Oracle 数据库使用自己的格式存储日期，使用7字节固定长度，每字节分别存储世纪、年、月、日、小时、分和秒。日期时间数据类型的值为公元前4712年1月1日到公元9999年12月31日。Oracle 中的 SYSDATE 函数的功能是返回当前的日期和时间。 - TIMESTAMP数据类型
TIMESTAMP数据类型用于存储日期的年、月、日，以及时间的小时、分和秒，其中秒值精确到小数点后6位，该数据类型同时包含时区信息。SYSTIMESTAMP函数的功能是返回当前日期、时间和时区。
LOB数据类型
LOB（Large Objects(大对象)）是用来存储大量的二进制和文本数据的一种数据类型。在 Oracle8i 中开始提供 LOB 字段，在 Oracle10g 前最多只能存储 4G 数据，从 Oracle10g 开始，可以存储 8-128T 的数据，取决于你的数据库的块大小。
- CLOB
字符型大对象（单字节字符数据）。定义一个 CLOB 变量，用于存储超长的文本数据，该字符型大对象存储于数据库中。 - BLOB
二进制大对象。定义一个 BLOB 变量，用于存储大量的二进制数据(如图像、视频、音频等)，该二进制大对象存储于数据库中。 - BFILE
二进制文件。定义一个 BFILE 变量，它指向操作系统的一个文件，Oracle 会将其视为二进制数据进行处理。 - NCLOB
字符型大对象（多字节国家字符数据）。定义一个 NCLOB 变量，用于存储超长的文本数据，该字符型大对象存储于数据库中。
Oracle中的伪列
Oracle中伪列就像一个表列，但是它并没有存储在表中
伪列可以从表中查询，但不能插入、更新和删除它们的值
常用的伪列有：
- ROWID
数据库中的每行都有一个行地址，ROWID 伪列返回该行地址。可以使用 ROWID 值来定位表中的一行。通常情况下，ROWID 值可以唯一地标识数据库中的一行。 - ROWNUM
对于一个查询返回的每行，rownum 伪列返回一个数值代表行的次序。返回的第一行的 rownum 值为1，返回的第二行的值为2，以此类推。通过使用 rownum 伪列，用户可以限制查询返回的行数。
SQL 语言简介
SQL语言是高级的结构化查询语言。用户使用SQL语句进行数据操作时，只需要提出做什么。而不必指明“怎么做”， 具体的执行过程由系统自动完成，大大减轻了用户负担。SQL语言是数据库服务器和客户端之间的重要沟通手段，用于存取数据，以及查询、更新和管理关系型数据库系统。
经过多年的发展，SQL 语言已经成为关系型数据库的标准语言。SQL 支持如下类别的命令。
数据定义语言 (DDL): CREATE (创建)、 ALTER (更改)、TRUNCATE (截断)和 DROP (删
除) 命令。
数据操纵语言(DML): INSERT (插入)、SELECT (选择)、DELETE (删除)和 UPDATE (更
新)命令。
事务控制语言(TCL): COMMIT (提交)、SAVEPOINT (保存点) 和 ROLLBACK (回滚) 命令。
数据控制语言 (DCL): GRANT (授予) 和 REVOKE (回收) 命令。
SQL 操作符
算术操作符
查询语句中要执行基于数值的计算，可以在 SQL 命令中使用算术表达式。算术表达式由 NUMBER 数据类型的列名、数值常量和连接它们的算术操作符组成。算术操作符包括 + (加)、- (减)、* (乘)、/ (除)。
比较操作符
比较操作符用于比较两个表达式的值。比较操作符包括=、!=、<、 >、<=、>、=、BETWEEN…AND (检查是否在两个值之间)、IN (与列表中的值相匹配)、LIKE (匹配字符模式) 和 IS NULL (检查是否为空)。
逻辑操作符
逻辑操作符用于组合多个比较运算的结果以生成一个或真或假的结果。逻辑操作符包括 AND(与) 、 OR (或) 和 NOT (非) 。
集合操作符
集合操作符将两个查询的结果组合成一个结果集。可以在SQL中使用下面的集合操作符来组合多个查询中的行。
- UNION (联合)。该操作符返回两个查询选定的所有不重复的行。
- UNION ALL (联合所有)。该操作符合并两个查询选定的所有行，包括重复的行。
- INTERSECT (交集)。该操作符只返回两个查询都有的行。
- MINUS(减集)。该操作符只返回由第一个查询选定而未被第二个选定的行，即在第一个查询结果中排除在第二个查询结果中出现的行。
使用集合操作符连接起来的SELECT语句中的列遵循以下规则。
- 通过集合操作符连接的各个查询具有相同的列数，而且对应列的数据类型必须兼容。
- 这种查询不应含 有LONG类型的列。列标题来自第一个SELECT语句。
连接操作符
连接操作符（||）用于将两个或多个字符串合并成一个字符串，或者将一个字符串与一个数值合并在一起。
SQL 函数
转换函数
将值从一种数据类型转换为另一种数据类型。
常用的转换函数：
| 函数 | 功能 | 实例 | 结果 |
|---|---|---|---|
| TO-CHAR | 转换字符串类型 | TO-CHAR(1234.5,’$9999.9’) | $1234.5 |
| TO-DATE | 转换日期类型 | TO-DATE(‘1980-01-01’,‘yyyy-mm-dd’) | 1980-01-01 |
| TO-NUMBER | 转换数值类型 | TO-NUMBER(‘1234.5’) | 1234.5 |
其他函数
| 函数 | 功能 |
|---|---|
| NVL(exp1,exp2) | 如果 exp1 的值为null，则返回 exp2 的值，否则返回 exp1 的值 |
| NVL2(exp1,exp2,exp3) | 如果 exp1 的值为null，则返回 exp3 的值，否则返回 exp2 的值 |
| DECODE(value,if1,then1,if2,then2,···,else) | 如果 value 的值为 if1 ，则返回 then1；如果 value 的值为 if2 ，则返回then2的值，·····否则返回 else 的值 |
select ename,
sal+NVL(comm,0) sal1,
NVL2(comm,sal+comm,sal)sal2,
DECODE(to_char(hiredate,'mm'),'01','一月','02','二月','03','三月','04','四月','05','五月','06','六月','下半年') mon from employee;
分析函数
分析函数根据一组行来计算聚合值。
分析函数为每组记录返回多个行。
以下三个分析函数用于计算一个行在一组有序行中的排位，序号从1开始。
语法：
函数名 ( [参数] ) OVER ( [分区子句] [排序子句] )
在语法中:
- 函数名表示分析函数的名称。
- 参数表示函数需要传入的参数。
- 分区子句 (PATITION BY)表示将查询结果分为不同的组，功能类似于GROUP BY语句，是分析函数工作的基础。默认将所有结果作为一个分组。
- 排序子句(ORDER BY) 表示将每个分区进行排序。
表空间
Oracle 数据库包含逻辑结构和物理结构。数据库的物理结构是指构成数据库的一组操作系统文件。数据库的逻辑结构是指描述数据组织方式的一组逻辑概念及它们之间的关系。表空间是数据库逻辑结构的一个重要组件。表空间可以存放各种应用对象，如表、索引。而每个表空间由一个或多个数据文件组成。
-
表空间分类
一般不需要建临时和撤销表空间，除非把它们转移其他磁盘中以提高性能 -
表空间的目的
(1）对不同用户分配不同的表空间，对不同的模式对象分配不同的表空间，方便对用户数据的操作，对模式对象的管理。
(2）可以将不同数据文件创建到不同的磁盘中，有利于管理磁盘空间，有利于提高V性能，有利于备份和恢复数据等。
一般在完成 Oracle 系统的安装并创建 Oracle 实例后，Oracle 系统会自动建立多个表空间。 -
创建表空间
create tablespace tablespacename datafile 'filename' [size integer [ k | m ] ] [ autoextend [ off | on ] ]
其中
tablespacename 是需创建的表空间名称。
datafile 指定组成表空间的一个或多个数据文件，当有多个数据文件时使用逗号分隔。
filename 是数据文件的路径和名称。
size 指定文件的大小，用 k 指定千字节大小，用 m 指定兆字节大小。
库中。
autoextend 子句用来启用或禁用数据文件的自动扩展，设置为 on 则空间使用完毕会自动扩展，设置为 off 则很容易出现表空间剩余容量为 0 的情况，使数据不能存储到数据库中。
--示例：
create tablespace worktbs
datafile 'd:\oracle\oradata\aptech\worktbs01.dbf'
size 10m autoextend on;
-
删除表空间
drop tablespace tablespacename;
自定义用户管理
SYS和SYSTEM用户都是Oracle的系统用户，它们都使用SYSTEM表空间，SYS拥有更大的权限
创建用户
create user username
identified by passwordname
[default tablespace tablespacename]
[temporary tablespace tablespacename]
其中
username 是用户名，用户名必须是一个标识符。
password 是用户口令，口令必须是一个标识符，且不区分大小写。
default tablespace 或 temporary tablespace 为用户确定默认表空间或临时表空间。
--示例
--创建用户
create user martin
identified by martinpwd
default tablespace worktbs
temporary tablespace temp;
--修改口令
alter user martin
identified by mpwd;
--删除用户
drop user martin;
--如果用户拥有模式对象时则无法删除用户,则需使用 cascade 选项删除用户和用户模式对象
drop user martin cascade;
数据库权限管理
权限指执行特定类型SQL命令或访问其他对象的权利
-
系统权限和对象权限
系统权限允许用户执行某些数据库操作。
对象权限允许用户对某一特定对象执行特定的操作。 -
常用系统预定义角色
connect：临时用户
resource：更为可靠和正式的用户
dba：数据库管理员角色，拥有管理数据库的最高权限 -
授予权限
grant 权限 to 用户名； -
撤销权限
revoke 权限 from 用户名；
示例
grant connect,resource to martin; --给martin授予connect和resource两个权限
revoke connect, resource from martin; --撤销martin的connect和resource两个权限
grant select on scott.emp to martin; --允许用户查看 emp 表中的记录
grant update on scott.emp to martin; --允许用户修改 emp 表中的记录
序列
序列是用于生成唯一、连续序号的对象。
序列可以是升序的，也可以是降序的。
使用CREATE SEQUENCE语句创建序列。
-
创建序列
create sequence sequencename [start with integer] [increment by integer] [maxvalue integer | nomaxvalue] [minvalue integer | nominvalue] [cycle | nocycle] [cache integer | nocache]
其中
- start with：指定要生成的第一个序列号。对于升序序列，其默认值为序列的最小值; 对于降序序列，其默认值为序列的最大值。
- integer：参数需要的数值
- increment by，用于指定序列号之间的间隔，其默认值为1。如果n为正值，则生成的序列将按升序排列；如果n为负值，则生成的序列将按降序排列。
- maxvalue：指定序列可以生成的最大值。
- nomaxvalue：如果指定了nomaxvalue，Oracle 将升序序列的最大值设为10^27，将降序序列的最大值设为-1。这是默认选项。
- minvalue：指定序列的最小值。minvalue 必须小于或等于 start with 的值，并且必须小于 maxvalue。
- nominvalue：如果指定了 nominvalue ，Oracle 将升序序列的最小值设为1．将降序序列的最小值设为-10^26。这是默认选项。
- cycle：指定序列在达到最大值或最小值后，将继续从头开始生成值。
- nocycle：指定序列在达到最大值或最小值后，将不能再继续生成值。这是默认选项。
- cache：使用 cache 选项可以预先分配一组序列号，并将其保留在内存中，这样可以更快地访问序列号。当用完缓存中的所有序列号时，Oracle将生成另一组数值．并将其保留在缓存中。
- nocache：使用 nocache 选项，则不会为加快访问速度而预先分配序列号。如果在创建序列时忽略了 cache 和 nocache 选项，Oracle 将默认缓存20个序列号。
--示例
--创建从10开始，每次增加1，最大为2000，不循环，再增加会报错，缓存30个序列号
create sequence seq1
start with 10
increment by 1
maxvalue 2000
nocycle
cache 30;
- 使用和访问序列
nextval：创建序列后第一次使用nextval时，将返回该序列的初始值。以后再引用nextval时，将使用increment by子句来增加序列值，并返回这个新值。
currval：返回序列的当前值，即最后一次引用NEXTVAL时返回的值。
--示例
insert into user(id,name,price)vlaues(seq1.nextval,'明',2500); --把序列加到user的id里去
insert into user(id,name,price)vlaues(seq1.nextval,'红',7500);
--查看序列的当前值
select seq1.currval from dual; --dual表示空表
-
更改序列
alter sequence sequencename [increment by integer] [maxvalue integer | nomaxvalue] [minvalue integer | nominvalue] [cycle | nocycle] [cache integer | nocache]注意: 不能修改start with参数
-
删除序列
drop sequence sequencename;
同义词
同义词用途
简化SQL语句。
隐藏对象的名称和所有者。
为分布式数据库的远程对象提供了位置透明性。
提供对对象的公共访问。
同义词分类
-
私有同义词
私有同义词只能在其模式内访问，且不能与当前模式的对象同名创建语法： create [or replace] synonym [schema.] synonymname for [schema.]objectname; 其中 or replace: 表示在同义词存在的情况下替换该同义词 synonymname: 表示用创建的同义词的名称 objectname: 指定要为之创建同义词的对象的名称 示例: 在a_oe用户下创建私有同义词访问a_hr用户下的employee表 创建 create synonym sy_emp for a_hr.employee; 访问 select * from sy_emp; -
公有同义词
公有同义词可被所有的数据库用户访问创建语法： create [or replace] public synonym synonymname for [schema.]objectname; 示例: 在a_hr用户下对employee表创建公有同义词public_sy_emp, 目的是使a_oe用户可以直接访问public_sy_emp。 创建 create public synonym public_sy_emp for employee; 访问 select * from public_sy_emp; -
删除同义词
删除私有同义词： drop synonym a_oe.sy_emp; --a_oe.sy_emp表示a_oe里的sy_emp同义词 删除公有同义词： drop public synonym a_hr.public.sy_emp;
索引
创建普通索引：
create [unique] index indexname on tablename (columnlist) [tablespace tablespacename];
其中
unique：用于指定唯一索引，默认情况下为非唯一索引。
indexname：指所创建索引的名称。
tablename：表示为之创建索引的表名。
columnlist：在其上创建索引的列名的列表，可以基于多列创建索引，列之间用逗号分隔,
tablespacename：为索引指定表空间。
-
B 数索引
B树索引通常也称为标准索引。索引的顶部为根，其中包含指向索引中下一级的项。下一级为分支块，分支块又指向索引中下一级的块。最低一级为叶节点，其中包含指向表行的索引项。叶块为双向链接，有助于按关键字值的升序和降序扫描索引。 -
唯一索引和非唯一索引
唯一索引：定义索引的列中任何两行都没有重复值。唯一索引中的索引关键字只能指向表中的一行。在创建主键约束和创建唯一约束时都会创建一个与之对应的唯一索引。
非唯一索引：单个关键字可以有多个与其关联的行。创建唯一索引: create unique index index_unique_userid on user(userid); -
反向键索引
与常规B树索引相反，反向键索引在保持列顺序的同时反转索引列的字节。反向键索引通过反转索引键的数据值来实现。其优点是对于连续增长的索引列，反转索引列可以将索引数据分散在多个索引块间，减少I/O瓶颈的发生。
反向键索引通常建立在一些值连续增长的列上，如系统生成的员工编号，但不能执行范围搜索。创建反向索引 create index index_reverse_userid on user(userid) reverse; -
位图索引
位图索引的优点在于，它最适于低基数列(即该列的值是有限的，理论上不会是无穷大)。例如，员工表中的职位列，即便是几百万条员工记录，职位也是可计算的。职位列可以作为位图索引．类似的还有图书表中的图书类别列等。位图索引具有下列优点。
(1）对于大批即时查询，可以减少响应时间。
(2）相比其他索引技术，占用空间明显减少。
(3）即使在配置很低的终端硬件上，也能获得显著的性能。
位图索引不应当用在频繁发生INSERT、UPDATE、DELETE 操作的表上，这些DML操作在性能方面的代价很高。位图索引最适合于数据仓库和决策支持系统。创建位图索引 create bitmap index index_bit_job on user (job); -
其他索引
组合索引：在表内多列上创建。索引中的列不必与表中的列顺序一致，也不必相互邻接，类似于SQL Server中的复合索引，如员工表中部门和职务列上的索引。组合索引最多包含32列。基于函数的索引：若使用的函数或表达式涉及正在建立索引的表中的一列或多列，则创建基于函数的索引。可以将基于函数的索引创建为B树或位图索引。
为角色名称创建大写函数索引 create index index_name on user(upper(name)); -
删除索引
drop index index_name; -
创建索引规则
(1）频繁搜索的列可以作为索引。
(2）经常排序、分组的列可作为索引。
(3）经常用作连接的列（主键/外键）可作为索引。
(4）将索引放在一个单独的表空间中，不要放在有回退段、临时段和表的表空间中，
(5）对大型索引而言，考虑使用NOLOGGING子句创建大型索引。
(6）根据业务数据发生的频率，定期重新生成或重新组织索引．并进行碎片整理。
(7）仅包含几个不同值的列不可以创建为B树索引，可根据需要创建位图索引。
(8）不要在仅包含几行的表中创建索引。
分区
Oracle 允许用户把一个表中的所有行分为几个部分，并将这些部分存储在不同的位置。被分区的表称为分区表，分成的每个部分称为一个分区。
对于包含大量数据的表来说，分区很有用。表分区有以下优点。
-
改善表的查询性能，在对表进行分区后，用户执行SQL查询时可以只访问表中的特定分区而非整个表。
-
表更容易管理，因为分区表的数据存储在多个部分中，按分区加载和删除数据比在表中加载和删除更容易。
-
便于备份和恢复，可以独立地备份和恢复每个分区。
-
提高数据安全性，将不同的分区分布在不同的磁盘，可以减小所有分区的数据同时损坏的可能性。
符合以下条件的表可以建成分区表:
（1）数据量大于2GB。
（2）已有的数据和新添加的数据有明显的界限划分。
表分区对用户是透明的，即应用程序可以不知道表已被分区，在更新和查询分区表时当作普通表来操作，但 Oracle优化程序知道表已被分区。
注意: 要分区的表不能具有LONG和 LONG RAW 数据类型的列。
- 范围分区
范围分区（range) 是应用范围比较广的表分区万式，它以列的值的泥围作为分区的划分条件，将记录存放到列值所在的 range 分区中。
--创建表并分区
create table sales1
(
sales_id number,
sales_date date
)
partition by range (sales_date)
(
partition p1 values less than (to_date('2020-4-1', 'yyyy-mm-dd')),--按季度分区
partition p2 values less than (to_date('2020-7-1', 'yyyy-mm-dd')),
partition p3 values less than (to_date('2020-10-1', 'yyyy-mm-dd')),
partition p4 values less than (maxvalue) --创建maxvalue分区,所有不在指定范围内的记录会被存储到maxvalue分区中
--查询第3季度的数据
select * from sales1 partition(p3);
--删除第3季度的数据
delete from sales1 partition(p3);
);
- 间隔分区
间隔分区（Interval〉是Oracle 11g 版本新引入的分区方法，是范围分区的一种增强功能，可以实现范围分区的自动化。
它的优点为在不需要创建表时就将所有分区划分清楚。间隔分区随着数据的增加会划分更多的分区, 并自动创建新的分区。
create table sales2
(
sales_id number,
sales_date date
)
partition by range(sales_date)
interval(numtoyminterval(3,'month')) --代表"间隔", 这里表示3个月为一个分区
(
partition p1 values less than (to_date('2013-04-1','yyyy/mm/dd'))
);
--interval(numtoyminterval(3,'month'))
--numtoyminterval(1,'year')：1年为一个分区
--numtoyminterval(1,'month')：1月为一个分区
--numtodsinterval(1,'day')：1天为一个分区
--numtodsinterval(1,'hour')：1小时为一个分区
--numtodsinterval(1,'minute')：1分钟为一个分区
--numtodsinterval(1,'second')：1秒钟为一个分区
--获得分区情况
select table_name,partition_name from user_tab_partitions where table_name=upper('sales2');
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
