Oracle Data Warehouse Tuning for 10g (eBook)

front cover 1
copyright 5
table of contents 6
Contents at a Glance 6
Contents 8
front matter 20
Preface 20
Introduction to Data Warehouse Tuning 24
The Origin and History of Data Warehouses 24
Separation of OLTP and Data Warehouse Databases 25
Tuning a Data Warehouse 26
What Is in this Book? 27
Part I. Data Warehouse Data Modeling 27
Part II. Specialized Data Warehouse SQL Code 29
Part III. Advanced Topics 30
Sample Databases in This Book 31
body 32
Part I: Data Warehouse Data Modeling 32
1 The Basics of Data Warehouse Data Modeling 34
1.1 The Relational and Object Data Models 34
1.1.1 The Relational Data Model 35
1.1.2 The Object Data Model 41
1.1.3 The Object-Relational Data Model 44
1.2 Data Modeling for Data Warehouses 44
1.2.1 The Container Shipment Tracking Schema 44
1.2.2 The Dimensional Data Model 46
1.2.3 Data Warehouse Data Model Design Basics 52
2 Introducing Data Warehouse Tuning 62
2.1 Let's Build a Data Warehouse 62
2.1.1 The Demographics Data Model 62
2.1.2 The Inventory-Accounting OLTP Data Model 63
2.1.3 The Data Warehouse Data Model 65
2.2 Methods for Tuning a Data Warehouse 68
2.2.1 Snowflake versus Star Schemas 68
2.2.2 3rd Normal Form Schemas 75
2.2.3 Introducing Other Data Warehouse Tuning Methods 75
2.3 Endnotes 78
3 Effective Data Warehouse Indexing 80
3.1 The Basics of Indexing 80
3.1.1 The When and What of Indexing 81
3.1.2 Types of Indexes in Oracle Database 85
3.2 Star Queries and Star Query Transformations 93
3.2.1 Star Queries 93
3.2.2 Star Transformation Queries 100
3.2.3 Problems with Star Queries and Star Transformations 104
3.3 Index Organized Tables and Clusters 106
3.4 Endnotes 108
4 Materialized Views and Query Rewrite 110
4.1 What Is a Materialized View? 110
4.1.1 The Benefits of Materialized Views 111
4.1.2 Potential Pitfalls of Materialized Views 112
4.2 Materialized View Syntax 113
4.2.1 CREATE MATERIALIZED VIEW 113
4.2.2 CREATE MATERIALIZED VIEW LOG 119
4.2.3 ALTER MATERIALIZED VIEW [LOG] 121
4.2.4 DROP MATERIALIZED VIEW [LOG] 121
4.3 Types of Materialized Views 122
4.3.1 Single Table Aggregations and Filtering Materialized Views 122
4.3.2 Join Materialized Views 125
4.3.3 Set Operator Materialized Views 129
4.3.4 Nested Materialized Views 129
4.3.5 Materialized View ORDER BY Clauses 133
4.4 Analyzing and Managing Materialized Views 133
4.4.1 Metadata Views 133
4.4.2 The DBMS_MVIEW Package 135
4.4.3 The DBMS_ADVISOR Package 139
4.5 Making Materialized Views Faster 140
4.6 Endnotes 143
5 Oracle Dimension Objects 144
5.1 What Is a Dimension Object? 144
5.2 Dimension Object Syntax 147
5.2.1 CREATE DIMENSION Syntax 148
5.2.2 ALTER and DROP DIMENSION Syntax 154
5.2.3 Using Constraints with Dimensions 154
5.3 Dimension Object Metadata 155
5.4 Dimension Objects and Performance 156
5.4.1 Rollup Using Dimension Objects 158
5.4.2 Join Back Using Dimension Objects 163
6 Partitioning and Basic Parallel Processing 168
6.1 What Are Partitioning and Parallel Processing? 168
6.1.1 What Is Partitioning? 168
6.1.2 The Benefits of Using Partitioning 169
6.1.3 Different Partitioning Methods 170
6.1.4 Parallel Processing and Partitioning 174
6.2 Partitioned Table Syntax 175
6.2.1 CREATE TABLE: Range Partition 175
6.2.2 CREATE TABLE: List Partition 177
6.2.3 CREATE TABLE: Hash Partition 178
6.2.4 Composite Partitioning 179
6.2.5 Partitioned Materialized Views 182
6.3 Tuning Queries with Partitioning 184
6.3.1 Partitioning EXPLAIN PLANs 184
6.3.2 Partitioning and Parallel Processing 185
6.3.3 Partition Pruning 185
6.3.4 Partition-Wise Joins 186
6.4 Other Partitioning Tricks 189
6.5 Partitioning Metadata 189
6.6 Endnotes 190
Part II: Tuning SQL Code in a Data Warehouse 192
7 The Basics of SQL Query Code Tuning 194
7.1 Basic Query Tuning 194
7.1.1 Columns in the SELECT Clause 195
7.1.2 Filtering with the WHERE Clause 195
7.1.3 Aggregating 200
7.1.4 Using Functions 201
7.1.5 Conditions and Operators 203
7.1.6 Pseudocolumns 207
7.1.7 Joins 210
7.2 How Oracle SQL Is Executed 215
7.2.1 The Parser 215
7.2.2 The Optimizer 216
7.3 Tools for Tuning Queries 222
7.3.1 What Is the Wait Event Interface? 223
7.3.2 Oracle Database Wait Event Interface Improvements 239
7.3.3 Oracle Enterprise Manager and the Wait Event Interface 240
7.4 Endnotes 244
8 Aggregation Using GROUP BY Clause Extensions 246
8.1 What Are GROUP BY Clause Extensions? 246
8.1.1 Why Use GROUP BY Clause Extensions? 246
8.2 GROUP BY Clause Extensions 247
8.2.1 The ROLLUP and CUBE Clauses 248
8.2.2 The GROUPING SETS Clause 256
8.2.3 Grouping Functions 263
8.3 GROUP BY Clause Extensions and Materialized Views 266
8.4 Combining Groupings Together 273
8.4.1 Composite Groupings 274
8.4.2 Concatenated Groupings 276
8.4.3 Hierarchical Cubes 277
9 Analysis Reporting 280
9.1 What Is Analysis Reporting? 280
9.1.1 How Does Analysis Reporting Affect Performance? 282
9.2 Types of Analysis Reporting 282
9.3 Introducing Analytical Functions 284
9.3.1 Simple Summary Functions 284
9.3.2 Statistical Function Calculators 284
9.3.3 Statistical Distribution Functions 285
9.3.4 Ranking Functions 286
9.3.5 Lag and Lead Functions 286
9.3.6 Aggregation Functions Allowing Analysis 287
9.4 Specialized Analytical Syntax 287
9.4.1 The OVER Clause 287
9.4.2 The WITH Clause 293
9.4.3 CASE and Cursor Expressions 297
9.5 Analysis in Practice 301
9.5.1 Rankings and Ratios 302
9.5.2 Lead and Lag Functionality 306
9.5.3 Histograms 306
9.5.4 Other Statistical Functionality 308
9.5.5 Data Densification 308
10 Modeling with the MODEL Clause 312
10.1 What Is the MODEL Clause? 312
10.1.1 The Parts of the MODEL Clause 312
10.1.2 How the MODEL Clause Works 314
10.1.3 Better Performance Using the MODEL Clause 317
10.2 MODEL Clause Syntax 319
10.2.1 Cell References 319
10.2.2 Return Rows 320
10.2.3 The Main Model 320
10.2.4 MODEL Clause Functions 322
10.3 What Can the MODEL Clause Do? 323
10.3.1 Materialized Views and the MODEL Clause 323
10.3.2 Referencing Cells 326
10.3.3 Referencing Multiple Models 332
10.3.4 UPDATE versus UPSERT 337
10.3.5 Loops 339
10.4 Performance and the MODEL Clause 339
10.4.1 Parallel Execution 339
10.4.2 Understanding MODEL Clause Query Plans 344
Part III: Advanced Topics 348
11 Query Rewrite 350
11.1 What Is Query Rewrite? 350
11.1.1 When Does the Optimizer Query Rewrite? 351
11.1.2 What Can the Optimizer Query Rewrite? 351
11.2 How the Optimizer Rewrites Queries 352
11.2.1 Matching Entire Query Strings 352
11.2.2 Matching Pieces of Queries 355
11.2.3 Special Cases for Query Rewrite 361
11.3 Affecting Query Rewrite Performance 362
11.4 Endnotes 364
12 Parallel Processing 366
12.1 What Is Parallel Processing? 366
12.1.1 What Can Be Executed in Parallel? 367
12.2 Degree of Parallelism (Syntax) 367
12.3 Configuration Parameters 368
12.4 Demonstrating Parallel Execution 370
12.4.1 Parallel Queries 370
12.4.2 Index DDL Statements 374
12.4.3 SELECT Statement Subqueries 375
12.4.4 DML Statements 376
12.4.5 Partitioning Operations 377
12.5 Performance Views 377
12.6 Parallel Execution Hints 379
12.7 Parallel Execution Query Plans 379
12.8 Endnotes 380
13 Data Loading 382
13.1 What Is Data Loading? 382
13.1.1 General Loading Strategies 383
13.2 Extraction 386
13.2.1 Logical Extraction 386
13.2.2 Physical Extraction 386
13.2.3 Extraction Options 387
13.3 Transportation Methods 392
13.3.1 Database Links and SQL 393
13.3.2 Transportable Tablespaces 394
13.4 Loading and Transformation 399
13.4.1 Basic Loading Procedures 400
13.4.2 Transformation Processing 414
13.5 Endnotes 415
14 Data Warehouse Architecture 416
14.1 What Is a Data Warehouse? 416
14.1.1 What Is Data Warehouse Architecture? 416
14.2 Tuning Hardware Resources for Data Warehousing 417
14.2.1 Tuning Memory Buffers 418
14.2.2 Tuning Block Sizes 419
14.2.3 Tuning Transactions 420
14.2.4 Tuning Oracle Net Services 421
14.2.5 Tuning I/O 424
14.3 Capacity Planning 440
14.3.1 Datafile Sizes 442
14.3.2 Datafile Content Sizes 443
14.3.3 The DBMS_SPACE Package 443
14.3.4 Statistics 445
14.3.5 Exact Column Data Lengths 450
14.4 OLAP and Data Mining 453
back matter 454
A New Data Warehouse Features in Oracle Database 10g 454
A.1 Endnotes 454
B Sample Schemas 456
Container Tracking Schemas 456
Demographics Schema 456
Inventory-Accounting Schema 456
Sample Schemas 456
C Sample Scripting 462
C.1 EXPLAINP.SQL 462
C.2 Create Tablespaces 463
C.3 GENERATE.SQL 465
C.3.1 CREATEUSER.SQL 465
C.3.2 SCHEMADIMSDW.SQL 465
C.3.3 SEQUENCESDIMSDW.SQL 467
C.3.4 DATADIMS.SQL 468
C.3.5 SCHEMAFACTSDW.SQL 471
C.3.6 DATAFACTSDW.SQL 473
C.3.7 SEQUENCESFACTSDW.SQL 474
C.4 Normalized Dimensions 474
D Syntax Conventions 478
E Sources of Information 480
index 482