1 Introduction to Oracle Physical Design ............................... 1 Preface .............................................................. 1 Relational Databases and Physical Design ............................. 1 Systems Development and Physical Design .............................. 2 Systems Analysis and Physical Database Design ........................ 4 The Structured Specification ..................................... 4 The Role of Functional Decomposition in Physical Database Design ..... 6 Introduction to Logical Database Design .............................. 7 Unnormalized Form ................................................ 9 Nested Tables ....................................................... 10 First Normal Form ............................................... 11 Second Normal Form .............................................. 12 Third Normal Form ............................................... 13 E/R Modeling ........................................................ 13 Bridging between Logical and Physical Models ........................ 15 Activities of Oracle Physical Design ............................ 17 Physical Design Requirements Validation ............................. 18 How to Identify a Poor Requirements Evaluation .................. 19 Functional Validation ........................................... 19 How to Spot a Poor Functional Analysis .......................... 20 Evaluating the Worth of an Existing System ...................... 20 Locating Oracle Physical Design Flaws ............................... 23 2 Physical Entity Design for Oracle ................................... 25 Introduction ........................................................ 25 Data Relationships and Physical Design .............................. 25 Redundancy and Physical Design .................................. 26 The Dangers of Overnormalization ................................ 28 Denormalizing One-to-Many Data Relationships .................... 29 Denormalizing Many-to-Many Data Relationships ................... 32 Recursive Data Relationships .................................... 34 Massive Denormalization: Star Schema Design ......................... 37 Object-Oriented Database Design ..................................... 38 Abstract Data Types ............................................. 39 Designing Class Hierarchies ......................................... 41 Representing Class Hierarchies .................................. 45 Materialized Views and Denormalization .............................. 45 Automatic SQL Query Rewrite ..................................... 48 When Is SQL Query Rewrite Used? ................................. 49 Referential Integrity ............................................... 49 Conclusion .......................................................... 53 3 Oracle Hardware Design .............................................. 55 Introduction ........................................................ 55 Planning the Server Environment ..................................... 57 Design for Oracle Server CPU .................................... 57 Designing Task Load Balancing Mechanisms .................. 58 Design for Oracle Server RAM .................................... 59 Making Oracle Memory Nonswappable ......................... 60 Design for the Oracle Server Swap Disk .......................... 60 Designing the Network Infrastructure for Oracle ..................... 62 Oracle Network Design ............................................... 63 The tcp.nodelay parameter .................................... 63 The automatic_ipc parameter ................................... 64 The break_poll_skip parameter ................................. 64 The disable_oob parameter ..................................... 65 The SDU and TDU parameters .................................... 65 The queuesize Parameter in listener.ora .................... 67 Connection Pooling and Network Performance ...................... 67 ODBC and Network Performance .................................... 68 Oracle Replication Design ....................................... 69 Oracle Disk Design .................................................. 70 Conclusion .......................................................... 70 4 Oracle Instance Design .............................................. 73 Introduction ........................................................ 73 Reserving RAM for Database Connections .......................... 74 RAM Used by Oracle Connections .............................. 75 Determining the Optimal PGA Size ................................ 76 A Script for Computing Total PGA RAM ............................ 77 SGA Parameter Components ............................................ 79 Designing the Shared Pool ........................................... 80 Library Cache Usage Measurement ................................. 81 Oracle Event Waits .................................................. 82 The Shared Pool Advisory Utility .................................... 82 Designing the Data Buffers .......................................... 87 Using v$db_cache_advice ......................................... 91 Design with the DBHR ................................................ 92 Using Statspack for the DBHR .................................... 94 Data Buffer Monitoring with Statspack ....................... 94 Pinning Packages in the SGA ......................................... 97 Automatic Repinning of Packages ................................. 99 Designing Logon Triggers to Track User Activity .................... 101 Designing a User Audit Table ................................... 101 User Table Normalization ....................................... 102 Designing a Logon Trigger ...................................... 102 Designing the Logoff Trigger ................................... 104 User Activity Reports .......................................... 106 User Logon Detail Reports ...................................... 107 Designing Oracle Failover Options .................................. 109 Conclusion ......................................................... 110 5 Oracle Tablespace Design ........................................... 111 Introduction ....................................................... 111 Sizing Oracle Data Blocks .......................................... 112 The Evolution of Oracle File Structures ............................ 113 Design for Oracle Segment Storage .................................. 114 Setting PCTFREE and PCTUSED .................................... 116 Free List Management for Oracle Objects ........................ 117 Design to Control Oracle Row Chaining and Row Migration ........ 118 The Issue of PCTFREE ........................................... 120 The Issue of PCTUSED ........................................... 122 The Sparse Table Problem ....................................... 123 Automatic Segment Space Management ................................. 124 Internal Freelist Management with ASSM ......................... 127 Potential Performance Issues with ASSM ......................... 130 Replication Design ................................................. 132 Conclusion ......................................................... 132 6 Oracle Table Design ................................................ 135 Introduction ....................................................... 135 Table Replication Design ........................................... 135 Is the Transfer of Data Time Sensitive? ........................ 136 Is the Number of Tables Manageable? ............................ 136 Do All Your Replicated Tables Need to Be Updatable? ............ 136 Does Your Database Change Constantly? .......................... 136 Is the Number of Transactions Manageable? ...................... 137 Are You Replicating between Different Versions of Oracle or Different OSs? .............................................. 137 Do Both Sites Require the Ability to Update the Same Tables? ... 137 Does the Replicated Site Require the Ability to Replicate to Another Site? ............................................... 137 Oracle External Tables ............................................. 138 Defining an External Table ..................................... 138 Internals of External Tables ................................... 143 Security for External Table Files .............................. 145 Limitations of Comma-Delimited Spreadsheet Files ............... 145 Design with Materialized Views ..................................... 147 Materialized Views and Snapshots ........................... 148 Prerequisites for Using Materialized Views ..................... 148 Invoking SQL Query Rewrite ................................. 149 Refreshing Materialized Views .............................. 149 Manual Complete Refresh .................................... 150 Manual Fast (Incremental) Refresh .......................... 150 Automatic Fast Refresh of Materialized Views ............... 150 Creating a Materialized View ................................... 151 Tips for Using Materialized Views .......................... 151 Design for Partitioned Structures .................................. 153 Oracle Object Structures ........................................... 154 Data Model Extension Capabilities .............................. 155 Object Orientation and Oracle .................................. 156 Oracle Nested Tables ........................................... 157 Performance of Oracle Object Extensions ........................ 158 Design with ADTs ............................................... 159 Nesting ADTs ................................................... 162 Design with Oracle OIDs ........................................ 163 Navigating with Pointers (OIDs) ................................ 166 Design with VARRAY Tables .......................................... 166 Advantages of Repeating Groups ................................. 168 Disadvantages of Repeating Groups .............................. 169 Determining When to Use Repeating Groups ....................... 170 Repeating Groups and ADTs ...................................... 170 Repeating Groups of OIDs ....................................... 171 Repeating Groups of Data Values ................................ 172 Pointing to Tables ......................................... 174 Using Nested Tables ................................................ 179 Understanding Multidimensional Pointers and Oracle ......... 182 Reviewing the Implications of OIDs for Oracle Design ....... 184 Designing Aggregate Objects ................................ 185 Designing with Oracle Methods ...................................... 187 Automatic Method Generation ................................ 192 Stored Procedures and Oracle Tables ........................ 194 Conclusion ......................................................... 196 7 Oracle Index Design ................................................ 197 Introduction ....................................................... 197 Index Design Basics ................................................ 198 The Oracle B-Tree Index ........................................ 198 Bitmapped Indexes .............................................. 199 Function-Based Indexes ......................................... 200 Index-Organized Tables ......................................... 201 Evaluating Oracle Index Access Methods ............................. 202 Index Range Scan ............................................... 202 Fast Full-Index Scan ........................................... 203 Designing High-Speed Index Access .................................. 208 Speed Factors .................................................. 208 Parallel Option ............................................ 208 Nologging Option ........................................... 209 Space and Structure Factors .................................... 209 Compress Option ............................................ 209 Tablespace Block Size Option ............................... 210 Designing Indexes to Reduce Disk I/O ........................... 211 Oracle Optimizer and Index Design .................................. 213 Physical Row-Ordering and Index Design ............................. 215 Constraints and Index Design ................................... 216 Using Multicolumn Indexes .......................................... 218 How Oracle Chooses Indexes ......................................... 219 Index Design for Star Schemas ...................................... 221 Indexing Alternatives to B-Tree Indexes ............................ 223 Bitmap Indexes ................................................. 223 Function-Based Indexes ......................................... 223 Reverse-Key Indexes and SQL Performance ........................ 224 Index Usage for Queries with IN Conditions ..................... 224 Design for Oracle Full-Index Scans ................................. 226 Oracle and Multiblock Reads .................................... 221 Basics of FBIs ..................................................... 228 Indexing on a Column with NULL Values .............................. 229 Invoking the Full-Index Scan with a FBI ............................ 230 An Important Oracle Enhancement ................................ 231 How to Use Oracle9i Bitmap Join Indexes ............................ 231 How Bitmap Join Indexes Work ....................................... 231 Bitmap Join Indexes in Action .................................. 233 Exclusions for Bitmap Join Indexes ............................. 234 Design for Automatic Histogram Creation ............................ 235 The method_opt='SKEWONLY' dbms_stats Option .................... 235 Conclusion ......................................................... 236 Index ................................................................ 237