Managing Schema Objects

1. Managing Schema Objects

2. Table Types Partition IOT Cluster DBA Tasks Objectives • After completing this lesson, you should be able to manage schema objects and: • Determine appropriate table types for your requirements: heap, partition, IOT, or cluster • Perform related DBA tasks: • Estimating the size of new tables • Analyzing growth trends • Managing optimizer statistics • Reorganizing schema objects online

3. Type Description Ordinary (heap-organized) table Data is stored as an unordered collection (heap). Partitioned table Data is divided into smaller, more manageable pieces. Index-organized table (IOT) Data (including non-key values) is sorted and stored in a B-tree index structure. Clustered table Related data from more than one table are stored together. Table Types Clustered Heap Partitioned IOT

4. What Is a Partition and Why Use It? Table Types > Partition IOT Cluster DBA Tasks • A partition is: • A piece of a “very large” table or index • Stored in its own segment • Used for improved performance and manageability

5. Partitions • Characteristics of partitions are: • Same logical attributes: Same columns, constraints, and indexes • Different physical attributes: Stored in different tablespaces • Transparent to applications • Several partitioning methods

6. Creating a Partition

7. Partitioning Methods • Range partitioning: Maps rows based on logical ranges of columns values—for example, months in a year • Hash partitioning: Maps rows based on the hash value of the partitioning key • List partitioning: Maps rows based on a discrete list of values, provided by the DBA • Range-hash partitioning: Maps rows using the range method, and within each range partition, creates hash subpartitions • Range-list partitioning: Maps rows first based on a range of values, then based on discrete values

8. Partition Maintenance

9. Index-Organized Tables Table Types Partition > IOT Cluster DBA Tasks Regular table access IOT access Table accessby ROWID Non-key columns Key column Row header

10. Index-Organized Tablesand Heap Tables • Compared to heap tables, IOTs: • Have faster key-based access to table data • Do not duplicate the storage of primary key values • Require less storage • Use secondary indexes and logical row IDs • Have higher availability because table reorganization does not invalidate secondary indexes • IOTs have the following restrictions: • Must have a primary key that is not DEFERRABLE • Cannot be clustered • Cannot use composite partitioning • Cannot contain a column of type ROWID or LONG

11. Creating Index-Organized Tables SQL> CREATE TABLE country 2 ( country_id CHAR(2) 3 CONSTRAINT country_id_nn NOT NULL, 4 country_name VARCHAR2(40), 5 currency_name VARCHAR2(25), 6 currency_symbol VARCHAR2(3), 7 map BLOB, 8 flag BLOB, 9 CONSTRAINT country_c_id_pk 10 PRIMARY KEY (country_id)) 11 ORGANIZATION INDEX 12 TABLESPACE indx 13 PCTTHRESHOLD 20 14 OVERFLOW TABLESPACE users;

12. Table Types Partition IOT > Cluster DBA Tasks Clusters Cluster Key (ORD_NO) 101 ORD_DT CUST_CD 05-JAN-97 R01 PROD QTY A4102 20 A5675 19 W0824 10 102 ORD_DT CUST_CD 07-JAN-97 N45 PROD QTY A2091 11 G7830 20 N9587 26 ORD_NO PROD QTY ... ----- ------ ------ 101 A4102 20 102 A2091 11 102 G7830 20 102 N9587 26 101 A5675 19 101 W0824 10 ORD_NO ORD_DT CUST_CD ------ ------ ------ 101 05-JAN-97 R01 102 07-JAN-97 N45 Unclustered orders and order_item tables Clustered orders and order_item tables

13. 1 2 3 Cluster Types Hash cluster Sorted hash cluster Index cluster Hash function Hash function

14. Criterion Index Hash Sorted hash Uniform key distribution Evenly distributed key values Rarely updated key Often joined master-detail tables Predictable number of key values Queries using equality predicate on key Data is retrieved in the order it was inserted Situations Where Clusters Are Useful

15. Sorted Hash Cluster: Overview • New data structure used to store data sorted by nonprimary key columns: • Cluster key values are hashed. • Rows corresponding to a particular cluster key value are sorted according to the sort key. • Used to guarantee that row order is returned by queries without sorting data: • Rows are returned in ascending or descending order for a particular cluster key value. • ORDERBY clause is not mandatory to retrieve rows in ascending order.

16. Sorted Hash Cluster: Example CREATE CLUSTER calls_cluster ( origin_number NUMBER , call_timestamp NUMBER SORT , call_duration NUMBER SORT) HASHKEYS 10000 SINGLE TABLE HASH IS origin_number SIZE 50; Cluster key Sort key CREATE TABLE calls ( origin_number NUMBER , call_timestamp NUMBER , call_duration NUMBER , other_info VARCHAR2(30)) CLUSTER calls_cluster( origin_number,call_timestamp,call_duration );

17. Sorted Hash Cluster: Basic Architecture SIZE Cluster key 1 Cluster key 2 Block chainstarting points HASHKEYS … Cluster key n Rows sorted bysort key in eachblock chain …

18. Schema Management Tasks Table Types Partition IOT Cluster > DBA Tasks • DBA tasks include: • Estimating the size of new tables • Analyzing growth trends • Managing optimizer statistics • Reorganizing schema objects online

19. Estimating Resource Usage

20. Analyzing Growth Trends • EM growth trend report: • Used by the Segment Advisor • Space usage statistics collected into AWR

21. Managing Optimizer Statistics 2 3 1 Not analyzed

22. Reorganizing Schema Objects Online • Modifying logical or physical structure of a schema object, such as a table or index • Transparent to users • Space requirements

23. Reorganizing Objects:Impact Report

24. Reorganizing Objects: Review

25. Basic Steps for Manual Online Reorganization • Verify that the table is a candidate for online reorganization. • Create an interim table. • Start the redefinition process. • Copy dependent objects. (This automatically creates any triggers, indexes, grants, and constraints on the interim table.) • Query the DBA_REDEFINITION_ERRORS view to check for errors. • Optionally, synchronize the interim table. • Complete the redefinition. • Drop the interim table.

26. Summary In this lesson, you should have learned how to manage schema objects and: • Determine appropriate table types for your requirements • Perform related DBA tasks: • Estimating the size of new tables • Analyzing growth trends • Managing optimizer statistics • Reorganizing schema objects online

27. Practice Overview:Managing Schema Objects • This practice covers the following topics: • Monitoring table and index space usage • Managing optimizer statistics • Reorganizing table and index