1 / 15

Optimizing Database Performance with Indexes and Sequences - A Complete Guide

Learn about refining the server model by adding new tables, indexes, sequences, and more for improved performance and efficiency. Understand the importance of key columns, efficient querying, and managing surrogate keys effectively.

lmcduffie
Download Presentation

Optimizing Database Performance with Indexes and Sequences - A Complete Guide

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Refining the Server Model

  2. Adding New Tables New Table New View New Snapshot New Cluster New Column New Man FK New Opt FK

  3. Add a Foreign Key

  4. Create Index for FK DDT Automatically Creates Index for FK

  5. Other Columns to Index • Key columns • Columns used frequently in queries • Columns with many distinct values • Guidelines: • Start with the least number of indexes • Add indexes as the data grows • Check both query and DML perfomance

  6. NAME JOB HIREDATE Choosing the Number of Indexes • More indexes = slower insert and delete • Concatenated indexes can help Queries: NAME = 'SMITH'; JOB LIKE 'SALES%' AND NAME = 'SMITH'; JOB = 'CLERK'AND NAME = 'SMITH'AND HIREDATE > '1-JUN-98'; HIREDATE = '31-DEC-98'; Indexes: NAME JOB HIREDATE

  7. 3 2 1 Oracle Sequence • Database object: • Generates unique numbers • Minimizes contention • Gaps in sequence

  8. Adding Surrogate Key Create a Sequence Create Key Assign to Column

  9. DDT Surrogate Keys • Automatically creates key • Automatically creates sequence • Automatically assigns sequence to key column

  10. Oracle Sequences • Efficient • Have Gaps • Rolled back transactions do not return used sequence numbers

  11. CC_DOMAIN CC_NEXT VALUE CC_INCREMENT CC_DOMAIN CC_NEXT VALUE CC_INCREMENT 1 1 1 1 EMP_SEQ1 EMP_SEQ1 EMP_SEQ1 EMP_SEQ1 2018 2017 Eliminating the Gaps CG_CODE_CONTROLS EMPLOYEES IDENTIFIER LAST_NAME 2016 SMITH Newrow 2017 HAMBURG

  12. Creating a Code Control Sequence Sequence Definitions ? Create Sequence: Name 1 2 3 Name of the sequence Purpose of the sequence Sequence type ORACLE sequence Code control sequence

  13. Sequence Within Parent • Generate a value within the context of the parent record RENTAL_ITEMS RENTALS RE_ID LINE_NO RE_ID 1 1 1 2 2 1 2 1 2 2

  14. Column Properties Derivation Autogen Type … Seq in Parent Creating a Sequence Within a Parent Set AutoGen Type to Seq in Parent

  15. Choosing a Method • Oracle sequence: • Sequential values with gaps • Recommended • Code control sequence: • Databases other than Oracle • Sequential values without gaps • Contention can be tolerated • Sequence within a parent: • Minimal number of detail records

More Related