OCM 11g Preparation - Administer partitioned tables and indexes using appropriate methods and keys

Partitioning is the key for performance when you are talking about DW or any object with a lot of rows.

What you must study and practice in this topic is how to create a table with:

  • Basic Partitioning Strategies: RANGE, LIST and HASH.
  • Advanced Partitioning Strategies: REFERENCE, INTERVAL, SYSTEM and VIRTUAL COLUMN-BASED.
  • Composite Partitions (all the combinations)
  • Multicolumns Partitions (This is not composite!)
  • Creating Partitioned Index-Organized Tables

And indexes:

  • Local Partitioned Indexes.
    • Local indexes can only be unique if the partitioning key is a part of the index key.
    • A bitmap index on a partitioned table must be a local index.
  • Global Partitioned Indexes.
    • Note here that global indexes can only be partitioned, not subpartitioned.
    • Their partition type are only RANGE or HASH.
  • Global Nonpartitioned Indexes (normal indexes, you already know them)
  • Prefixed and Nonprefixed Indexes (Performance Implications also)

Path to Documentation:

Database SQL Language Reference -> CREATE TABLE / CREATE INDEX
VLDB and Partitioning Guide -> 4 Partition Administration -> Creating Partitions - (Good for copy paste examples during the exam)
VLDB and Partitioning Guide -> 2 Partitioning Concepts
VLDB and Partitioning Guide -> 3 Partitioning for Availability, Manageability, and Performance -> Index Partitioning


Click here to go back to the Main OCM 11g Preparation page.

Have you enjoyed? Please leave a comment or give a 👍!

Leave a Reply

Your email address will not be published.