Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions

by Kyte, Thomas

• ISBN13: 9781590595305
• ISBN10: 1590595300
• Edition: CD
• Format: Paperback
• Copyright: 2005-09-19
• Publisher: Springer-Verlag New York Inc

Summary

Expert Oracle: 9i and 10g Programming Techniques and Solutions is the definitive book if you're a developer or DBA who works with Oracle-driven database applications. This fully revised edition covers both the 9i and 10g versions. It also comes with a CD containing a searchable PDF of the 8i version of the book. Every important feature and function of the database is covered and, as a result, you will be able to build fast, effective, scalable, and secure Oracle applications.

Table of Contents

Foreword	xi
About the Author	xiv
About the Technical Reviewers	xv
Acknowledgments	xvi
Introduction	xvii
Setting Up Your Environment	xxv
Developing Successful Oracle Applications	1	(48)
My Approach	2	(2)
The Black Box Approach	4	(5)
How (and How Not) to Develop Database Applications	9	(38)
Understanding Oracle Architecture	9	(6)
Understanding Concurrency Control	15	(5)
Multi-Versioning	20	(6)
Database Independence?	26	(16)
``How Do I Make It Run Faster?''	42	(4)
The DBA--Developer Relationship	46	(1)
Summary	47	(2)
Architecture Overview	49	(16)
Defining Database and Instance	50	(5)
The SGA and Background Processes	55	(2)
Connecting to Oracle	57	(5)
Dedicated Server	57	(2)
Shared Server	59	(1)
Mechanics of Connecting over TCP/IP	60	(2)
Summary	62	(3)
Files	65	(50)
Parameter Files	66	(12)
What Are Parameters?	67	(2)
Legacy init.ora Parameter Files	69	(2)
Server Parameter Files (SPFILEs)	71	(7)
Parameter File Wrap-Up	78	(1)
Trace Files	78	(7)
Requested Trace Files	79	(4)
Trace Files Generated in Response to Internal Errors	83	(2)
Trace File Wrap-Up	85	(1)
Alert File	85	(3)
Data Files	88	(8)
A Brief Review of File System Mechanisms	89	(1)
The Storage Hierarchy in an Oracle Database	90	(4)
Dictionary-Managed and Locally-Managed Tablespaces	94	(2)
Temp Files	96	(2)
Control Files	98	(1)
Redo Log Files	98	(5)
Online Redo Log	99	(2)
Archived Redo Log	101	(2)
Password Files	103	(3)
Change Tracking File	106	(1)
Flashback Log Files	107	(1)
Flashback Database	107	(1)
Flash Recovery Area	108	(1)
DMP Files (EXP/IMP Files)	108	(2)
Data Pump Files	110	(3)
Flat Files	113	(1)
Summary	114	(1)
Memory Structures	115	(40)
The Process Global Area and User Global Area	115	(20)
Manual PGA Memory Management	116	(7)
Automatic PGA Memory Management	123	(10)
Choosing Between Manual and Auto Memory Management	133	(2)
PGA and UGA Wrap-Up	135	(1)
The System Global Area	135	(19)
Fixed SGA	139	(1)
Redo Buffer	140	(1)
Block Buffer Cache	141	(7)
Shared Pool	148	(2)
Large Pool	150	(1)
Java Pool	151	(1)
Streams Pool	152	(1)
Automatic SGA Memory Management	152	(2)
Summary	154	(1)
Oracle Processes	155	(28)
Server Processes	156	(14)
Dedicated Server Connections	156	(2)
Shared Server Connections	158	(1)
Connections vs. Sessions	159	(6)
Dedicated Server vs. Shared Server	165	(4)
Dedicated/Shared Server Wrap-Up	169	(1)
Background Processes	170	(11)
Focused Background Processes	171	(7)
Utility Background Processes	178	(3)
Slave Processes	181	(1)
I/O Slaves	181	(1)
Parallel Query Slaves	182	(1)
Summary	182	(1)
Locking and Latching	183	(48)
What Are Locks?	183	(3)
Locking Issues	186	(23)
Lost Updates	186	(1)
Pessimistic Locking	187	(2)
Optimistic Locking	189	(11)
Optimistic or Pessimistic Locking?	200	(1)
Blocking	200	(3)
Deadlocks	203	(5)
Lock Escalation	208	(1)
Lock Types	209	(21)
DML Locks	209	(8)
DDL Locks	217	(3)
Latches	220	(9)
Manual Locking and User-Defined Locks	229	(1)
Summary	230	(1)
Concurrency and Multi-Versioning	231	(24)
What Are Concurrency Controls?	231	(1)
Transaction Isolation Levels	232	(10)
Read Uncommitted	234	(1)
Read Committed	235	(2)
Repeatable Read	237	(2)
Serializable	239	(2)
Read Only	241	(1)
Implications of Multi-Version Read Consistency	242	(4)
A Common Data Warehousing Technique That Fails	242	(2)
An Explanation for Higher Than Expected I/O on Hot Tables	244	(2)
Write Consistency	246	(7)
Consistent Reads and Current Reads	247	(2)
Seeing a Restart	249	(3)
Why Is a Restart Important to Us?	252	(1)
Summary	253	(2)
Transactions	255	(28)
Transaction Control Statements	256	(1)
Atomicity	257	(5)
Statement-Level Atomicity	257	(2)
Procedure-Level Atomicity	259	(3)
Transaction-Level Atomicity	262	(1)
Integrity Constraints and Transactions	262	(3)
Immediate Constraints	262	(1)
Deferrable Constraints and Cascading Updates	263	(2)
Bad Transaction Habits	265	(8)
Committing in a Loop	266	(6)
Using Autocommit	272	(1)
Distributed Transactions	273	(2)
Autonomous Transactions	275	(6)
How Autonomous Transactions Work	275	(2)
When to Use Autonomous Transactions	277	(4)
Summary	281	(2)
Redo and Undo	283	(54)
What Is Redo?	283	(1)
What Is Undo?	284	(3)
How Redo and Undo Work Together	287	(4)
Example Insert-Update-Delete Scenario	287	(4)
Commit and Rollback Processing	291	(9)
What Does a Commit Do?	292	(6)
What Does a Rollback Do?	298	(2)
Investigating Redo	300	(23)
Measuring Redo	300	(2)
Redo Generation and Before/After Triggers	302	(6)
Can I Turn Off Redo Log Generation?	308	(5)
Why Can't I Allocate a New Log?	313	(1)
Block Cleanout	314	(3)
Log Contention	317	(2)
Temporary Tables and Redo/Undo	319	(4)
Investigating Undo	323	(13)
What Generates the Most and Least Undo?	323	(2)
ORA-01555: snapshot too old Error	325	(11)
Summary	336	(1)
Database Tables	337	(84)
Types of Tables	337	(2)
Terminology	339	(12)
Segment	339	(2)
Segment Space Management	341	(1)
High-Water Mark	342	(2)
Freelists	344	(3)
Pctfree and Pctused	347	(3)
Logging and Nologging	350	(1)
Initrans and Maxtrans	351	(1)
Heap Organized Tables	351	(3)
Index Organized Tables	354	(16)
Index Organized Tables Wrap-Up	369	(1)
Index Clustered Tables	370	(8)
Index Clustered Tables Wrap-Up	378	(1)
Hash Clustered Tables	378	(10)
Hash Clustered Tables Wrap-Up	387	(1)
Sorted Hash Clustered Tables	388	(2)
Nested Tables	390	(13)
Nested Tables Syntax	391	(8)
Nested Table Storage	399	(3)
Nested Tables Wrap-Up	402	(1)
Temporary Tables	403	(7)
Temporary Tables Wrap-Up	410	(1)
Object Tables	410	(8)
Object Tables Wrap-Up	418	(1)
Summary	418	(3)
Indexes	421	(68)
An Overview of Oracle Indexes	422	(1)
B*Tree Indexes	423	(25)
Index Key Compression	426	(3)
Reverse Key Indexes	429	(6)
Descending Indexes	435	(2)
When Should You Use a B*Tree Index?	437	(10)
B*Trees Wrap-Up	447	(1)
Bitmap Indexes	448	(7)
When Should You Use a Bitmap Index?	449	(4)
Bitmap Join Indexes	453	(2)
Bitmap Indexes Wrap-Up	455	(1)
Function-Based Indexes	455	(14)
Important Implementation Details	455	(1)
A Simple Function-Based Index Example	456	(8)
Indexing Only Some of the Rows	464	(2)
Implementing Selective Uniqueness	466	(1)
Caveat on Case	467	(1)
Caveat Regarding ORA-01743	468	(1)
Function-Based Indexes Wrap-Up	469	(1)
Application Domain Indexes	469	(2)
Frequently Asked Questions and Myths About Indexes	471	(17)
Do Indexes Work on Views?	471	(1)
Do Nulls and Indexes Work Together?	471	(3)
Should Foreign Keys Be Indexed?	474	(1)
Why Isn't My Index Getting Used?	475	(7)
Myth: Space Is Never Reused in an Index	482	(3)
Myth: Most Discriminating Elements Should Be First	485	(3)
Summary	488	(1)
Datatypes	489	(68)
An Overview of Oracle Datatypes	489	(3)
Character and Binary String Types	492	(10)
NLS Overview	492	(3)
Character Strings	495	(7)
Binary Strings: RAW Types	502	(2)
Number Types	504	(9)
Number Type Syntax and Usage	507	(3)
Binary_Float/Binary_Double Type Syntax and Usage	510	(1)
Non-Native Number Types	511	(1)
Performance Considerations	511	(2)
Long Types	513	(7)
Restrictions on Long and Long Raw Types	513	(2)
Coping with Legacy Long Types	515	(5)
Date, Timestamp, and Interval Types	520	(20)
Formats	521	(1)
Date Type	522	(7)
Timestamp Type	529	(8)
Interval Type	537	(3)
Lob Types	540	(15)
Internal Lobs	541	(12)
BFILEs	553	(2)
Rowid/Urowid Types	555	(1)
Summary	556	(1)
Partitioning	557	(58)
Partitioning Overview	558	(9)
Increased Availability	558	(2)
Reduced Administrative Burden	560	(5)
Enhanced Statement Performance	565	(2)
Table Partitioning Schemes	567	(15)
Range Partitioning	567	(3)
Hash Partitioning	570	(5)
List Partitioning	575	(2)
Composite Partitioning	577	(2)
Row Movement	579	(2)
Table Partitioning Schemes Wrap-Up	581	(1)
Partitioning Indexes	582	(24)
Local Indexes vs. Global Indexes	583	(1)
Local Indexes	584	(6)
Global Indexes	590	(16)
Partitioning and Performance, Revisited	606	(6)
Auditing and Segment Space Compression	612	(2)
Summary	614	(1)
Parallel Execution	615	(34)
When to Use Parallel Execution	616	(2)
A Parallel Processing Analogy	617	(1)
Parallel Query	618	(6)
Parallel DML	624	(3)
Parallel DDL	627	(12)
Parallel DDL and Data Loading Using External Tables	628	(2)
Parallel DDL and Extent Trimming	630	(9)
Parallel Recovery	639	(1)
Procedural Parallelism	639	(9)
Parallel Pipelined Functions	640	(3)
Do-It-Yourself Parallelism	643	(5)
Summary	648	(1)
Data Loading and Unloading	649	(56)
SQL*Loader	649	(31)
Loading Data with SQLLDR FAQs	653	(26)
SQLLDR Caveats	679	(1)
SQLLDR Summary	680	(1)
External Tables	680	(12)
Setting Up External Tables	681	(6)
Dealing with Errors	687	(3)
Using an External Table to Load Different Files	690	(1)
Multiuser Issues	691	(1)
External Tables Summary	692	(1)
Flat File Unload	692	(9)
Data Pump Unload	701	(2)
Summary	703	(2)
Index	705