1 / 39

Advanced Table Management in Oracle 9i: LOB and Index-Organized Tables

This chapter delves into advanced table management techniques in Oracle 9i, focusing on Large Object (LOB) columns and index-organized tables. Learn how to create tables with LOB columns, understanding storage options and the types of LOBs (internal and external). Discover the use of new packages for LOB management, the semantics of copying LOBs, and effective storage management strategies. Additionally, explore index-organized tables for efficient data retrieval by primary key. Gain insights into analyzing table performance to support SQL query optimization.

redford
Télécharger la présentation

Advanced Table Management in Oracle 9i: LOB and Index-Organized Tables

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 8 Advanced Table Management Oracle9i Database Administrator: Implementation and Administration

  2. Objectives • Create tables with large object (LOB) columns and tables that are index-organized • Understand the tasks involved in table management • Use data dictionary views to find information about tables and their underlying structures Oracle9i Database Administrator: Implementation and Administration

  3. Advanced Table Structures Table types covered: • Tables with LOB columns: • Store large amount of data in a single column of one row • Can define a separate segment for the LOB • Index-organized tables • Store data in primary key order • Best when queries nearly always use primary key to look up data in the table Oracle9i Database Administrator: Implementation and Administration

  4. Tables with LOB Columns Types of LOB columns: • Internal LOB • Stored inside the database • Datatypes: BLOB, CLOB, NCLOB • External LOB • Stored outside the database • Datatypes: BFILE • Read-only Oracle9i Database Administrator: Implementation and Administration

  5. Tables with LOB Columns Example: CREATE TABLE SOUNDBYTES (ALBUM_ID VARCHAR2(20), ARTIST VARCHAR2(40), CD_MUSIC BFILE, POSTER_SHOT BLOB ); External LOB Internal LOB Oracle9i Database Administrator: Implementation and Administration

  6. Tables with LOB Columns New package for updating internal LOBs: • DBMS_LOB.LOADBLOBFROMFILE: Load data into BLOB from external file • DBMS_LOB.LOADCLOBFROMFILE: Load data into CLOB from external file Co Oracle9i Database Administrator: Implementation and Administration

  7. Tables with LOB Columns Semantics of copying LOBs: • Internal LOB uses copy semantics: Copying a LOB from one row or column to another copies the contents • External LOB uses reference semantics: Copying a LOB from one row or column to another copies only a pointer to the original location of contents Co Oracle9i Database Administrator: Implementation and Administration

  8. LOB Storage • Internal LOBs: • Default stored inline or out of line, by size • Always stored out of line with DISABLE STORAGE IN ROW • External LOBs: • LOB locator stored in the table • LOB data stored in operating system file Oracle9i Database Administrator: Implementation and Administration

  9. LOB Storage Syntax: CREATE TABLE <tablename> (<other_column_specs>, <LOBcolumnname> <LOBdatatype>) LOB (<LOBcolumnname>) STORE AS <lobsegmentname> (TABLESPACE <tablespacename> ENABLE STORAGE IN ROW|DISABLE STORAGE IN ROW CHUNK <nn> STORAGE (INITIAL <nn> NEXT <nn> MAXEXTENTS UNLIMITED|<nn>) PCTVERSION <nn>|RETENTION LOGGING|NOLOGGING CACHE|NOCACHE); Oracle9i Database Administrator: Implementation and Administration

  10. LOB Storage Syntax of storage for LOB: • STORE AS <lobsegmentname> • DISABLE / ENABLE STORAGE IN ROWS • CHUNK <nn> • PCTVERSION / RETENTION • LOGGING / NOLOGGING • CACHE / NOCACHE Oracle9i Database Administrator: Implementation and Administration

  11. LOB Storage Example: Oracle9i Database Administrator: Implementation and Administration

  12. Index-Organized Tables • Relational table with primary key • Sorted and stored in key order • Normal relational tables are heap-organized • Stored in b-tree structure Oracle9i Database Administrator: Implementation and Administration

  13. Index-Organized Tables • Heap-organized versus Index-organized Oracle9i Database Administrator: Implementation and Administration

  14. Index-Organized Tables • Example: Oracle9i Database Administrator: Implementation and Administration

  15. Index-Organized Tables Syntax components: • ORGANIZATION INDEX • OVERFLOW • PCTTHRESHOLD • INCLUDING <col> • TABLESPACE <name> • STORAGE (<storage>) Oracle9i Database Administrator: Implementation and Administration

  16. Index-Organized Tables Example: CREATE TABLE ZIPREFERENCES (ZIPCODE VARCHAR2(10) NOT NULL, CITY VARCHAR2(40) NOT NULL, STATE VARCHAR2(10) NOT NULL, PROVINCE VARCHAR2(10) NOT NULL, COUNTRY VARCHAR2(20) NOT NULL, CONSTRAINT ZIP_PK PRIMARY KEY(ZIPCODE)) ORGANIZATION INDEX TABLESPACE USERS PCTTHRESHOLD 20 OVERFLOW TABLESPACE USERS STORAGE (INITIAL 64K NEXT 32K MAXEXTENTS 50 PCTINCREASE 0); Oracle9i Database Administrator: Implementation and Administration

  17. Overview of Table Management Types of changes: • Change the storage settings • Reorganize the table online • Drop columns • Truncate or drop the table Oracle9i Database Administrator: Implementation and Administration

  18. Analyzing a Table Why analyze tables? • To give the optimizer up-to-date information for optimizing queries and other SQL commands • To give you information to help you decide which storage or column settings to change Oracle9i Database Administrator: Implementation and Administration

  19. Analyzing a Table Oracle's optimizers: • Cost-based optimizer • Default • Uses table statistics • Rule-based optimizer • Older • Uses syntax rules only Oracle9i Database Administrator: Implementation and Administration

  20. Analyzing a Table Examples • ANALYZE command: ANALYZE TABLE CUSTOMER ESTIMATE STATISTICS SAMPLE 1000 ROWS; • DBMS_STATS package: EXECUTE DBMS_STATS.GATHER_TABLE_STATS - ('CLASSMATE','CUSTOMER'); Oracle9i Database Administrator: Implementation and Administration

  21. Adjusting Table Storage Structure Syntax of ALTER TABLE command: ALTER TABLE <schema>.<tablename> PCTFREE <nn> PCTUSED <nn> INITTRANS <nn> MAXTRANS <nn> STORAGE (NEXT <nn> PCTINCREASE <nn> MAXEXTENTS <nn>|UNLIMITED) ALLOCATE EXTENT SIZE <nn> DATAFILE <filename> DEALLOCATE UNUSED KEEP <nn> COMPRESS|NOCOMPRESS MOVE TABLESPACE <tablespacename> STORAGE (INITIAL <nn> NEXT <nn> PCTINCREASE <nn> MAXEXTENTS <nn>|UNLIMITED) COMPRESS|NOCOMPRESS ONLINE Oracle9i Database Administrator: Implementation and Administration

  22. Adjusting Table Storage Structure Syntax components: • PCTFREE <nn> PCTUSED <nn> • INITTRANS <nn> MAXTRANS <nn> • STORAGE (...) • ALLOCATE EXTENT • DEALLOCATE UNUSED • COMPRESS • MOVE • ONLINE Oracle9i Database Administrator: Implementation and Administration

  23. Adjusting Table Storage Structure Examples: • Deallocate unused space: ALTER TABLE HORSERACE DEALLOCATE UNUSED KEEP 50K; • Change STORAGE setting: ALTER TABLE CLASSIFIED_AD MOVE TABLESPACE USERS STORAGE (NEXT 56K); Oracle9i Database Administrator: Implementation and Administration

  24. Reorganizing a Table DBMS_REDEFINITION package: • Redefine anything in the table • Keep old version of the table available during the change Oracle9i Database Administrator: Implementation and Administration

  25. Reorganizing a Table DBMS_REDEFINITION steps: • Check the table – is it eligible • Create interim table with changes you want done • Start redefinition process • Finish redefinition process Each step (except #2) is a call to DBMS_REDEFINITION package Oracle9i Database Administrator: Implementation and Administration

  26. Making Other Table Changes Syntax of the ALTER TABLE command: ALTER TABLE <schema>.<tablename> RENAME TO <newname> LOGGING|NOLOGGING MONITORING|NOMONITORING ENABLE|DISABLE ROW MOVEMENT CACHE|NOCACHE Oracle9i Database Administrator: Implementation and Administration

  27. Making Other Table Changes ALTER TABLE components: • RENAME TO <newname> • LOGGING / NOLOGGING • MONITORING / NOMONITORING • ENABLE / DISABLE ROW MOVEMENT • CACHE / NOCACHE Oracle9i Database Administrator: Implementation and Administration

  28. Making Other Table Changes Example: Modify monitoring and cache: ALTER TABLE EMPLOYEE MONITORING CACHE; Oracle9i Database Administrator: Implementation and Administration

  29. Dropping, Adding, or Modifying a Column in a Table • Syntax for ALTER TABLE command: ALTER TABLE <schema>.<tablename> RENAME COLUMN <oldcolname> TO <newcolname> ADD (<colname> <datatype>, ... ) MODIFY (<colname> <datatype>, ... ) DROP (<colname>, <colname>,...)|COLUMN <colname> CASCADE CONSTRAINTS SET UNUSED (<colname>,<colname>,...)|COLUMN <colname> CASCADE CONSTRAINTS DROP UNUSED COLUMNS Note: Each of these clauses must be used alone in a single ALTER TABLE command. Oracle9i Database Administrator: Implementation and Administration

  30. Dropping, Adding, or Modifying a Column in a Table Examples: • Rename a column: ALTER TABLE CH08SURGERY RENAME COLUMN PATIENT_FISRT_NAME TO PATIENT_FIRST_NAME; • Add a column: ALTER TABLE CH08SURGERY ADD (OUTCOME VARCHAR2(40), OPERATING_ROOM_NO CHAR(4)); • Change length of column: ALTER TABLE CH08SURGERY MODIFY (DOCTOR_NAME VARCHAR2(20)); Oracle9i Database Administrator: Implementation and Administration

  31. Truncating and Dropping a Table TRUNCATE is fast: • No redo log entries • No undo records • Cannot rollback • Syntax: TRUNCATE TABLE <schema>.<tablename> DROP STORAGE|REUSE STORAGE Oracle9i Database Administrator: Implementation and Administration

  32. Truncating and Dropping a Table DROP command: • Removes data and structure • Must drop constraints that use the dropped table • Example: DROP TABLE COPCAR CASCADE CONSTRAINTS; Oracle9i Database Administrator: Implementation and Administration

  33. Querying Table-Related Data Dictionary Views Oracle9i Database Administrator: Implementation and Administration

  34. Querying Table-Related Data Dictionary Views Oracle9i Database Administrator: Implementation and Administration

  35. Querying Table-Related Data Dictionary Views Example: • Look at statistics gathered by monitoring tables: SELECT TABLE_NAME, TABLESPACE_NAME, MONITORING, BLOCKS, EMPTY_BLOCKS FROM USER_TABLES; Oracle9i Database Administrator: Implementation and Administration

  36. Chapter Summary • LOB data types are either internal LOBS or external LOBS • New features in the DBMS_LOB package simplify loading LOB data • Internal LOB values larger than 4000 bytes are stored out of line in a LOB data segment • Oracle9i works with LOBs by reading and writing one chunk at a time • LOB data segments can also be used to store varray data Oracle9i Database Administrator: Implementation and Administration

  37. Chapter Summary • Index-organized tables require a primary key and store data in order • Optionally split the column data into the main segment and an overflow segment • The ANALYZE command gathers statistics • The DBMS_STATS package also gathers statistics • Change a table’s storage parameters using the ALTER TABLE command Oracle9i Database Administrator: Implementation and Administration

  38. Chapter Summary • DBMS_REDEFINITION restructures a table while keeping it online • ALTER TABLE can do these tasks: • Move table to different tablespace • Release unused space • Rename the table • Modify columns (add, change, drop) • TRUNCATE removes rows permanently Oracle9i Database Administrator: Implementation and Administration

  39. Chapter Summary • TRUNCATE can release unused space or keep the space • DROP TABLE removes data and structure • CASCADE CONSTRAINTS if the table being dropped is connected by constraints to other tables Oracle9i Database Administrator: Implementation and Administration

More Related