Burleson D.K. Physical database design using Oracle
Навигация

Выставка новых поступлений  |  Поступления иностранных книг в библиотеки СО РАН : 2003 | 2006 |2008
 
Книжные оглавления
Burleson D.K. Physical database design using Oracle / Burleson D.K. - Boca Raton: CRC, 2004. - 247 p. - (Foundations of Database Design; vol.1). - ISBN 0-8493-1817-3.
 
ОглавлениеСноска

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


Вверх Burleson D.K. Physical database design using Oracle / Burleson D.K. - Boca Raton: CRC, 2004. - 247 p. - (Foundations of Database Design; vol.1). - ISBN 0-8493-1817-3.

Сигла: 050По алфавиту | По темам ||| Информатика | Вычислительная техника | Математика
[О библиотеке | Академгородок | Новости | Выставки | Ресурсы | Библиография | Партнеры | ИнфоЛоция | Поиск | English]
  Пожелания и письма: branch@gpntbsib.ru
© 1997-2020 Отделение ГПНТБ СО РАН (Новосибирск)
Статистика доступов: архив | текущая статистика
 

Документ изменен: Wed Feb 27 14:51:58 2019. Размер: 19,930 bytes.
Посещение N 3329 с 24.09.2007