创建表空间
CREATE TABLESPACE assm
DATAFILE '/u01/oradata/denver/assm_1.dbf'
SIZE 100M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT AUTO;
size 100M
:设定了这个数据文件的初始大小为100兆字节(MB)。exterl management local uniform size 128K
:exterl management local
表示采用本地管理方式来管理表空间的存储空间分配。与字典管理方式相比,本地管理方式具有更好的性能和可维护性等优势。uniform size 128K
意味着表空间中的所有区(extents)将被统一分配为128千字节(KB)的大小。这种统一分配方式有助于更高效地管理空间和减少空间碎片。
segment space management auto
:指定了段空间管理方式为自动(auto)。在自动段空间管理模式下,数据库会自动处理段内空闲空间的管理等相关事宜,比如自动跟踪和管理空闲块等,使得数据库在空间利用和管理上更加智能化和高效。
创建用户并授予权限
创建用户:
create user msi identified by msi default tablespace mssm; created.
create user msi
:明确指定了要创建的用户名为msi
。identified by msi
:设定了该用户的登录密码也是msi
。这种设置密码的方式在实际环境中可能存在安全风险,因为密码过于简单且与用户名相同,通常建议使用更复杂且难以猜测的密码。default tablespace mssm
:指定了该用户的默认表空间为mssm
。当这个用户创建数据库对象(如表格、索引等)时,如果没有特别指定表空间,这些对象将会被创建在mssm
这个表空间中。
授予权限:
grant connect, resource to as1;
- 功能:此语句的作用是授予用户
as1
两个系统权限,即connect
权限和resource
权限。 - 详细解释:
grant
:是用于授予权限的SQL关键字。connect
:拥有该权限的用户可以连接到数据库。基本上,这是允许用户登录并与数据库进行交互的最基本权限之一。resource
:具有该权限的用户可以在其拥有的表空间内创建各种数据库对象,如表格、视图、索引等。通过授予resource
权限,用户as1
就能够在其相应的权限范围内进行数据库对象的创建和管理等操作。
创建表的示例
CREATE TABLE t (id INT,name CHAR(10)
)
STORAGE (INITIAL 128K,NEXT 128K,PCTINCREASE 0,MINEXTENTS 1,MAXEXTENTS 5
)
TABLESPACE assm;
语句解释
- 创建表结构部分:
CREATE TABLE t (
:这是创建表的起始语句,表明要创建一个名为t
的表。id INT,
:定义了表中的一个列名为id
,数据类型为整数类型(INT
)。name CHAR(10)
:定义了另一个列名为name
,数据类型为定长字符型,长度为10个字符。
- 存储参数部分:
STORAGE (
:开始设置表的存储参数相关内容。INITIAL 128K
:指定了表在创建时分配的初始存储空间大小为128千字节NEXT 128K
:当表中已有空间用完,需要额外分配空间时,每次分配的空间大小为128千字节。PCTINCREASE 0
:设置每次分配额外空间时,相较于上一次分配空间大小的增长百分比为0%。即每次分配的额外空间大小保持固定,都是NEXT
参数所设定的值(这里是128K)。MINEXTENTS 1
:规定表最少需要分配的区(extent
,是数据库存储分配的一种单位)数量为1个。MAXEXTENTS 5
:限制表最多能够分配的区数量为5个。这是为了防止表无限制地占用存储空间,起到一定的空间管理和控制作用。
- 表空间指定部分:
TABLESPACE assm
:指定了该表t
将被创建在名为assm
的表空间中。之前创建用户msi
时指定了默认表空间为mssm
,但这里通过此语句可以将特定表创建在其他指定的表空间中(这里是assm
)。
自动段空间管理(ASSM)和手动段空间管理(MSSM)
简单介绍
这段内容主要围绕创建堆表时在不同段空间管理模式(自动段空间管理ASSM和手动段空间管理MSSM)下需要注意的重要选项进行阐述,具体如下:
- FREELISTS(仅MSSM):
- 功能:每个表通过它在堆中管理已分配的块,且表可拥有多个。
- 影响:若预计有许多并发用户大量插入数据到表中,合理配置多个FREELISTS能显著提升性能,但会增加额外存储成本。其原理是可加速块分配过程,类似多窗口并行处理插入请求。
- PCTFREE(ASSM和MSSM):
- 功能:衡量在INSERT过程中一个块可被填满的程度,用于控制是否能向块中添加行,还能控制后续更新引起的行迁移。
- 设置要点:需根据表的具体使用方式设置,设置不当会导致行迁移频繁或空间浪费等问题,如经常更新的数据表可适当增大,以插入为主的数据表可适当减小。
- PCTUSED(仅MSSM):
- 功能:衡量一个块再次成为插入候选块之前需空出的程度,小于PCTUSED使用空间的块可作为插入候选块。
- 设置要点:与PCTFREE协同影响块空间利用和数据操作性能,设置不当会造成空间浪费或不必要的操作,要根据表的数据操作特性合理调整。
- INITRANS(ASSM和MSSM):
- 功能:指定最初分配给一个块的事务槽数量,用于记录并发事务对块的操作信息。
- 设置要点:若设置过低,在多用户访问同一块且接近满且事务列表不能动态扩展时,会引发并发问题,若预计有大量并发更新相同块的情况,应适当提高该值。
很长的论述
在创建表语句(CREATE TABLE)中堆表重要选项的解析
在创建表语句(CREATE TABLE)的众多可用选项中,针对堆表(普通表/heap tabel),有若干重要选项值得特别关注。其中,在自动段空间管理(ASSM)模式下有两个关键选项,手动段空间管理(MSSM)模式下则有四个重要选项。
一、FREELISTS选项(仅适用于MSSM模式)
在MSSM模式下,每个表均通过自由列表来管理其在堆中所分配的块,且一个表能够拥有多个自由列表。当存在大量并发用户对表执行频繁插入操作的预期时,合理配置多个自由列表可显著提升性能,当然,这可能会伴随一定的额外存储开销。
具体而言,自由列表的主要作用在于加速块的分配过程,其工作原理是预先维护一系列可供插入数据的空闲块列表。例如,在一个电商系统的订单表中,如果在促销活动期间有众多用户同时下单(即并发插入订单数据),若仅设置一个自由列表,那么所有插入操作都需在该列表中排队等待空闲块分配,这将严重影响插入性能;而若配置多个自由列表,就如同开设多个服务窗口,可并行处理插入请求,大大提高整体效率。
二、PCTFREE选项(适用于ASSM和MSSM模式)
在插入数据(INSERT)过程中,它用于衡量一个块能够被填充的程度。当向表中插入数据时,数据库会依据PCTFREE的值来判定是否能将新行插入到当前块中。例如,若设定PCTFREE为20%,则意味着当块的已使用空间达到80%时,将不再允许新行插入该块,除非进行更新操作且更新后的行长度不超过剩余空间。
此外,PCTFREE还在控制后续更新操作引发的行迁移方面发挥关键作用。若PCTFREE设置过小,可能导致频繁的行迁移,这是因为更新操作可能使行长度增加,当块已满且无足够空间容纳更新后的行时,该行就需迁移至其他块,这无疑会增加数据库的开销并降低性能。反之,若PCTFREE设置过大,则会造成空间浪费,因为块过早地被标记为不可插入新行状态。因此,需根据表的具体使用场景谨慎设置该值,如对于经常更新且更新后行长度变化较大的数据表,可适当增大PCTFREE的值;而对于主要以插入操作为主、更新操作较少的数据表,则可适当减小PCTFREE的值。
三、PCTUSED选项(仅存在于MSSM模式)
它用于衡量一个块在经过一系列删除或更新操作后,需空出多少空间才能再次成为插入新行的候选块。例如,若设定PCTUSED为40%,那么当一个块因数据删除或更新导致其使用空间低于40%时,该块将被纳入可插入新行的候选块列表。
PCTUSED与PCTFREE协同工作,共同影响块的空间利用效率和数据操作性能。若PCTUSED设置过高,可能导致块在很长时间内无法被重新利用进行插入操作,造成空间浪费;若设置过低,则可能频繁地将仍有较多可用空间的块作为插入候选块,增加不必要的块扫描和数据移动操作,同样会影响性能。所以,在设置PCTUSED时,需充分考虑表的数据操作特性,如对于数据更新频繁且数据量波动较大的表,应合理调整PCTUSED的值,以实现块空间的高效循环利用。
四、INITRANS选项(适用于ASSM和MSSM模式)
它用于指定最初分配给一个块的事务槽数量。事务槽的作用在于记录并发事务对块的操作信息。若该值设置过低(其默认值与最小值均为2),在多个用户同时访问同一个块并进行事务操作时,尤其是在数据库块接近满且事务列表无法动态扩展的情况下,可能引发严重的并发问题。
例如,在一个银行系统的账户余额表中,若多个用户同时对同一账户进行转账操作(即并发更新相同块中的数据),而INITRANS设置为2,当有超过两个并发事务时,事务槽将供不应求,后续事务只能排队等待,导致系统响应变慢,甚至可能引发交易超时等问题。因此,若预估会有大量并发更新针对相同块的情况发生,应适当提高INITRANS的值,以确保事务能够顺利进行,避免因事务槽不足而导致的并发瓶颈。
创建表的指南
- 将表放置在独立表空间:便于管理和维护,利于分类存放不同业务功能的表,方便备份、恢复与性能优化,还可避免某个表空间出问题影响其他表所在空间。
- 采用本地管理表空间:由数据库自身管理区分配,相比传统字典管理更高效地分配和回收空间,能自动避免产生空间碎片,保证数据存储连续性与高效性,提升整体性能。
- 使用少量标准区大小:使空间分配更规整、可预测,减少表空间碎片化,便于数据库快速定位和操作数据,提高效率。
- 避免与特定段放一起:不要把表放在包含回滚段、临时段和索引的表空间内,因为它们用途和特性不同,一起存放会使管理复杂,影响性能,分开能精细化管理。
创建临时表
- 创建方式:使用“
GLOBAL TEMPORARY
”子句创建。 - 数据保留期限:仅在事务或会话期间保留数据,事务特定临时表的数据在事务持续期间存在,会话特定临时表的数据在整个会话期间存在,且各会话的数据相互独立,只能看到和修改自己的数据。
- 锁相关情况:不会对临时表的数据获取数据操纵语言(DML)锁。
- 可创建其他对象:能在临时表上创建索引、视图和触发器,也可用导出和导入工具导出导入临时表的定义,但即便用了包含行数据的选项(ROWS option)也不会导出数据,临时表的定义对所有会话可见。
关于PCTFREE和PCTUSED的计算说明
- PCTFREE计算:
- 公式为((Average Row Size - Initial Row Size) *100/Average Row Size)。这个公式的目的是计算出一个百分比值,用于衡量在数据块中应该为数据更新预留多少空间。
- 其中(Average Row Size)(平均行大小)是表中每行数据的平均占用空间大小,(Initial Row Size)(初始行大小)可能是指插入数据时行的初始大小。通过这个公式计算出来的PCTFREE,表示为了应对行数据增长(例如更新操作导致行数据变大),在数据块中应该预留的空闲空间比例。
- PCTUSED计算:
- 公式为(Average Row Size * 100/(100 - PCTFREE))(这里分母的(100 - PCTFREE)表示扣除预留更新空间后的可用空间比例),目的是计算出一个与数据块空间利用相关的百分比值。
- 它衡量的是在经过一系列删除或更新操作后,一个块需要空出多少空间才能再次成为插入新行的候选块。这个值的计算涉及到数据块的整体空间利用情况,考虑了已经预留的更新空间(PCTFREE),以确定在什么情况下块可以重新用于插入新数据。
- Available Data Space(可用数据空间):这里提到的“可用数据空间”可能是在计算PCTUSED过程中作为一个参考概念,即整个数据块空间扣除PCTFREE预留空间后,剩余的可用于其他操作(如插入新行)的空间。它与PCTUSED的计算紧密相关,因为PCTUSED的计算需要考虑在这个可用空间的基础上,块达到什么程度的空闲才可以再次用于插入新行。
设置 PCTFREE 和 PCTUSED
PCTFREE
:- 作用:在数据库块内为数据更新预留更多空间。
- 适用场景:若表包含初始为 NULL 而后更新有值的列,或者包含因更新可能导致大小增加的列,就适合设置较高的
PCTFREE
值。不过较高的PCTFREE
会使块密度降低,即每个块能容纳的行数变少。通过特定公式可确保块内有足够空闲空间供行数据增长。
PCTUSED
:设置该参数是为了确保只有当块中有足够空间容纳平均行大小时,块才会被放回空闲列表。若空闲列表中的块没有足够空间插入一行,Oracle 服务器会查找空闲列表中的下一个块,这种线性扫描会持续,直到找到有足够空间的块或者到达列表末尾。利用给定公式可通过提高找到有足够空闲空间块的概率,减少扫描空闲列表所花费的时间。平均行大小的值可通过ANALYZE TABLE
命令来估算。
临时表
临时表用于在事务或会话期间保存中间结果集。临时表中存储的数据仅对当前会话可见——即便当前会话提交了这些数据,其他任何会话也看不到该会话的数据。在临时表方面,多用户并发也不是问题,因为一个会话永远不会因使用临时表而阻塞另一个会话。即便我们“锁定”临时表,它也不会阻止其他会话使用它们各自的临时表。
正如我们在第9章中所了解到的,临时表产生的重做信息比常规表要少得多。然而,由于临时表必须为其所包含的数据生成回滚信息,所以它们还是会产生一定量的重做信息。其中,更新和删除操作会产生最多的重做信息,而插入和查询操作产生的重做信息最少。
Oracle的临时表与其他关系数据库中的临时表类似,主要的区别是它们是“静态”定义的。每个数据库创建一次,而不是数据库中的每个存储过程创建一次。它们始终存在一它们将作为对象存在于数据字典中,但是在会话将数据放入它们之前,它们始终显示为空。由于它们是静态定义的,因此可以创建引用临时表的视图,创建使用静态SQL引用临时表的存储过程,等等。
临时表会产生最少的重做,但它们仍然会产生一些重做,并且没有办法禁用它。重做是为回滚数据生成的,在大多数典型使用中,它可以忽略不计。如果只对临时表进行INSERT和SELECT操作,那么生成的重做量将不会明显。只有当你大量地删除或更新临时表时,你才会看到大量的重做生成。
CBO使用的统计数据可以谨慎地在临时表上生成;但是,可以使用DBMS_STATS包在临时表上设置更好的统计数据猜测集,或者由优化器在硬解析时使用动态采样动态收集统计数据。
临时表(Temporary Tables)相关的特点及性质
用途方面
临时表用于在事务(transaction)或者会话(session)期间存储中间结果集(intermediate result sets),也就是在特定的事务处理过程中或者整个会话持续阶段,临时存放一些过渡性的数据。
数据可见性方面
临时表中所存放的数据仅对当前会话可见,即便当前会话提交(COMMIT)了这些数据,其他会话也无法看到当前会话的数据,每个会话的数据都是相互独立、私有的。
多用户并发方面
在多用户并发场景下,临时表不会引发问题。因为一个会话使用临时表时,不会阻塞其他会话对各自临时表的使用,各会话之间不会相互干扰,不存在一个会话因为使用临时表而影响到其他会话正常操作的情况。
重做(redo)与回滚(undo)信息生成方面
相较于常规表,临时表产生的重做信息(redo)明显更少。不过,由于临时表必须为其所包含的数据生成回滚(undo)信息,所以还是会产生一定量的重做信息,其中执行更新(UPDATE)和删除(DELETE)操作时产生的重做信息最多,而插入(INSERT)和查询(SELECT)操作产生的重做信息最少。