《Oracle编程艺术》学习笔记(37)-数据类型-LOB

1) BLOB
二进制LOB。用于存储大量的二进制信息,不会进行字符集转换。
允许存储最多(4GB)×(创建LOB时指定的CHUNK参数,默认为数据库块大小)字节的数据。

2) CLOB
字符LOB。这种类型用于存储大量的文本信息,要进行字符集转换。
允许存储最多(4GB)×(创建LOB时指定的CHUNK参数,默认为数据库块大小)字节的数据。

3) NCLOB
存储用Unicode编码的信息,而且像CLOB一样,这些信息要进行字符集转换。
允许存储最多(4GB)×(创建LOB时指定的CHUNK参数,默认为数据库块大小)字节的数据。
NCLOB中存储按照NLS_NCHAR_CHARACTERSET参数指定的字符集来编码的数据。

4)BFILE
允许在数据库列中存储一个Oracle目录对象(操作系统目录的一个指针)和一个文件名,并读取这个文件。
实际上允许以一种只读的方式访问数据库服务器上可用的操作系统文件,就好像它们存储在数据库表本身中一样。

BLOB,CLOB和NCLOB
CLOB、BLOB和NCLOB在数据库中的存储,还有创建时的参数选项都是类似的。

tony@ORA11GR2> create table t(x int, y clob) segment creation immediate; Table created. tony@ORA11GR2> select segment_name, segment_type from user_segments; SEGMENT_NAME SEGMENT_TYPE -------------------------------- ------------ T TABLE SYS_IL0000073988C00002$ LOBINDEX SYS_LOB0000073988C00002$ LOBSEGMENT


可以看到为CLOB创建了2个段,即LOBINDEX和LOBSEGMENT。
LOBINDEX用于执行LOB的导航。创建一个LOB列时,一般来说,存储在行中的这是一个指针(pointer),或LOB 定位器(LOB locator)。
当查询时,将对LOBINDEX使用LOB定位器来找出这些字节存储在哪里,然后再访问LOBSEGMENT。

通过下面语句可以查看LOB可用的属性有哪些。

tony@ORA11GR2> select dbms_metadata.get_ddl('TABLE','T') from dual; DBMS_METADATA.GET_DDL('TABLE','T') -------------------------------------------------------------------------------- CREATE TABLE "TONY"."T" ( "X" NUMBER(*,0), "Y" CLOB ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" LOB ("Y") STORE AS BASICFILE ( TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))


1)STORE AS [BASICFILE/SECUREFILE]
指定BASICFILE/SECUREFILE是11g之后增加的特性。默认为BASICFILE,这也是11g之前的唯一选择。
与高级压缩选项或者高级安全选项结合使用时,SECUREFILE可以提供更多选择(压缩,加密,消除复制)

2)LOB表空间
指定存储LOBINDEX和LOBSEGMENT的表空间。
基于管理和性能的考虑,需要为LOB数据另外指定一个表空间。
LOB数据庞大,另外分配一个表空间有利于备份,恢复,空间管理。
默认情况下,LOB数据不在缓冲区缓存中进行缓存,每次访问都会带来一个物理I/O,这种情况下,把LOB数据单独储存到一个磁盘上就非常有意义。

3)ENABLE/DISABLE STORAGE IN ROW
控制是否将LOB数据和表分开存储。如果使用ENABLE STORAGE IN ROW,这也是默认行为,对于小的LOB(最多4000字节),就会像VARCHAR2一样直接存储在表中,这会带来性能上的提高。

4)CHUNK
LOB存储在CHUNK中;指向LOB数据的索引会指向各个CHUNK。
CHUNK是逻辑上连续的一组数据库块(block),这也是LOB的最小分配单元,而通常数据库的最小分配单元是数据库块。
CHUNK大小必须是数据库块大小的整数倍。
CHUNK大小必须合适。如果过大,会造成空间浪费;如果过小,会造成CHUNK数很多,这就会造成LOBINDEX很大,降低性能。

