1 / 28

Chapter 11

Chapter 11. Large Objects. Need for Large Objects. Data type to store objects that contain large amount of text, log, image, video, or audio data. Most companies have a need to store this type of data. Oracle provides the following data types: LONG and LONG RAW

hart
Download Presentation

Chapter 11

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 11 Large Objects

  2. Need for Large Objects • Data type to store objects that contain large amount of text, log, image, video, or audio data. • Most companies have a need to store this type of data. • Oracle provides the following data types: • LONG and LONG RAW • Character large object (CLOB) • National character large object (NCLOB) • Binary large object (BLOB) • Binary file (BFILE)

  3. Understanding the Lob Architecture

  4. Understanding BFILEs

  5. BasicFilesversus SecureFiles • SecureFile is a new LOB architecture introduced in Oracle Database 11g. • The SecureFile architecture has many new enhancements that improve the manageability and performance of LOBs. • If you’re using Oracle Database 11g or higher, then you should create your LOB columns with the SECUREFILE clause. • If you’re not using Oracle Database 11g, then your only option is to use the BasicFilearchitecture. • This is the default type of LOB created, and it’s been available since Oracle version 8.

  6. SecureFileLOB Enhancements • Encryption (requires Oracle Advanced Security Option) • Compression (requires Oracle Advanced Compression Option) • Deduplication (requires Oracle Advanced Compression Option)

  7. Prerequisites for SecureFiles • A SecureFile LOB must be stored in a tablespace using the automated segment space management feature (ASSM). • The DB_SECUREFILE initialization setting must be either PERMITTED or ALWAYS.

  8. Creating a BasicFile LOB Column • Basic example: create table patchmain( patch_id number ,patch_descclob); • In prior code, table and LOB stored in the same tablespace.

  9. Default Behavior of LOBs • LOBs by default are created as BasicFiles. • Oracle creates a LOB segment and a LOB index for each LOB column. • The LOB segment has a name of this format: SYS_LOB<string>. • The LOB index has a name of this format: SYS_IL<string>. • The <string> is the same for each LOB segment and its associated index. • The LOB segment and index are created in the same tablespace as the table unless you specify a different tablespace. • By default, nearly 4000 bytes of a LOB are stored in the table row (inline). • With Oracle Database 11g release 2 and higher, a LOB segment and a LOB index aren’t created until a record is inserted into the table (the so-called deferred segment creation feature). This means DBA/ALL/USER_SEGMENTS and DBA/ALL/USER_EXTENTS have no information in them until a row is inserted into the table.

  10. Creating a LOB in a Different Tablespace from the Table • Allows you to maintain and manage LOB segment separately from table segment and extents. create table patchmain (patch_id number ,patch_descclob ,patch blob ) tablespace users lob (patch_desc) store as (tablespace clob_data) ,lob (patch) store as (tablespace blob_data);

  11. Creating a SecureFile LOB • Use the SECUREFILE clause • Must be in an ASSM managed tablespace create table patchmain( patch_id number ,patch_descclob) lob(patch_desc) store as securefile (tablespace lob_data);

  12. Partitioning a LOB • Allows you to spread out data across several tablespaces. CREATE TABLE patchmain( patch_id NUMBER ,region VARCHAR2(16) ,patch_descCLOB) LOB(patch_desc) STORE AS (TABLESPACE patch1) PARTITION BY LIST (REGION) ( PARTITION p1 VALUES ('EAST') LOB(patch_desc) STORE AS SECUREFILE (TABLESPACE patch1 COMPRESS HIGH) TABLESPACE inv_data1 , PARTITION p2 VALUES ('WEST') LOB(patch_desc) STORE AS SECUREFILE (TABLESPACE patch2 DEDUPLICATENOCOMPRESS) TABLESPACE inv_data2 , PARTITION p3 VALUES (DEFAULT) LOB(patch_desc) STORE AS SECUREFILE (TABLESPACE patch3 COMPRESS LOW) TABLESPACE inv_data3 );

  13. Moving a LOB • Allows you to move a LOB to a separate tablespace from the table. alter table patchmain move lob(patch_desc) store as basicfile (tablespace inv_clob);

  14. Adding a LOB Column • Business requirements frequently change. • You can add a column with a LOB datatype just like other datatypes. • Use ALTER TABLE ... ADD SQL> alter table inv add(inv_image blob);

  15. Removing a LOB Column • Business requirements frequently change. • Might want to rename the column before dropping it (to better determine if an application is using the column): SQL> alter table patchmain rename column patch_desc to patch_desc_old; SQL> alter table patchmain drop(patch_desc_old);

  16. Caching LOBs • By default, LOBs are not placed in the SGA. • Use CACHE to place LOB in buffer cache for reads/writes. • Use CACHE READS to place LOB in buffer cache for only reads. • NOCACHE is the default, the LOB is not placed in the buffer cache. create table patchmain( patch_id number ,patch_descclob) lob(patch_desc) store as (tablespace lob_data cache);

  17. Storing LOBsIn Line • By default, up to approximately 4000 characters of a LOB column are stored in line with the table row. • If the LOB is over 4000 characters, then Oracle automatically stores the LOB outside of the row data. • Main advantage of storing a LOB in row is that small LOBs (less than 4000 characters) require less I/O, because Oracle doesn’t have to search out of row for the LOB data. create table patchmain( patch_id number ,patch_descclob ,log_file blob) lob(patch_desc, log_file) store as ( tablespace lob_data enable storage in row);

  18. Storing LOBsOut of Line • You can explicitly instruct Oracle to store the LOB outside of the row with the DISABLE STORAGE IN ROW. create table patchmain( patch_id number ,patch_descclob ,log_file blob) lob(patch_desc, log_file) store as ( tablespace lob_data disable storage in row);

  19. SecureFile Compression • If you’re using SecureFileLOBs, then you can specify a degree of compression. • The benefit is that the LOBs consume much less space in the database. • The downside is that reading and writing the LOBs may take longer. ALTER TABLE patchmain MODIFY LOB(patch_desc) (COMPRESS HIGH);

  20. SecureFileDeduplicating • If you have an application where identical LOBs are associated with two or more rows, you should consider using the SecureFilededuplicationfeature. • When enabled, this instructs Oracle to check when a new LOB is inserted into a table and see whether that LOB is already stored in another row (for the same LOB column). • If it’s already stored, then Oracle stores a pointer to the existing identical LOB. • This can dramatically reduce space for applications that tend to store the same LOB value multiple times in multiple rows.

  21. SecureFileDeduplicating CREATE TABLE patchmain( patch_id NUMBER ,patch_descCLOB) LOB(patch_desc) STORE AS SECUREFILE (DEDUPLICATE) TABLESPACE inv_clob;

  22. SecureFile Encryption • You can transparently encrypt a SecureFile LOB column (just like any other column). • Before you use encryption features, you must set up an encryption wallet. CREATE TABLE patchmain( patch_id number ,patch_descclob) LOB(patch_desc) STORE AS SECUREFILE (encrypt) tablespace inv_clob;

  23. Migrating BasicFiles to SecureFiles • Create a new table, load the data from the old table, and rename the tables. • Move the table. • Redefine the table online.

  24. Viewing LOB Metadata • Troubleshooting often begins by looking at LOB metadata. select table_name,column_name,index_name ,tablespace_name from all_lobs order by table_name; • select • segment_name • ,segment_type • ,tablespace_name • from user_segments • where segment_name like 'SYS_LOB%' • or segment_name like 'SYS_IL%';

  25. Loading a CLOB or BLOB • Create directory • Create table • Load LOB into table using DBMS_LOB package

  26. Troubleshooting LOB space related issues select a.table_name ,a.column_name ,a.segment_name ,a.index_name ,b.bytes/1024/1024 meg_bytes from user_lobs a ,user_segments b where a.segment_name = b.segment_name;

  27. Determining Blocks used by LOB • DBMS_SPACE.SPACE_USAGE for blocks being used by a LOB. • Overloaded procedure, one form for BasicFile the other for SecureFile

  28. Summary • LOBs are a datatype you can use to store large objects such as text files, images, video, and so on. • Most companies have a need to store LOBs. • Use the SecureFile feature from 11g forward. • SecureFile is the new architecture and allows for advanced LOB management.

More Related