Lane P.
Table of contents :
Send Us Your Comments……Page 17
Preface……Page 19
1 Data Warehousing Concepts……Page 29
Integrated……Page 30
Contrasting OLTP and Data Warehousing Environments……Page 31
Data Warehouse Architecture (Basic)……Page 33
Data Warehouse Architecture (with a Staging Area)……Page 34
Data Warehouse Architecture (with a Staging Area and Data Marts)……Page 35
2 Logical Design in Data Warehouses……Page 39
Creating a Logical Design……Page 40
Data Warehousing Schemas……Page 41
Other Schemas……Page 42
Fact Tables……Page 43
Dimension Tables……Page 44
Unique Identifiers……Page 45
Typical Example of Data Warehousing Objects and Their Relationships……Page 46
3 Physical Design in Data Warehouses……Page 49
Physical Design……Page 50
Tablespaces……Page 52
Integrity Constraints……Page 53
Dimensions……Page 54
4 Hardware and I/O Considerations in Data Warehouses……Page 55
Why Stripe the Data?……Page 56
Automatic Striping……Page 57
Local and Global Striping……Page 58
Analyzing Striping……Page 60
RAID Configurations……Page 63
Striping, Mirroring, and Media Recovery……Page 64
RAID 5……Page 65
The Importance of Specific Analysis……Page 66
5 Parallelism and Partitioning in Data Warehouses……Page 67
When to Implement Parallel Execution……Page 68
Block Range Granules……Page 69
Types of Partitioning……Page 70
Partition Pruning……Page 79
Partition-wise Joins……Page 81
6 Indexes……Page 93
Bitmap Indexes……Page 94
Bitmap Join Indexes……Page 98
Local Indexes Versus Global Indexes……Page 102
7 Integrity Constraints……Page 103
Why Integrity Constraints are Useful in a Data Warehouse……Page 104
Overview of Constraint States……Page 105
UNIQUE Constraints in a Data Warehouse……Page 106
FOREIGN KEY Constraints in a Data Warehouse……Page 107
RELY Constraints……Page 108
View Constraints……Page 109
8 Materialized Views……Page 111
Materialized Views for Data Warehouses……Page 112
The Need for Materialized Views……Page 113
Components of Summary Management……Page 115
Terminology……Page 117
Schema Design Guidelines for Materialized Views……Page 118
Materialized Views with Aggregates……Page 120
Materialized Views Containing Only Joins……Page 126
Nested Materialized Views……Page 128
Creating Materialized Views……Page 132
Storage Characteristics……Page 133
Enabling Query Rewrite……Page 134
Query Rewrite Restrictions……Page 135
Refresh Options……Page 136
Materialized View Logs……Page 140
Using Materialized Views with NLS Parameters……Page 141
Registering Existing Materialized Views……Page 142
Partition Change Tracking……Page 144
Partitioning a Materialized View……Page 148
Partitioning a Prebuilt Table……Page 149
Choosing Indexes for Materialized Views……Page 150
Security Issues with Materialized Views……Page 151
Dropping Materialized Views……Page 152
Using the DBMS_MVIEW.EXPLAIN_MVIEW Procedure……Page 153
MV_CAPABILITIES_TABLE.CAPABILITY_NAME Details……Page 156
MV_CAPABILITIES_TABLE Column Details……Page 158
Overview of Materialized View Management Tasks……Page 159
9 Dimensions……Page 163
What are Dimensions?……Page 164
Creating Dimensions……Page 166
Multiple Hierarchies……Page 169
Using Normalized Dimension Tables……Page 171
Using The DEMO_DIM Package……Page 172
Using Dimensions with Constraints……Page 173
Validating Dimensions……Page 174
Altering Dimensions……Page 175
Deleting Dimensions……Page 176
10 Overview of Extraction, Transformation, and Loading……Page 179
Overview of ETL……Page 180
ETL Tools……Page 181
Evolution of the Data Warehouse……Page 182
11 Extraction in Data Warehouses……Page 183
Understanding Extraction Methods in Data Warehouses……Page 184
Logical Extraction Methods……Page 185
Physical Extraction Methods……Page 186
Change Data Capture……Page 187
Extraction Using Data Files……Page 190
Extraction Via Distributed Operations……Page 193
12 Transportation in Data Warehouses……Page 195
Transportation Through Distributed Operations……Page 196
Transportation Using Transportable Tablespaces……Page 197
13 Loading and Transformation……Page 201
Transformation Flow……Page 202
Loading Mechanisms……Page 204
SQL*Loader……Page 205
External Tables……Page 206
Transformation Mechanisms……Page 208
Transformation Using SQL……Page 209
Transformation Using Table Functions……Page 216
Parallel Load Scenario……Page 226
Exception Handling Scenario……Page 234
Pivoting Scenarios……Page 235
14 Maintaining the Data Warehouse……Page 241
Using Partitioning to Improve Data Warehouse Refresh……Page 242
Implementing an Efficient Merge……Page 245
Maintaining Referential Integrity……Page 247
Purging Data……Page 248
Refreshing Materialized Views……Page 249
Complete Refresh……Page 250
Manual Refresh Using the DBMS_MVIEW Package……Page 251
Refresh Specific Materialized Views with REFRESH……Page 252
Refresh Dependent Materialized Views with REFRESH_DEPENDENT……Page 253
Monitoring a Refresh……Page 255
Tips for Refreshing Materialized Views with Aggregates……Page 256
Tips for Refreshing Materialized Views Without Aggregates……Page 259
Tips for Refreshing Nested Materialized Views……Page 260
Tips After Refreshing Materialized Views……Page 261
Fast Refresh with Partition Change Tracking……Page 262
Fast Refresh with CONSIDER FRESH……Page 266
15 Change Data Capture……Page 269
About Oracle Change Data Capture……Page 270
Publish and Subscribe Model……Page 271
Example of a Change Data Capture System……Page 272
Components and Terminology for Synchronous Change Data Capture……Page 273
Columns in a Change Table……Page 276
Views……Page 278
Publishing Change Data……Page 279
Steps Required to Subscribe to Change Data……Page 281
What Happens to Subscriptions When the Publisher Makes Changes……Page 284
Export and Import Considerations……Page 285
16 Summary Advisor……Page 287
Overview of the Summary Advisor in the DBMS_OLAP Package……Page 288
Using the Summary Advisor……Page 292
Identifier Numbers……Page 293
Workload Management……Page 294
Loading a User-Defined Workload……Page 295
Loading a Trace Workload……Page 297
Loading a SQL Cache Workload……Page 301
Validating a Workload……Page 303
Using Filters with the Summary Advisor……Page 304
Removing a Filter……Page 308
Recommending Materialized Views……Page 309
SQL Script Generation……Page 313
Summary Data Report……Page 315
When Recommendations are no Longer Required……Page 317
Sample Sessions……Page 318
ESTIMATE_MVIEW_SIZE Parameters……Page 323
Is a Materialized View Being Used?……Page 324
DBMS_OLAP.EVALUATE_MVIEW_STRATEGY Procedure……Page 325
17 Schema Modeling Techniques……Page 329
Star Schemas……Page 330
Tuning Star Queries……Page 332
Using Star Transformation……Page 333
18 SQL for Aggregation in Data Warehouses……Page 339
Overview of SQL for Aggregation in Data Warehouses……Page 340
Analyzing Across Multiple Dimensions……Page 341
Optimized Performance……Page 342
An Aggregate Scenario……Page 343
Interpreting NULLs in Examples……Page 344
ROLLUP Syntax……Page 345
Partial Rollup……Page 346
CUBE Syntax……Page 348
Partial CUBE……Page 350
GROUPING Function……Page 351
When to Use GROUPING……Page 354
GROUPING_ID Function……Page 355
GROUP_ID Function……Page 356
GROUPING SETS Expression……Page 357
Composite Columns……Page 359
Concatenated Groupings……Page 362
Concatenated Groupings and Hierarchical Data Cubes……Page 364
Hierarchy Handling in ROLLUP and CUBE……Page 366
HAVING Clause Used with GROUP BY Extensions……Page 367
Computation Using the WITH Clause……Page 368
19 SQL for Analysis in Data Warehouses……Page 371
Overview of SQL for Analysis in Data Warehouses……Page 372
RANK and DENSE_RANK……Page 375
Top N Ranking……Page 382
CUME_DIST……Page 383
PERCENT_RANK……Page 384
NTILE……Page 385
ROW_NUMBER……Page 386
Windowing Aggregate Functions……Page 387
Cumulative Aggregate Function……Page 388
Moving Aggregate Function……Page 389
Centered Aggregate Function……Page 390
Windowing Aggregate Functions with Logical Offsets……Page 391
Variable Sized Window……Page 392
Windowing Aggregate Functions with Physical Offsets……Page 393
Reporting Aggregate Functions……Page 394
Reporting Aggregate Example……Page 396
RATIO_TO_REPORT……Page 397
LAG/LEAD Syntax……Page 398
FIRST/LAST Syntax……Page 399
FIRST/LAST As Regular Aggregates……Page 400
FIRST/LAST As Reporting Aggregates……Page 401
REGR_COUNT……Page 402
Linear Regression Statistics Examples……Page 403
Sample Linear Regression Calculation……Page 404
Normal Aggregate Syntax……Page 405
Inverse Percentile Restrictions……Page 408
Hypothetical Rank and Distribution Syntax……Page 409
WIDTH_BUCKET Function……Page 410
WIDTH_BUCKET Syntax……Page 411
User-Defined Aggregate Functions……Page 413
CASE Expressions……Page 414
Creating Histograms with User-defined Buckets……Page 415
20 Advanced Analytic Services……Page 417
Benefits of OLAP and RDBMS Integration……Page 418
Data Mining……Page 420
Mining Within the Database Architecture……Page 421
Java API……Page 423
21 Using Parallel Execution……Page 425
When to Implement Parallel Execution……Page 426
The Parallel Execution Server Pool……Page 427
How Parallel Execution Servers Communicate……Page 429
Parallelizing SQL Statements……Page 430
Parallel Query……Page 435
Parallel DDL……Page 437
Parallel DML……Page 442
Parallel Execution of Functions……Page 452
Other Types of Parallelism……Page 453
Initializing and Tuning Parameters for Parallel Execution……Page 454
Using Automatically Derived Parameter Settings……Page 455
Setting the Degree of Parallelism……Page 456
How Oracle Determines the Degree of Parallelism for Operations……Page 458
Balancing the Workload……Page 461
Parallelization Rules for SQL Statements……Page 462
Degree of Parallelism and Adaptive Multiuser: How They Interact……Page 470
Forcing Parallel Execution for a Session……Page 471
Tuning General Parameters for Parallel Execution……Page 472
Parameters Establishing Resource Limits for Parallel Operations……Page 473
Parameters Affecting Resource Consumption……Page 482
Parameters Related to I/O……Page 489
Monitoring and Diagnosing Parallel Execution Performance……Page 491
Is There Regression?……Page 492
Is There a Serial Plan?……Page 493
Is The Workload Evenly Distributed?……Page 494
Monitoring Parallel Execution Performance with Dynamic Performance Views……Page 495
Monitoring Session Statistics……Page 498
Monitoring System Statistics……Page 500
Affinity and Parallel Operations……Page 501
Affinity and Parallel DML……Page 502
Miscellaneous Parallel Execution Tuning Tips……Page 503
Formula for Memory, Users, and Parallel Execution Server Processes……Page 504
Balancing the Formula……Page 506
Parallel Execution Space Management Issues……Page 507
Overriding the Default Degree of Parallelism……Page 508
Rewriting SQL Statements……Page 509
Creating and Populating Tables in Parallel……Page 510
Creating Temporary Tablespaces for Parallel Sort and Hash Join……Page 511
Executing Parallel SQL Statements……Page 512
Additional Considerations for Parallel DML……Page 513
Creating Indexes in Parallel……Page 517
Parallel DML Tips……Page 518
Incremental Data Loading in Parallel……Page 521
Using Hints with Cost-Based Optimization……Page 524
22 Query Rewrite……Page 525
Overview of Query Rewrite……Page 526
Cost-Based Rewrite……Page 527
When Does Oracle Rewrite a Query?……Page 528
Enabling Query Rewrite……Page 531
Controlling Query Rewrite……Page 532
Privileges for Enabling Query Rewrite……Page 533
Accuracy of Query Rewrite……Page 534
How Oracle Rewrites Queries……Page 535
Text Match Rewrite Methods……Page 536
General Query Rewrite Methods……Page 537
When are Constraints and Dimensions Needed?……Page 538
Query Rewrite Using Partially Stale Materialized Views……Page 569
Query Rewrite Using Nested Materialized Views……Page 572
Query Rewrite with CUBE, ROLLUP, and Grouping Sets……Page 574
Explain Plan……Page 579
DBMS_MVIEW.EXPLAIN_REWRITE Procedure……Page 580
Outer Joins……Page 585
Grouping Conditions……Page 586
Statistics……Page 587
A Glossary……Page 591
B Sample Data Warehousing Schema……Page 599
Index……Page 605
Reviews
There are no reviews yet.