Oracle9i Data Warehousing Guide

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