Tài liệu Managing Tables pptx

LINK DOWNLOAD MIỄN PHÍ TÀI LIỆU "Tài liệu Managing Tables pptx": http://123doc.vn/document/1043609-tai-lieu-managing-tables-pptx.htm

11-10
Copyright © Oracle Corporation, 2002. All rights reserved.
ROWID Format
•
Extended ROWID Format
•
Restricted ROWID Format
OOOOOO BBBBBBFFF RRR
Data object
number
Relative file
number
Row numberBlock number
BBBBBBBB FFFFRRRR
Block number Row number File number
. .
11-12
Copyright © Oracle Corporation, 2002. All rights reserved.
Structure of a Row
Database block
Row header
Column length
Column value
11-13
Copyright © Oracle Corporation, 2002. All rights reserved.
Creating a Table
CREATE TABLE hr.employees(
employee_id NUMBER(6),
first_name VARCHAR2(20),
last_name VARCHAR2(25),
email VARCHAR2(25),
phone_number VARCHAR2(20),
hire_date DATE DEFAULT SYSDATE,
job_id VARCHAR2(10),
salary NUMBER(8,2),
commission_pct NUMBER (2,2),
manager_id NUMBER(6),
department_id NUMBER(4))
TABLESPACE USERS;
11-17
Copyright © Oracle Corporation, 2002. All rights reserved.
Creating a Table: Guidelines
•
Place tables in separate tablespaces.
•
Use locally-managed tablespaces to avoid
fragmentation.
•
Use few standard extent sizes for tables to reduce
tablespace fragmentation.
11-18
Copyright © Oracle Corporation, 2002. All rights reserved.
Creating Temporary Tables
•
Created using the GLOBAL TEMPORARY clause:
•
Tables retain data only for the duration of a
transaction or session.
•
DML locks are not acquired on the data.
•
You can create indexes, views, and triggers on
temporary tables.
CREATE GLOBAL TEMPORARY TABLE
hr.employees_temp
AS SELECT * FROM hr.employees;
11-19
Copyright © Oracle Corporation, 2002. All rights reserved.
(Average Row Size - Initial Row Size) * 100
Average Row Size
Average Row Size * 100
100
-
- PCTFREE
-
-
Available Data Space
Setting PCTFREE and PCTUSED
•
Compute PCTFREE
•
Compute PCTUSED
11-20
Copyright © Oracle Corporation, 2002. All rights reserved.
Before update After update
Pointer
Row Migration and Chaining
11-21
Copyright © Oracle Corporation, 2002. All rights reserved.
Changing Storage and Block
Utilization Parameters
ALTER TABLE hr.employees
PCTFREE 30
PCTUSED 50
STORAGE(NEXT 500K
MINEXTENTS 2
MAXEXTENTS 100);
11-24
Copyright © Oracle Corporation, 2002. All rights reserved.
Manually Allocating Extents
ALTER TABLE hr.employees
ALLOCATE EXTENT(SIZE 500K
DATAFILE ‘/DISK3/DATA01.DBF’);
11-25
Copyright © Oracle Corporation, 2002. All rights reserved.
Nonpartitioned Table Reorganization
•
When a nonpartitioned table is reorganized, its
structure is kept, but not its contents.
•
It is used to move a table to a different tablespace or
reorganize extents.
ALTER TABLE hr.employees
MOVE TABLESPACE data1;