Oracle数据库中Clob大字段的查询优化技术研究
1. 1 修改Oracle 的配置参数 在本系统中, 含Clob 大字段的表名字叫文章表( AR- T ICLES)
。该文章表保存多年来各个用户发表的有关计 算机技术文章。该表主要由文章唯一编号( ID)
、文章标题 ( T ITLE)
、发表时间( PDATE)
、作者( AUTHOR)
和文章内 容( CONTENT)
等字段组成, 其中, 文章内容就是Clob 大 字段里。文章表至少有几十万条记录, 也就是说Clob 字段 记录数至少也是几十万条。由于文章表具有大数据量的 特性, 跟其他表设置有所不同, 为了不相互影响各自的性 能, 所以应将文章表和它的全文索引数据放在与其他数据 不同的表空间。
存储文章表的表空间, 需要改变它的一些相关参数来 提高查询Clob 字段的速度 [ 2]
。
( 1)
更改表空间的块尺寸。
在本系统中, Oracle 数据库服务器是一台性能较好的 低档服务器, 4G 内存, 双CPU, RAID5 磁盘陈列。由于文 章表的索引数据量很大, 所以决定将表空间的块尺寸( db- block- size= 16k)
设置大一些, 这样索引的高度将大大减 第 16 卷 第8 期 2006 年8 月 计 算 机 技 术 与 发 展 COMPUT ER TECHNOLOGY AND DEVELOPMENT Vol. 16 No. 8 Aug. 2006 少, 会提高 IO 效率。设置非标准的块大小, 需要改变其相 对应的Oralce 数据库缓冲区, 即 DB- 16k- CACHE- SIZE, 因为它不会用DB- CACHE- SIZE 设置的默认数据库缓冲 区。命令如下：
alter system set db - 16k - cache - size = 200M; ( 2)
同时读取多块数据。
文章表里的一篇文章一般都在10k 以上, 它们分布在 多个block 上, 为了让Oracle 能同时读取大量的数据块以 降低系统的I/ O 开销和 CPU 开销, 需要设置 Oracle 的多 块读取的特性。设置初始化参数 DB - FILE - MULT I- BLOCK- READ- COUNT, 由于这个参数几乎不会导致系 统性能的降低, 所以把它设置高一些, 如：
DB- FILE- MULT IBLOCK - READ - COUNT = 30。为了充分发挥多 块读取数据的优势, 应当尽量配置自己的系统以使数据库 的块尽可能都是连续的。后面创建表空间的时候, 会使表 空间的范围设置大一些, 就是为了满足多个块是连续的。
( 3)
调整Oracle 实例。
为了对Oracle 实例进行有效的调整, 需要对Oracle 初 始化参数进行认真的配置, 因为这些重要参数将直接极大 地影响整个系统( 包括Clob 字段查询)
的性能。主要调整 的参数：
SGA 参数、程序全局区和用户内存参数、undo 参 数、混合参数( 如：
log- buffer, open- cursors)
等。
1. 2 创建表空间 在Oracle9i 中, 需要将文章表及其索引块分配到非默 认数据块大小的表空间。在创建一个表空间时, 使用一个 新的 blocksize 参数, 创建了一个 blocksize 为 16k 的表空 间 [ 3]
。
CREATE TABLESPACE / T EXT0 DAT AFILE . C：
\ ORACLE \ ORADATA \ NET COP \ TEXT. ORA. SIZE 2000M BLOCKSIZE 16KDEFAULT STOR- AGE ( INIT IAL 50M NEXT 50M MINEXT ENT S 1 MAX- EXT ENTS UNLIMIT ED PCT INCREASE 0)
; 这里需要说明的是, 没有采取本地管理( EXT ENT MANAGEMENT LOCAL)
的方式创建表空间, 因为认为文 章表比较特殊, 这里采取人工管理会比本地管理要更好 些。
为什么表空间存储参数 INIT IAL 和 NEXT 会设置 50M 这么大呢?
主要有两个原因：
( 1)
文章表的文章内容为Clob 大字段, 文章字节数多, 设置一个大的范围可以使一篇文章尽量放在一个范围内 存储, 由于文章的数量也很多, 因而不会浪费大的磁盘空 间。
( 2)
保证尽可能多的连续数据块, 使 DB- FILE- MULT IBLOCK - READ - COUNT 参数发挥最大作用。
1. 3 创建包含Clob 字段的表及索引 在作了前面的配置基础上, 创建了一个文章表：
CREATE TABLE / ARTICLES0 ( / ID0 NUMBER( 10)
NOT NULL, / T ITLE0 VARCHAR2( 100)
NOT NULL, / AUTHOR0 VARCHAR2 ( 40)
NOT NULL, / PDATE0 DAT E DEFAULT SYSDATE NOT NULL, / CONTENT 0 CLOB, PRIMARY KEY( / ID0)
)
创建基于Clob 字段的全文索引：
CREATE INDEX CT XSYS. ART ICLES- CT X ON ART ICLES( CONT ENT)
INDEXTYPE IS CTXSYS. CON- T EXT PARAMETERS ( - LEXER WKSYS. WK- CHI- NESE - LEXER STOPLIST CTXSYS. EMPTY - STO- PLIST. )
1. 4 查询Clob 字段 在有几十万条记录的文章表( ART ICLES)
里, 执行如 下查询语句：
select title from ART ICLES where contains ( content, -金华. )
> 0 and pdate> = sysdate- 365; 查询今 年来发表的含有/ 金华0字样的所有文章, 并将标题显示出 来。这时, 发现查询速度非常慢, 结果在20 秒后才显示出 来, 这已经不能满足使用者的需要了。因此, 必须探讨对 查询的优化技术。
2 查询Clob字段数据和优化查询 2. 1 优化索引 对表ART ICLES 建立全文索引时, 系统自动建立了多 个对应的DR$ 表, 如：
DR$ ART ICLES- CTX $ I、DR$ ART ICLES- CT X $ K、DR$ ART ICLES- CTX $ R 等, 事 实上Oracle 管理全文索引的本质就是管理上面这些表和 相关索引。其中最重要的表为：
DR$ ART ICLES - CTX $ I。为了说明它工作的机理, 下面是对它进行的跟踪过 程 [ 4]
。
( 1)
首先插入ART ICLES 表一条记录：
insert into ARTICLES values ( 1, / test0, / guest0, sys- date, -厦门中国. )
; ( 2)
统计DR$ ART ICLES- CTX $ I 的记录：
select count( * )
from DR$ ARTICLES- CT X $ I; 发现4 条记录如表1 所示。
表1 DR$ ARTICLES- CTX $ I 的记录 TOKEN - TEXT TOKEN - FIRST TOKEN - LAST TOKEN - COUNT 厦门 1 1 1 门中 1 1 1 中国 1 1 1 国 1 1 1 之所以有4 条记录, 是因为：
oracle数据库怎么查询表中文是按两个字组成一 条记录, 如果词组有重复, 它会自动合并为一条。最后一 条记录为一个中文字或英文词。这样可以大约算出索引 的记录数为：
中文+ 英文的个数。
( 3)
再插入ARTICLES 表一条记录：
insert into ART ICLES values ( 1, / test10, / guest10, sys- date, -厦门中国. )
; ( 4)
统计DR$ ART ICLES- CTX $ I 的记录：
# 98 # 计算机技术与发展 第16 卷 select count( * )
from DR$ ARTICLES - CT X $ I; 发现8 条记录如表2 所示。
表2 DR$ ARTICLES- CTX $ I 的记录 TOKEN - TEXT TOKEN - FIRST TOKEN - LAST TOKEN - COUNT 厦门 1 1 1 门中 1 1 1 中国 1 1 1 国 1 1 1 厦门 2 2 1 门中 2 2 1 中国 2 2 1 国 2 2 1 如果继续增加记录, DR$ ART ICLES - CTX $ I 表会 越来越大。因此, 应该优化索引, 使重复的记录合并在一 起, 从而减少记录数。
( 5)
优化索引：
执行 ctx- ddl. optimize- index ( -ART ICLES- CTX. , -FAST. )
; 现在看看DR$ ART ICLES - CT X $ I 的记录：
select count( * )
from DR$ ART ICLES- CT X $ I; 发现 8 条记录减少到4 条了。其记录情况如表3 所示。
表3 DR$ ARTICLES- CTX $ I 的记录 TOKEN- TEXT TOKEN- FIRST TOKEN- LAST TOKEN- COUNT 厦门 1 2 2 门中 1 2 2 中国 1 2 2 国 1 2 2 之所以出现上面的情况, 其原因是：
文章内容的字词数( 中文字+ 英文词)
总和大约等于 DR$ ART ICLES - CTX$ I 表( 简称I 表)
的记录数。比如：
一篇文章有 50 个中文字+ 10 个英文词= 60, 那么 I 表记 录数= 60, 如果只按中文计算, 那么占用的最大空间4* 60 = 240byte, 因为I 表保存的总是两个中文字( 除了最后一 个字)
。
现在大致计算实际文章表对应的I 表记录数。假 设有20 万篇文章, 一篇文章有10000 个中文和英文, 那么 I 表将有 10000* 20* 10000= 2000000000 条记录, 即20 亿条记录, 所以, 即使做了上面的优化, 查询还是很慢。
2. 2 建立一个优化JOB 为进一步优化, 编制了一个JOB 任务, 定期优化索引, 优化后的I 表只有原来的1/ 30 大小。
优化索引主要分3 种：
( 1)
ctx- ddl. opt imize- index ( -ARTICLES- CTX . , -FAST. )
; 它不合并空间, 只合并重复记录, 花费时间最短。
( 2)
ctx - ddl. opt imize - index ( -ARTICLES - CTX . , -FULL. )

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。