5)RETENTION/PCTVERSION
LOB实现读一致性(http://blog.csdn.net/fw0124/article/details/6899246)的方式和一般的表数据有所不同。
LOBINDEX会像其他段一样生成UNDO,但是LOBSEGMENT并不使用UNDO来记录其修改,而是直接在LOBSEGMENT本身中维护数据的版本。
修改一个LOB时,Oracle会分配一个新的CHUNK,并且仍保留原来的CHUNK。如果回滚了事务,对LOB索引所做的修改会回滚,索引将再次指向原来的CHUNK。
Oracle使用LOBINDEX的读一致视图来撤销对LOB的修改,从而获得读一致性。

RETENTION子句告诉Oracle按照UNDO_RETENTION参数来决定将修改过的LOB历史数据保存在LOBSEGMENT中多久的时间。
PCTVERSION子句控制着用于保存修改过的LOB历史数据的存储空间占已分配LOB空间的百分比。

6)CACHE/CACHE READS/NOCACHE
默认为NOCACHE,LOB数据不在缓冲区缓存中进行缓存,每个访问都是对磁盘直接读写。
CACHE READS允许缓存从磁盘读的LOB数据,但是LOB数据的写操作直接写至磁盘。
CACHE则允许读和写时都能缓存LOB数据。
对于频繁读写,并且较小的LOB,比较适合使用缓存。

BFILE
BFILE类型只是操作系统上一个文件的指针。它用于为这些操作系统文件提供只读访问。
可以使用dbms_lob包,把BFILE当成一个LOB来处理。

不过需要注意的是,如果要从BFILE中加载文字数据,需要注意字符集问题。
为此可以使用DBMS_LOB.LOADCLOBFROMFILE过程,它的bfile_csid参数用来指定BFILE的character set id。
可以用下面的语句查询所使用的字符集对应的character set id。
select nls_charset_id(value) charset_id, value charset_name
from v$nls_valid_values where parameter = 'CHARACTERSET'
order by value;
例如,通过上面语句查到ZHS16GBK对应的id是852。

在D盘根目录下建立文件test.txt,存入文字,当前是中文系统,因此默认是中文(ZHS16GBK)编码方式。
下面来从此文件读入文本到一个CLOB对象中。

