10-05-2009, Physical Design and PerformanceOverviewDB Design PhasesIntroduction - Inputs to Physical DesignPhysical Design DecisionsDesigning FieldsSelection of a Primary KeyExample of Data DictionarySlide 9Composite usage mapSlide 11Slide 12INDEXESRules for Using IndexesSlide 15Different Type of IndexesIndexes (Defaults)B-Tree (Balanced Tree)B-Trees (continued)Good Indexing (B-Tree) CandidatesBitmap IndexBitmap Index (cont.)Hash IndexingHash Index work best withIndex-Organized TablesReverse Key IndexesConclusions on IndexesDenormalizationRules for Adding Derived ColumnsRules for Storing Repeating GroupsRules for Storing Repeating Groups Across ColumnsSlide 32Slide 33PartitioningSlide 35Slide 36Intro. To Query ProcessingGoals3 alternativesComparing costsPhases of Query ProcessingDynamic versus Static OptimizationQuery Optimizer - PlanOracle operations (results of autotrace)Slide 45Slide 46Slide 47Slide 48Slide 49Tuning SQL and PL/SQL QueriesROWIDROWID (cont.)SELECT STATEMENTQUERY OPTIMIZEREnd of LectureCS 8630 Database Administration, Dr. Guimaraes10-05-2009, Physical Design and PerformanceClassWill Start Momentarily…CS8630 Database AdministrationDr. Mario GuimaraesCS 8630 Database Administration, Dr. GuimaraesOverview•Introduction: input to Physical Design, Decisions•Create Index•Rewrite SQL / Query Optimizer (Leccotech)•Denormalization, Materialized Views•Partition Database•Redundant Arrays of Inexpensive Disks (RAID)•Redefine Main memory structures (SGA in Oracle)•Change default Block Size at installation •Export/Import (drop indexes): defragment•Check Locks•Separate data by category in proper tablespaces•Redefining Client-Server ArchitectureWhere should a DBA start when trying to optimize ? Why ?a) DB, b) OS, c) DB Application, 4) OtherCS 8630 Database Administration, Dr. GuimaraesDB Design Phases•Conceptual Design•Logical Design•Physical DesignCS 8630 Database Administration, Dr. GuimaraesIntroduction - Inputs to Physical Design•Normalized relations.•Volume estimates.•Attribute definitions.•Data usage: entered, retrieved, deleted, updated.•Response time requirements.•Requirements for security, backup, recovery, retention, integrity.•DBMS characteristics.•systemCS 8630 Database Administration, Dr. GuimaraesPhysical Design Decisions•Specifying attribute data types.•Modifying the logical design.•Specifying the file organization (sometimes)•Choosing indexes.CS 8630 Database Administration, Dr. GuimaraesDesigning Fields•Choosing PK•Choosing data type.•Coding, compression, encryption.•Controlling data integrity.–Default value.–Range control.–Null value control.–Referential integrity.CS 8630 Database Administration, Dr. GuimaraesSelection of a Primary Key•Consider a shorter field or selecting another candidate key to substitute for a long, multi-field primary key (and all associated foreign keys.)–System-generated non-information-carrying key–Versus–Primary key like Phone numberCS 8630 Database Administration, Dr. GuimaraesExample of Data DictionaryAttribute Table Null? Unique? Pkey? Fkey? Ref tableDomainCID College N Y Y N NA4 digit integer greater than 1000Office College Y N N N NAcharacter string length 10DID Dept N Y Y N NA4 digit integer greater than 1000Location Dept Y N N N NAcharacter string length 65CID Dept Y N N Y College4 digit integer greater than 1000CS 8630 Database Administration, Dr. GuimaraesExample code-look-up tableCS 8630 Database Administration, Dr. GuimaraesComposite usage mapCS 8630 Database Administration, Dr. GuimaraesDesigning Fields•Handling missing data.–Substitute an estimate of the missing value.–Assign default value.–Trigger a report listing missing values.–In programs, ignore missing data unless the value is significant.CS 8630 Database Administration, Dr. Guimaraes•END OF INTRODUCTION TO PHYSICAL DESIGN•START OF PERFORMANCE (INDEXES, QUERY OPTIMIZATION).CS 8630 Database Administration, Dr. GuimaraesINDEXES•What is an INDEX ?•Why do we CREATE an INDEX ?A) To speed up query B) To speed up data entry (insert/update/delete) ?C) Both ?CS 8630 Database Administration, Dr. GuimaraesRules for Using Indexes1. Use on larger tables.2. Index the primary key of each table.3. Index search fields.4. Fields in WHERE clause of SQL commands.5. Cardinality is high. For example, not on SEX, where cardinality is 2.Typically: When there are >100 different values but not when there are <10 values.CS 8630 Database Administration, Dr. GuimaraesRules for Using Indexes6. DBMS may have limit on number of indexes per table and number of bytes per indexed field(s).7. Null values may not be referenced from an index.8. Use indexes heavily for non-volatile databases (Datawarehouse); limit the use of indexes for volatile databases.CS 8630 Database Administration, Dr. GuimaraesDifferent Type of IndexesTypical Indexes•B-Trees (traditional) Indexes•Hash-cluster•Bitmap Indexes•Index-Organized Tables•Reverse-Key Indexes--------------------------------------•When we issue the command:Create index cidx on orders (cid);What type of an index do we create ?•General Format: Create index <iName> on <tname> (<col_name>);CS 8630 Database Administration, Dr. GuimaraesIndexes (Defaults)•Anytime a PK is created, an index is automatically created.•Anytime when the type of index is not specificied, the type of index created isa B-Trees.CS 8630 Database Administration, Dr. GuimaraesB-Tree (Balanced Tree)•Most popular type of index structure for any programming language or database.•When you don’t know what to do, the best option is usually a B-Tree. They are flexible and perform well (not very well) in several scenarios.•It is really the B+ tree or B* treeCS 8630 Database Administration, Dr. GuimaraesB-Trees (continued)•One node corresponds to one block/page(minimum disk I-O).•Non-Leaf nodes(n keys, n+1 pointers)•Leaf-Nodes (contain n entries, where each entry has an index and a pointer to a data block). Also, each node has a pointer to next node.•All leaves are at the same height.CS 8630 Database Administration, Dr. GuimaraesGood Indexing (B-Tree) Candidates•Table must be reasonably large•Field is queried by frequently•Field has a high cardinality (don’t index by sex, where the cardinality is 2!!).•Badly balanced trees may inhibit performance. Destroying and re-creating index may improve performance.CS 8630 Database Administration, Dr.