Storage Structure

1

• Storage Structure
• for Oracle Objects

Eric Langager Beihang College of Software
2
Introduction to Storage Structures

• Internal structures store
• The structure of every table, view, and other
  objects you create
• The data you load into your tables and other
  objects
• Information about the structure of tables, views,
  etc. (metadata)

3
Introduction to Storage Structures

• Physical structures
• Datafiles
• Operating system blocks
• Redo log files
• Control files

4
Introduction to Storage Structures

• Logical Structures
• Data block
• Extent
• Segment
• Schema object
• Tablespace

5
Introduction to Storage Structures
Logical data blocks map directly to contiguous
operating system blocks in datafiles
6
Segment Types and Their Uses

• Data segment for tables, object tables, triggers
• Index segment for indexes
• Temporary segment for sorting operations and
  temporary tables
• Rollback segment for undo data
• LOB segment for LOB data stored separately from
  the rest of the tables data

7
Introduction to Table Structures

• Table types
• Relational
• Index-organized
• Object
• Temporary
• External
• Nested
• XML
• Cluster

8
Setting Block Space Usage

• Syntax of the CREATE TABLE command
• CREATE TABLE lttablenamegt
• (ltcolumn specificationsgt)
• ltconstraintsgt
• TABLESPACE lttablespacenamegt
• STORAGE (ltstorage settingsgt)

Area of focus in this chapter
9
Setting Block Space Usage

• Expanded STORAGE clause syntax
• TABLESPACE lttablespacenamegt
• STORAGE (INITIAL ltnngt NEXT ltnngt
• PCTINCREASE ltnngt
• MINEXTENTS ltnngt MAXEXTENTS ltnngt
• FREELISTS ltnngt FREELIST GROUPS ltnngt
• BUFFER_POOL KEEPRECYCLEDEFAULT)
• PCTFREE ltnngt PCTUSED ltnngt
• INITRANS ltnngt MAXTRANS ltnngt

INITIAL Size of the first extent NEXT Size of
next and all subsequent extents
PCTINCREASE Range of 0 to 100 Percent of
previous extent to increase each subsequent extent
10
Setting Block Space Usage

• Expanded STORAGE clause syntax
• TABLESPACE lttablespacenamegt
• STORAGE (INITIAL ltnngt NEXT ltnngt
• PCTINCREASE ltnngt
• MINEXTENTS ltnngt MAXEXTENTS ltnngt
• FREELISTS ltnngt FREELIST GROUPS ltnngt
• BUFFER_POOL KEEPRECYCLEDEFAULT)
• PCTFREE ltnngt PCTUSED ltnngt
• INITRANS ltnngt MAXTRANS ltnngt

MINEXTENTS Min. number of extents MAXEXTENTS
Max. number of extents
Primarily for Real Application Clusters FREELISTS
Number of freelists per extent FREELIST GROUPS
Number of freelist groups per extent
11
Setting Block Space Usage

• Expanded STORAGE clause syntax
• TABLESPACE lttablespacenamegt
• STORAGE (INITIAL ltnngt NEXT ltnngt
• PCTINCREASE ltnngt
• MINEXTENTS ltnngt MAXEXTENTS ltnngt
• FREELISTS ltnngt FREELIST GROUPS ltnngt
• BUFFER_POOL KEEPRECYCLEDEFAULT)
• PCTFREE ltnngt PCTUSED ltnngt
• INITRANS ltnngt MAXTRANS ltnngt

PCTFREE Minimum percent of data block reserved
for updates PCTUSED Percent of data block used
below which block is re-opened for updates
BUFFER POOL buffer area in which data blocks
from the table are stored in the SGA
12
Setting Block Space Usage

• Expanded STORAGE clause syntax
• TABLESPACE lttablespacenamegt
• STORAGE (INITIAL ltnngt NEXT ltnngt
• PCTINCREASE ltnngt
• MINEXTENTS ltnngt MAXEXTENTS ltnngt
• FREELISTS ltnngt FREELIST GROUPS ltnngt
• BUFFER_POOL KEEPRECYCLEDEFAULT)
• PCTFREE ltnngt PCTUSED ltnngt
• INITRANS ltnngt MAXTRANS ltnngt

Initial and maximum number of transactions that
can concurrently access a data block
13
Setting Block Space Usage
Data block components Arrows indicate direction
of growth
14
Setting Block Space Usage

