10 Managing Schema Objects Copyright 2006 Oracle All

Objectives Table Types Partition IOT Cluster DBA Tasks 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: – – 10 -2 Estimating the size of new tables Analyzing growth trends Managing optimizer statistics Reorganizing schema objects online Copyright © 2006, Oracle. All rights reserved.

Table Types Clustered Heap Type Description Ordinary (heaporganized) 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. Partitioned 10 -3 IOT Copyright © 2006, Oracle. All rights reserved.

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 10 -4 Copyright © 2006, Oracle. All rights reserved.

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 10 -5 Copyright © 2006, Oracle. All rights reserved.

Creating a Partition 10 -6 Copyright © 2006, Oracle. All rights reserved.

Partitioning Methods • • • 10 -7 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 Copyright © 2006, Oracle. All rights reserved.

Partition Maintenance 10 -8 Copyright © 2006, Oracle. All rights reserved.

Index-Organized Tables Regular table access Table Types Partition > IOT Cluster DBA Tasks IOT access Table access by ROWID Non-key columns Key column Row header 10 -9 Copyright © 2006, Oracle. All rights reserved.

Index-Organized Tables and Heap Tables • Compared to heap tables, IOTs: – – – • IOTs have the following restrictions: – – 10 -10 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 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 Copyright © 2006, Oracle. All rights reserved.

Creating Index-Organized Tables SQL> CREATE TABLE country 2 ( country_id CHAR(2) 3 CONSTRAINT country_id_nn NOT NULL, 4 country_name VARCHAR 2(40), 5 currency_name VARCHAR 2(25), 6 currency_symbol VARCHAR 2(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; 10 -12 Copyright © 2006, Oracle. All rights reserved.

Table Types Partition IOT > Cluster DBA Tasks Clusters ORD_NO ----101 102 102 101 PROD -----A 4102 A 2091 G 7830 N 9587 A 5675 W 0824 QTY -----20 11 20 26 19 10 ORD_NO -----101 102 . . . ORD_DT CUST_CD -----05 -JAN-97 R 01 07 -JAN-97 N 45 Unclustered orders and order_item tables 10 -13 Cluster Key (ORD_NO) 101 ORD_DT CUST_CD 05 -JAN-97 R 01 PROD QTY A 4102 20 A 5675 19 W 0824 10 102 ORD_DT CUST_CD 07 -JAN-97 N 45 PROD QTY A 2091 11 G 7830 20 N 9587 26 Clustered orders and order_item tables Copyright © 2006, Oracle. All rights reserved.

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

Situations Where Clusters Are Useful Criterion Index 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 10 -16 Copyright © 2006, Oracle. All rights reserved. Hash Sorted hash

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. – ORDER BY clause is not mandatory to retrieve rows in ascending order. 10 -17 Copyright © 2006, Oracle. All rights reserved.

Sorted Hash Cluster: Example CREATE CLUSTER calls_cluster ( origin_number NUMBER Cluster key , call_timestamp NUMBER SORT Sort key , call_duration NUMBER SORT) HASHKEYS 10000 SINGLE TABLE HASH IS origin_number SIZE 50; CREATE TABLE calls ( origin_number NUMBER , call_timestamp NUMBER , call_duration NUMBER , other_info VARCHAR 2(30)) CLUSTER calls_cluster( origin_number, call_timestamp, call_duration ); 10 -18 Copyright © 2006, Oracle. All rights reserved.

Sorted Hash Cluster: Basic Architecture SIZE Cluster key 1 HASHKEYS Cluster key 2 … Cluster key n Rows sorted by sort key in each block chain … 10 -19 Copyright © 2006, Oracle. All rights reserved. Block chain starting points

Schema Management Tasks DBA tasks include: • Estimating the size of new tables • Analyzing growth trends • Managing optimizer statistics • Reorganizing schema objects online 10 -20 Copyright © 2006, Oracle. All rights reserved. Table Types Partition IOT Cluster > DBA Tasks

Estimating Resource Usage 10 -21 Copyright © 2006, Oracle. All rights reserved.

Analyzing Growth Trends EM growth trend report: • Used by the Segment Advisor •

Managing Optimizer Statistics 2 3 Not analyzed 10 -23 Copyright © 2006, Oracle. All

Reorganizing Schema Objects Online • • • 10 -24 Modifying logical or physical structure of a schema object, such as a table or index Transparent to users Space requirements Copyright © 2006, Oracle. All rights reserved.

Reorganizing Objects: Impact Report 10 -26 Copyright © 2006, Oracle. All rights reserved.

Reorganizing Objects: Review 10 -27 Copyright © 2006, Oracle. All rights reserved.

Basic Steps for Manual Online Reorganization 1. Verify that the table is a candidate for online reorganization. 2. Create an interim table. 3. Start the redefinition process. 4. Copy dependent objects. (This automatically creates any triggers, indexes, grants, and constraints on the interim table. ) 5. Query the DBA_REDEFINITION_ERRORS view to check for errors. 6. Optionally, synchronize the interim table. 7. Complete the redefinition. 8. Drop the interim table. 10 -28 Copyright © 2006, Oracle. All rights reserved.

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: – – 10 -29 Estimating the size of new tables Analyzing growth trends Managing optimizer statistics Reorganizing schema objects online Copyright © 2006, Oracle. All rights reserved.

Practice Overview: Managing Schema Objects This practice covers the following topics: • Monitoring table and index space usage • Managing optimizer statistics • Reorganizing table and index 10 -30 Copyright © 2006, Oracle. All rights reserved.