Oracle 11g新的SecureFile存储特性扩展了大对象(LOB)的灵活性和容量，本文研究如何有效地从BasicFile迁移到SecureFile存储，如何测量SecureFile vs BasicFile LOB的效率，以及如何为SecureFile LOB使用不同的压缩和重复数据删除选项。
我创建了一个新表TRBTKT.SECURE_TICKETS重新定义了原始表TRBTKT.TICKETS的结构，将原始的BasicFile LOB映射到对应的SecureFile LOB，然后执行了一条INSERT INTO … SELECT FROM DML语句将原始表中的内容传输到新表中。
在一个相对简单的应用程序环境中，这种载入方式足以应付，但目前在数据仓库和商业智能应用程序中广泛地使用了LOB，特别是存储那些包含了复杂商务交易的XML文档和医院信息系统捕获到的医疗影像数据。
为了便于说明，我使用清单1中的代码重新创建了这两个表，我使用了列表分区对这两个表进行了分区，在清单2中，我使用更大的数据重新载入TRBTKT.TICKETS表。
清单1 使用列表分区重新创建这两个表以模拟数据仓库环境
| 以下是引用片段： — 重新创建表TRBTKT.TICKETS，这次包括了一个STATUS列 CREATE TABLE trbtkt.tickets ( tkt_id NUMBER ,description VARCHAR2(30) ,submit_dtm TIMESTAMP ,status VARCHAR2(8) ,document BLOB ,scrnimg BLOB ) LOB(document) STORE AS BASICFILE (TABLESPACE basicfiles) ,LOB(scrnimg) STORE AS BASICFILE (TABLESPACE basicfiles) PARTITION BY LIST (status) ( PARTITION sts_open VALUES (‘OPEN’) ,PARTITION sts_pending VALUES (‘PENDING’) ,PARTITION sts_closed VALUES (‘CLOSED’) ,PARTITION sts_other VALUES (DEFAULT) ) ; –注释 COMMENT ON TABLE trbtkt.tickets IS ‘Contains Trouble Ticket transaction data’; COMMENT ON COLUMN trbtkt.tickets.tkt_id IS ‘Unique identifier for a Trouble Ticket’; COMMENT ON COLUMN trbtkt.tickets.description IS ‘Trouble Ticket Description’; COMMENT ON COLUMN trbtkt.tickets.submit_dtm IS ‘Trouble Ticket Submission Time Stamp’; COMMENT ON COLUMN trbtkt.tickets.status IS ‘Trouble Ticket Status’; COMMENT ON COLUMN trbtkt.tickets.document IS ‘Large Object (LOB) that contains a Microsoft Word document that provides detailed information about a Trouble Ticket’; COMMENT ON COLUMN trbtkt.tickets.scrnimg IS ‘Large Object (LOB) that contains a screen print or other image that helps a problem solver identify how to act on a Trouble Ticket’; — 创建索引和约束 CREATE UNIQUE INDEX trbtkt.tickets_pk_idx ON trbtkt.tickets(tkt_id) TABLESPACE users; ALTER TABLE trbtkt.tickets ADD CONSTRAINT tickets_pk PRIMARY KEY (tkt_id); —– — 创建表TRBTKT.SECURE_TICKETS的一个改良版本，使用分区划分SecureFile LOB的存储，基于STATUS 的值设置合适的保留周期 DROP TABLE trbtkt.secure_tickets PURGE; CREATE TABLE trbtkt.secure_tickets ( tkt_id NUMBER ,description VARCHAR2(30) ,submit_dtm TIMESTAMP ,status VARCHAR2(8) ,document BLOB ,scrnimg BLOB ) LOB(document) STORE AS SECUREFILE ( TABLESPACE securefiles DISABLE STORAGE IN ROW CACHE ) ,LOB(scrnimg) STORE AS SECUREFILE ( TABLESPACE securefiles DISABLE STORAGE IN ROW CACHE READS ) PARTITION BY LIST (status) ( PARTITION sts_open VALUES (‘OPEN’) LOB (document) STORE AS SECUREFILE (KEEP_DUPLICATES NOCOMPRESS) ,LOB (scrnimg) STORE AS SECUREFILE (KEEP_DUPLICATES COMPRESS) ,PARTITION sts_pending VALUES (‘PENDING’) LOB (document) STORE AS SECUREFILE (KEEP_DUPLICATES NOCOMPRESS) ,LOB (scrnimg) STORE AS SECUREFILE (DEDUPLICATE COMPRESS) ,PARTITION sts_closed VALUES (‘CLOSED’) LOB (document) STORE AS SECUREFILE (DEDUPLICATE COMPRESS) ,LOB (scrnimg) STORE AS SECUREFILE (DEDUPLICATE COMPRESS HIGH) ,PARTITION sts_other VALUES (DEFAULT) LOB (document) STORE AS SECUREFILE (DEDUPLICATE COMPRESS HIGH) ,LOB (scrnimg) STORE AS SECUREFILE (DEDUPLICATE COMPRESS HIGH) ) ; –注释 COMMENT ON TABLE trbtkt.secure_tickets IS ‘Contains Trouble Ticket transaction data’; COMMENT ON COLUMN trbtkt.secure_tickets.tkt_id IS ‘Unique identifier for a Trouble Ticket’; COMMENT ON COLUMN trbtkt.secure_tickets.description IS ‘Trouble Ticket Description’; COMMENT ON COLUMN trbtkt.secure_tickets.submit_dtm IS ‘Trouble Ticket Submission Time Stamp’; COMMENT ON COLUMN trbtkt.secure_tickets.status IS ‘Trouble Ticket Status’; COMMENT ON COLUMN trbtkt.secure_tickets.document IS ‘Large Object (LOB) that contains a Microsoft Word document that provides detailed information about a Trouble Ticket’; COMMENT ON COLUMN trbtkt.secure_tickets.scrnimg IS ‘Large Object (LOB) that contains a screen print or other image that helps a problem solver identify how to act on a Trouble Ticket’; |
清单2 使用附加数据重新载入表TRBTKT.TICKETS
SET SERVEROUTPUT ON TRUNCATE TABLE trbtkt.tickets; BEGIN trbtkt.pkg_securefiles.AddTroubleTicket ( tkt_id => 101 ,description => ‘Trouble Ticket 101’ ,submit_dts => ‘2008-12-31 23:45:00’ ,status => ‘OPEN’ ,docFileName => ‘New_101.doc’ ,imgFileName => ‘DBRIssues.jpg’ ); trbtkt.pkg_securefiles.AddTroubleTicket ( tkt_id => 102 ,description => ‘Trouble Ticket 102’ ,submit_dts => ‘2009-01-04 00:00:00’ ,status => ‘OPEN’ ,docFileName => ‘New_102.doc’ ,imgFileName => ‘Unresolved.jpg’ ); trbtkt.pkg_securefiles.AddTroubleTicket ( tkt_id => 103 ,description => ‘Trouble Ticket 103’ ,submit_dts => ‘2009-01-02 00:00:00’ ,status => ‘OPEN’ ,docFileName => ‘New_103.doc’ ,imgFileName => ‘Unresolved.jpg’ ); trbtkt.pkg_securefiles.AddTroubleTicket ( tkt_id => 104 ,description => ‘Trouble Ticket 104’ ,submit_dts => ‘2009-01-14 12:30:00’ ,status => ‘OPEN’ ,docFileName => ‘New_104.doc’ ,imgFileName => ‘DBRIssues.jpg’ ); trbtkt.pkg_securefiles.AddTroubleTicket ( tkt_id => 105 ,description => ‘Trouble Ticket 105’ ,submit_dts => ‘2009-01-09 00:00:00’ ,status => ‘OPEN’ ,docFileName => ‘New_105.doc’ ,imgFileName => ‘Unresolved.jpg’ ); trbtkt.pkg_securefiles.AddTroubleTicket ( tkt_id => 106 ,description => ‘Trouble Ticket 106’ ,submit_dts => ‘2009-01-11 00:00:00’ ,status => ‘OPEN’ ,docFileName => ‘New_106.doc’ ,imgFileName => ‘Unresolved.jpg’ ); trbtkt.pkg_securefiles.AddTroubleTicket ( tkt_id => 107 ,description => ‘Trouble Ticket 107’ ,submit_dts => ‘2009-01-16 00:00:00’ ,status => ‘OPEN’ ,docFileName => ‘New_107.doc’ ,imgFileName => ‘DBRIssues.jpg’ ); trbtkt.pkg_securefiles.AddTroubleTicket ( tkt_id => 108 ,description => ‘Trouble Ticket 108’ ,submit_dts => ‘2009-01-12 00:00:00’ ,status => ‘OPEN’ ,docFileName => ‘New_108.doc’ ,imgFileName => ‘Unresolved.jpg’ ); trbtkt.pkg_securefiles.AddTroubleTicket ( tkt_id => 109 ,description => ‘Trouble Ticket 109’ ,submit_dts => ‘2009-01-02 00:00:00’ ,status => ‘OPEN’ ,docFileName => ‘New_109.doc’ ,imgFileName => ‘Unresolved.jpg’ ); trbtkt.pkg_securefiles.AddTroubleTicket ( tkt_id => 110 ,description => ‘Trouble Ticket 110’ ,submit_dts => ‘2009-01-14 12:45:00’ ,status => ‘OPEN’ ,docFileName => ‘New_110.doc’ ,imgFileName => ‘DBRIssues.jpg’ ); trbtkt.pkg_securefiles.AddTroubleTicket ( tkt_id => 201 ,description => ‘Trouble Ticket 201’ ,submit_dts => ‘2008-12-31 23:45:00’ ,status => ‘PENDING’ ,docFileName => ‘New_101.doc’ ,imgFileName => ‘DBRIssues.jpg’ ); trbtkt.pkg_securefiles.AddTroubleTicket ( tkt_id => 202 ,description => ‘Trouble Ticket 202’ ,submit_dts => ‘2009-01-04 00:00:00’ ,status => ‘OPEN’ ,docFileName => ‘New_102.doc’ ,imgFileName => ‘Unresolved.jpg’ ); trbtkt.pkg_securefiles.AddTroubleTicket ( tkt_id => 203 ,description => ‘Trouble Ticket 203’ ,submit_dts => ‘2009-01-02 00:00:00’ ,status => ‘CLOSED’ ,docFileName => ‘New_103.doc’ ,imgFileName => ‘Unresolved.jpg’ ); trbtkt.pkg_securefiles.AddTroubleTicket ( tkt_id => 204 ,description => ‘Trouble Ticket 204’ ,submit_dts => ‘2009-01-14 12:30:00’ ,status => ‘OPEN’ ,docFileName => ‘New_104.doc’ ,imgFileName => ‘DBRIssues.jpg’ ); trbtkt.pkg_securefiles.AddTroubleTicket ( tkt_id => 205 ,description => ‘Trouble Ticket 205’ ,submit_dts => ‘2009-01-09 00:00:00’ ,status => ‘OPEN’ ,docFileName => ‘New_105.doc’ ,imgFileName => ‘Unresolved.jpg’ ); trbtkt.pkg_securefiles.AddTroubleTicket ( tkt_id => 206 ,description => ‘Trouble Ticket 206’ ,submit_dts => ‘2009-01-11 00:00:00’ ,status => ‘PENDING’ ,docFileName => ‘New_106.doc’ ,imgFileName => ‘Unresolved.jpg’ ); trbtkt.pkg_securefiles.AddTroubleTicket ( tkt_id => 207 ,description => ‘Trouble Ticket 207’ ,submit_dts => ‘2009-01-16 00:00:00’ ,status => ‘OPEN’ ,docFileName => ‘New_107.doc’ ,imgFileName => ‘DBRIssues.jpg’ ); trbtkt.pkg_securefiles.AddTroubleTicket ( tkt_id => 208 ,description => ‘Trouble Ticket 208’ ,submit_dts => ‘2009-01-12 00:00:00’ ,status => ‘OPEN’ ,docFileName => ‘New_108.doc’ ,imgFileName => ‘Unresolved.jpg’ ); trbtkt.pkg_securefiles.AddTroubleTicket ( tkt_id => 209 ,description => ‘Trouble Ticket 209’ ,submit_dts => ‘2009-01-02 00:00:00’ ,status => ‘PENDING’ ,docFileName => ‘New_109.doc’ ,imgFileName => ‘Unresolved.jpg’ ); trbtkt.pkg_securefiles.AddTroubleTicket ( tkt_id => 210 ,description => ‘Trouble Ticket 210’ ,submit_dts => ‘2009-01-14 12:45:00’ ,status => ‘OPEN’ ,docFileName => ‘New_110.doc’ ,imgFileName => ‘DBRIssues.jpg’ ); trbtkt.pkg_securefiles.AddTroubleTicket ( tkt_id => 301 ,description => ‘Trouble Ticket 301’ ,submit_dts => ‘2008-12-31 23:45:00’ ,status => ‘CLOSED’ ,docFileName => ‘New_101.doc’ ,imgFileName => ‘DBRIssues.jpg’ ); trbtkt.pkg_securefiles.AddTroubleTicket ( tkt_id => 302 ,description => ‘Trouble Ticket 302’ ,submit_dts => ‘2009-01-04 00:00:00’ ,status => ‘OPEN’ ,docFileName => ‘New_102.doc’ ,imgFileName => ‘Unresolved.jpg’ ); trbtkt.pkg_securefiles.AddTroubleTicket ( tkt_id => 303 ,description => ‘Trouble Ticket 303’ ,submit_dts => ‘2009-01-02 00:00:00’ ,status => ‘OPEN’ ,docFileName => ‘New_103.doc’ ,imgFileName => ‘Unresolved.jpg’ ); trbtkt.pkg_securefiles.AddTroubleTicket ( tkt_id => 304 ,description => ‘Trouble Ticket 304’ ,submit_dts => ‘2009-01-14 12:30:00’ ,status => ‘CLOSED’ ,docFileName => ‘New_104.doc’ ,imgFileName => ‘DBRIssues.jpg’ ); trbtkt.pkg_securefiles.AddTroubleTicket ( tkt_id => 305 ,description => ‘Trouble Ticket 305’ ,submit_dts => ‘2009-01-09 00:00:00’ ,status => ‘PENDING’ ,docFileName => ‘New_105.doc’ ,imgFileName => ‘Unresolved.jpg’ ); trbtkt.pkg_securefiles.AddTroubleTicket ( tkt_id => 306 ,description => ‘Trouble Ticket 306’ ,submit_dts => ‘2009-01-11 00:00:00’ ,status => ‘CLOSED’ ,docFileName => ‘New_106.doc’ ,imgFileName => ‘Unresolved.jpg’ ); trbtkt.pkg_securefiles.AddTroubleTicket ( tkt_id => 307 ,description => ‘Trouble Ticket 307’ ,submit_dts => ‘2009-01-16 00:00:00’ ,status => ‘OPEN’ ,docFileName => ‘New_107.doc’ ,imgFileName => ‘DBRIssues.jpg’ ); trbtkt.pkg_securefiles.AddTroubleTicket ( tkt_id => 308 ,description => ‘Trouble Ticket 308’ ,submit_dts => ‘2009-01-12 00:00:00’ ,status => ‘OPEN’ ,docFileName => ‘New_108.doc’ ,imgFileName => ‘Unresolved.jpg’ ); trbtkt.pkg_securefiles.AddTroubleTicket ( tkt_id => 309 ,description => ‘Trouble Ticket 309’ ,submit_dts => ‘2009-01-02 00:00:00’ ,status => ‘CLOSED’ ,docFileName => ‘New_109.doc’ ,imgFileName => ‘Unresolved.jpg’ ); trbtkt.pkg_securefiles.AddTroubleTicket ( tkt_id => 310 ,description => ‘Trouble Ticket 310’ ,submit_dts => ‘2009-01-14 12:45:00’ ,status => ‘CLOSED’ ,docFileName => ‘New_110.doc’ ,imgFileName => ‘DBRIssues.jpg’ ); COMMIT; END; / — 收集优化器统计信息 BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(ownname => ‘TRBTKT’, CASCADE => TRUE); END; / |
我们一直都在努力坚持原创.......请不要一声不吭，就悄悄拿走。
我原创，你原创，我们的内容世界才会更加精彩！
【所有原创内容版权均属TechTarget，欢迎大家转发分享。但未经授权，严禁任何媒体（平面媒体、网络媒体、自媒体等）以及微信公众号复制、转载、摘编或以其他方式进行使用。】
微信公众号
TechTarget
官方微博
TechTarget中国
翻译
相关推荐
-
DBA在使用SQL语句时需要注意的问题
Michael McLaughlin是Oracle 11g和MySQL 5.6开发者指南的作者，他在书中为Oracle数据库开发者提供了一些建议与帮助。
-
Oracle数据库不同版本的硬件需求
每个企业的IT环境都是不同的，这些环境中可能会包含虚拟服务器、各种网络操作系统、基于云计算的服务、SAN等等，根据应用场景选择硬件是基本的准则。
-
Oracle 11g SQL性能分析器技术指南
当今信息时代要求企业的IT系统能够始终保证系统的性能并且提供更高的服务质量，我们的IT系统经常要面对应用程序升级、硬件更新、数据库升级、对象结构变更等导致SQL性能下降的问题。
-
Oracle 11g SecureFile技术详解手册
SecureFile不仅是新一代 LOB，它们还为 LOB 带来了更多的价值，尤其是以前只能在文件系统领域中获得的特性。