• Overhead
• Grows when more rows are inserted
• Grows when INITRANS or MAXTRANS are increased
• Never shrinks

15
Setting Block Space Usage

• Free space
• Cannot insert rows if free space exceeds PCTFREE
• Cannot update rows if no free space left
• Can resume inserting rows if used space falls
  below PCTUSED

16
Setting Block Space Usage

• Row data
• Grows when rows are inserted or updated
• Shrinks when rows are deleted or updated

17
Setting Block Space Usage

• When a single row spans two data blocks
• Chained row
• Migrated row

18
Storage Methods

• Options when setting storage for a table
• Use tablespace defaults
• Customize the STORAGE clause for each table
• Use Oracle defaults

19
Storage MethodsHow to Set Storage For Locally
Managed Tables

• Simplified syntax
• TABLESPACE lttablespacenamegt
• STORAGE (INITIAL ltnngt)
• Example
• CREATE TABLE CH07BICYCLE
• (BIKE_ID NUMBER(10) PRIMARY KEY,
• BIKE_MAKER VARCHAR2(50) NOT NULL,
• STYLE VARCHAR2(15))
• TABLESPACE USERS
• STORAGE (INITIAL 25M)

20
Storage MethodsHow to Set Storage For
Dictionary-Managed Tables

• Example (customize storage for table)
• CREATE TABLE TRUCK_MAINTENANCE
• (TRUCK_ID NUMBER(10), REPAIR_DATE DATE,
• PROBLEM_DESCRIPTION VARCHAR2(2000),
• DIAGNOSIS VARCHAR2(2000),
• BILLING_DATE DATE, BILLING_AMT NUMBER (10,2))
• TABLESPACE USER_DTAB
• STORAGE (INITIAL 80M NEXT 40M PCT INCREASE 0
  
• MINEXTENTS 2 MAXEXTENTS 25)
• PCTFREE 25 PCTUSED 50 MINTRANS 1 MAXTRANS 2

21
Storage MethodsHow to Set Storage For
Dictionary-Managed Tables

• Example (use tablespace defaults)
• CREATE TABLE BIKE_MAINTENANCE
• (BIKE_ID NUMBER(10),
• REPAIR_DATE DATE,
• DESCRIPTION VARCHAR2(30))

22
Row Structure and the ROWID

• Row
• components
• Row header
• Column Data

23
Row Structure and the ROWID

• Physical rowid
• Used for most tables
• Never changes once the row is created
• Subtypes
• Extended Default
• Restricted Old format
• Logical rowid
• Identifies row by primary key value
• Used for index-organized tables

24
Row Structure and the ROWID
Internal format of each rowid type
25
Creating Tables

• Expanded syntax
• CREATE TABLE ltschemagt.lttablenamegt
• (ltcolumn_namegt ltdatatypegt ltsizegt NULLNOT NULL
• DEFAULT ltdefault_valuegt CHECK
  ltcheck_constraintgt,
• ... )
• ltconstraintsgt
• TABLESPACE lttablespacenamegt
• STORAGE (INITIAL ltnngt NEXT ltnngt PCTINCREASE ltnngt
• MINEXTENTS ltnngt MAXEXTENTS ltnngt
• FREELISTS ltnngt FREELIST GROUPS ltnngt
• BUFFER_POOL KEEPRECYCLEDEFAULT)
• PCTFREE ltnngt PCTUSED ltnngt INITRANS ltnngt MAXTRANS
  ltnngt

26
Creating Tables Columns and Data Types

• Datatypes
• Character types
• CHAR, VARCHAR, VARCHAR2
• NCHAR, NVARCHAR, NVARCHAR2
• LONG
• Number type NUMBER

27
Creating Relational Tables

• Factors in relational table design
• Name of the table
• Name and data type of all columns
• Estimated initial size and growth pattern
• Location of the table
• Constraints, relationships to other tables,
  default data

28
Creating Relational Tables

• Example
• CREATE TABLE EMPLOYEE
• (EMPLOYEE_ID NUMBER (10),
• JOB_TITLE VARCHAR2(45),
• first_name varchar2(40),
• Last_name varchar2(40),
• phone_number Varchar2(20))

• Storage settings used
• TABLESPACE
• User's default tablespace
• SYSTEM, if no user default
• Each STORAGE setting
• The tablespace's default storage setting
• Oracle default, if no tablespace default setting