tony@ORA11GR2> create or replace directory os_dir as 'D:\'; Directory created. tony@ORA11GR2> create table t(os_file bfile, txt clob); Table created. tony@ORA11GR2> declare 2 l_clob clob; 3 l_file bfile := bfilename('OS_DIR', 'test.txt'); 4 l_dest_offset number := 1; 5 l_src_offset number := 1; 6 l_bfile_csid number := 852; 7 l_lang_context number := 0; 8 l_warning number := 0; 9 begin 10 insert into t(os_file, txt) values(l_file, empty_clob()) 11 returning txt into l_clob; 12 13 if (dbms_lob.fileexists(l_file) != 0) then 14 dbms_lob.fileopen(l_file, dbms_lob.lob_readonly); 15 dbms_lob.LOADCLOBFROMFILE( 16 dest_lob => l_clob, 17 src_bfile => l_file, 18 amount => dbms_lob.getlength(l_file), 19 dest_offset => l_dest_offset, 20 src_offset => l_src_offset, 21 bfile_csid => l_bfile_csid, 22 lang_context => l_lang_context, 23 warning => l_warning); 24 dbms_lob.fileclose(l_file); 25 end if; 26 end; 27 / PL/SQL procedure successfully completed. tony@ORA11GR2> column os_file format a35 tony@ORA11GR2> select * from t; OS_FILE TXT ----------------------------------- -------------------------------------------------- bfilename('OS_DIR', 'test.txt') 查看数据库字符集和National Character字符集的方法。 select * from v$nls_parameters; NLS_CHARACTERSET - 数据库字符集 NLS_NCHAR_CHARACTERSET - National Character字符集
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
目录 序 ............................................................................................... 17 前言 ............................................................................................ 20 本书内容.................................................................................... 21 读者对象.................................................................................... 21 本书组织结构............................................................................... 23 源代码和有关更新.......................................................................... 29 勘误表....................................................................................... 29 配置环境....................................................................................... 30 建立SCOTT/TIGER 模式.................................................................. 30 环境.......................................................................................... 32 设置SQL*Plus 的AUTOTRACE ......................................................... 35 配置Statspack............................................................................. 37 定制脚本.................................................................................... 38 SHOW_SPACE ............................................................................. 49 BIG_TABLE ................................................................................. 57 代码约定.................................................................................... 60 第 1章 开发成功的Oracle应用程序...................................................... 61 1.1 我的方法................................................................................ 63 3 / 976 1.2 黑盒方法................................................................................ 65 1.3 开发数据库应用的正确(和不正确)方法.......................................... 70 1.3.1 了解Oracle体系结构............................................................ 70 1.3.2 理解并发控制...................................................................... 78 1.3.3 多版本.............................................................................. 84 1.3.4 数据库独立性? .................................................................... 93 1.3.5 “怎么能让应用运行得更快?”................................................114 1.3.6 DBA与开发人员的关系.........................................................119 1.4 小结....................................................................................121 第 2章体系结构概述........................................................................123 2.1 定义数据库和实例....................................................................125 2.2 SGA和后台进程......................................................................133 2.3 连接Oracle ...........................................................................136 2.3.1 专用服务器.......................................................................137 2.3.2 共享服务器.......................................................................138 2.3.3 TCP/IP连接的基本原理........................................................140 2.4 小结....................................................................................143 第 3章文件..................................................................................144 3.1 参数文件...............................................................................146 4 / 976 3.1.1 什么是参数?.....................................................................147 3.1.2 遗留的init.ora 参数文件........................................................150 3.1.3 服务器参数文件..................................................................152 3.1.4 参数文件小结.....................................................................163 3.2 跟踪文件...............................................................................163 3.2.1 请求的跟踪文件..................................................................165 3.2.2 针对内部错误生成的跟踪文件..................................................170 3.2.3 跟踪文件小结.....................................................................174 3.3 警告文件...............................................................................175 3.4 数据文件...............................................................................179 3.4.1 简要回顾文件系统机制..........................................................180 3.4.2 Oracle数据库中的存储层次体系..............................................181 3.4.3 字典管理和本地管理的表空间..................................................186 3.5 临时文件...............................................................................188 3.6 控制文件...............................................................................192 3.7 重做日志文件..........................................................................192 3.7.1 在线重做日志.....................................................................193 3.7.2 归档重做日志.....................................................................196 3.8 密码文件...............................................................................198 5 / 976 3.9 修改跟踪文件..........................................................................203 3.10 闪回日志文件........................................................................205 3.10.1 闪回数据库......................................................................205 3.10.2 闪回恢复区......................................................................206 3.11 DMP文件(EXP/IMP文件) .....................................................207 3.12 数据泵文件...........................................................................210 3.13 平面文件.............................................................................214 3.14 小结...................................................................................215 第 4章内存结构............................................................................215 4.1 进程全局区和用户全局区............................................................216 4.1.1 手动PGA内存管理..............................................................217 4.1.2 自动PGA内存管理..............................................................227 4.1.3 手动和自动内存管理的选择....................................................244 4.1.4 PGA和UGA小结...............................................................246 4.2 系统全局区............................................................................246 4.2.1 固定SGA .........................................................................254 4.2.2 重做缓冲区.......................................................................254 4.2.3 块缓冲区缓存.....................................................................256 4.2.4 共享池.............................................................................266 6 / 976 4.2.5 大池...............................................................................269 4.2.6 Java池............................................................................271 4.2.7 流池...............................................................................272 4.2.8 自动SGA内存管理..............................................................272 4.3 小结....................................................................................274 第 5章Oracle进程.........................................................................275 5.1 服务器进程............................................................................276 5.1.1 专用服务器连接..................................................................277 5.1.2 共享服务器连接..................................................................279 5.1.3 连接与会话.......................................................................280 5.1.4 专用服务器与共享服务器.......................................................289 5.1.5 专用/共享服务器小结...........................................................293 5.2 后台进程...............................................................................294 5.2.1 中心后台进程.....................................................................295 5.2.2 工具后台进程.....................................................................234 5.3 从属进程...............................................................................236 5.3.1 I/O从属进程.....................................................................236 5.3.2 并行查询从属进程...............................................................237 5.4 小结....................................................................................237 7 / 976 第 6章 锁.....................................................................................238 6.1 什么是锁?............................................................................238 6.2 锁定问题...............................................................................240 6.2.1 丢失更新..........................................................................240 6.2.2 悲观锁定..........................................................................241 6.2.3 乐观锁定..........................................................................243 6.2.4 乐观锁定还是悲观锁定?.......................................................256 6.2.5 阻塞...............................................................................257 6.2.6 死锁...............................................................................260 6.2.7 锁升级.............................................................................266 6.3 锁类型..................................................................................266 6.3.1 DML锁...........................................................................267 6.3.2 DDL锁............................................................................276 6.3.3 闩..................................................................................280 6.3.4 手动锁定和用户定义锁..........................................................290 6.4 小结....................................................................................291 第 7章 并发与多版本.......................................................................292 7.1 什么是并发控制?....................................................................292 7.2 事务隔离级别..........................................................................293 8 / 976 7.2.1 READ UNCOMMITTED........................................................294 7.2.2 READ COMMITTED............................................................296 7.2.3 REPEATABLE READ ............................................................297 7.2.4 SEAIALIZABLE...................................................................299 7.2.5 READ ONLY .....................................................................302 7.3 多版本读一致性的含义...............................................................302 7.3.1 一种会失败的常用数据仓库技术...............................................303 7.3.2 解释热表上超出期望的I/O .....................................................304 7.4 写一致性...............................................................................307 7.4.1 一致读和当前读..................................................................307 7.4.2 查看重启动.......................................................................311 7.4.3 为什么重启动对我们很重要?..................................................314 7.5 小结....................................................................................315 第 8章 事务..................................................................................317 8.1 事务控制语句..........................................................................317 8.2 原子性..................................................................................318 8.2.1 语句级原子性.....................................................................318 8.2.2 过程级原子性.....................................................................321 8.2.3 事务级原子性.....................................................................325 9 / 976 8.3 完整性约束和事务....................................................................325 8.3.1 IMMEDIATE 约束...............................................................325 8.3.2 DEFERRABLE 约束和级联更新.................................................326 8.4 不好的事务习惯.......................................................................329 8.4.1 在循环中提交.....................................................................329 8.4.2 使用自动提交.....................................................................337 8.5 分布式事务............................................................................338 8.6 自治事务...............................................................................340 8.6.1 自治事务如果工作? ............................................................340 8.6.2 何时使用自治事务? ............................................................343 8.7 小结....................................................................................348 第 9章 redo与undo ......................................................................349 9.1 什么是redo? ........................................................................349 9.2 什么是undo? .......................................................................350 9.2.1 redo和undo如何协作?......................................................353 9.3 提交和回滚处理.......................................................................357 9.3.1 COMMIT 做什么?..............................................................357 9.3.2 ROLLBACK做什么? ...........................................................365 9.4 分析redo..............................................................................366 10 / 976 9.4.1 测量redo.........................................................................367 9.4.2 redo生成和BEFORE/AFTER触发器.........................................369 9.4.3 我能关掉重做日志生成吗? ....................................................378 9.4.4 为什么不能分配一个新日志?..................................................383 9.4.5 块清除.............................................................................384 9.4.6 日志竞争..........................................................................388 9.4.7 临时表和redo/undo ...........................................................390 9.5 分析undo.............................................................................394 9.5.1 什么操作会生成最多和最少的undo?........................................394 9.5.2 ORA-01555:snapshot too old 错误.........................................397 9.6 小结....................................................................................409 第 10 章 数据库表...........................................................................411 10.1 表类型................................................................................411 10.2 术语...................................................................................412 10.2.1 段................................................................................413 10.2.2 段空间管理......................................................................414 10.2.3 高水位线........................................................................415 10.2.4 freelists .........................................................................417 10.2.5 PCTFREE 和PCTUSED........................................................421 11 / 976 10.2.6 LOGGING和NOLOGGING .................................................423 10.2.7 INITRANS 和MAXTRANS ...................................................424 10.3 堆组织表.............................................................................424 10.4 索引组织表...........................................................................427 10.5 索引聚簇表...........................................................................445 10.6 散列聚簇表...........................................................................455 10.7 有序散列聚簇表.....................................................................465 10.8 嵌套表................................................................................469 10.8.1 嵌套表语法......................................................................469 10.8.2 嵌套表存储......................................................................478 10.8.3 嵌套表小结......................................................................482 10.9 临时表................................................................................483 10.10对象表..............................................................................491 10.11小结.................................................................................500 第 11 章 索引................................................................................502 11.1 Oracle索引概述....................................................................502 11.2 B*树索引.............................................................................503 11.2.1 索引键压缩......................................................................506 11.2.2 反向键索引......................................................................509 12 / 976 11.2.3 降序索引........................................................................517 11.2.4 什么情况下应该使用B*树索引?.............................................519 11.2.5 B*树小结........................................................................532 11.3 位图索引.............................................................................532 11.3.1 什么情况下应该使用位图索引? .............................................533 11.3.2 位图联结索引...................................................................538 11.3.3 位图索引小结...................................................................541 11.4 基于函数的索引.....................................................................542 11.4.1 重要的实现细节................................................................542 11.4.2 一个简单的基于函数的索引例子.............................................543 11.4.3 只对部分行建立索引...........................................................554 11.4.4 实现有选择的惟一性...........................................................556 11.4.5 关于CASE 的警告..............................................................557 11.4.6 关于ORA-01743的警告.....................................................559 11.4.7 基于函数的索引小结...........................................................560 11.5 应用域索引...........................................................................560 11.6 关于索引的常见问题和神话........................................................562 11.6.1 视图能使用索引吗?...........................................................562 11.6.2 Null和索引能协作吗?........................................................562 13 / 976 11.6.3 外键是否应该加索引? ........................................................566 11.6.4 为什么没有使用我的索引?...................................................567 11.6.5 神话:索引中从不重用空间...................................................576 11.6.6 神话:最有差别的元素应该在最前面........................................581 11.7 小结...................................................................................585 第 12 章 数据类型...........................................................................587 12.1 Oracle数据类型概述...............................................................587 12.2 字符和二进制串类型................................................................589 12.2.1 NLS 概述........................................................................589 12.2.2 字符串...........................................................................593 12.3 二进制串:RAW类型..............................................................601 12.4 数值类型.............................................................................604 12.4.1 NUMBER类型的语法和用法.................................................607 12.4.2 BINARY_FLOAT/BINARY_DOUBLE 类型的语法和用法..................612 12.4.3 非固有数据类型................................................................612 12.4.4 性能考虑........................................................................613 12.5 LONG 类型..........................................................................615 12.5.1LONG和LONG RAW 类型的限制...........................................615 12.5.2 处理遗留的LONG类型.......................................................616 14 / 976 12.6 DATE、TIMESTAMP和INTERVAL类型........................................625 12.6.1 格式..............................................................................625 12.6.2 DATE 类型......................................................................626 12.6.3 TIMESTAMP类型.............................................................637 12.6.4 INTERVAL类型................................................................647 12.7 LOB 类型.............................................................................651 12.7.1 内部LOB........................................................................652 12.7.2 BFILE ............................................................................666 12.8 ROWID/UROWID 类型............................................................666 12.9 小结...................................................................................666 第 13 章 分区................................................................................666 13.1 分区概述.............................................................................666 13.1.1 提高可用性......................................................................666 13.1.2 减少管理负担...................................................................666 13.1.3 改善语句性能...................................................................666 13.2 表分区机制...........................................................................666 13.2.1 区间分区........................................................................666 13.2.2 散列分区........................................................................666 13.2.3 列表分区........................................................................666 15 / 976 13.2.4 组合分区........................................................................666 13.2.5 行移动...........................................................................666 13.2.6 表分区机制小结................................................................666 13.3 索引分区.............................................................................666 13.3.1 局部索引........................................................................666 13.3.2 全局索引........................................................................666 13.4 再论分区和性能.....................................................................666 13.5 审计和段空间压缩...................................................................666 13.6 小结...................................................................................666 第 14 章 并行执行...........................................................................666 14.1 何时使用并行执行...................................................................666 14.2 并行查询.............................................................................666 14.3 并行DML............................................................................666 14.4 并行DDL.............................................................................666 14.4.1 并行DDL和使用外部表的数据加载.........................................666 14.4.2 并行DDL和区段截断.........................................................666 14.5 并行恢复.............................................................................666 14.6 过程并行化...........................................................................666 14.6.1 并行管道函数...................................................................666 16 / 976 14.6.2 DIY 并行化......................................................................666 14.7 小结...................................................................................666 第 15 章 数据加载和卸载...................................................................666 15.1 SQL*Loader.........................................................................666 15.1.1 用SQLLDR加载数据的FAQ .................................................666 15.1.2 SQLLDR 警告...................................................................666 15.1.3 SQLLDR小结...................................................................666 15.2 外部表................................................................................666 15.2.1 建立外部表......................................................................666 15.2.2 处理错...........................................................................666 15.2.3 使用外部表加载不同的文件...................................................666 15.2.4 多用户问题......................................................................666 15.2.5 外部表小结......................................................................666 15.3 平面文件卸载........................................................................666 15.4 数据泵卸载...........................................................................666 15.5 小结...................................................................................666

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值