Oracle 12c Upgrade: 20 – Partitioning Enhancements

Card Set Information

Author:
Tralala
ID:
297939
Filename:
Oracle 12c Upgrade: 20 – Partitioning Enhancements
Updated:
2015-03-09 03:53:52
Tags:
Partitioning Enhancements
Folders:
Oracle 12c Upgrade
Description:
Oracle 12c Upgrade: 20 – Partitioning Enhancements
Show Answers:

Home > Flashcards > Print Preview

The flashcards below were created by user Tralala on FreezingBlue Flashcards. What would you like to do?


  1. What happens when moving partitions?
    They are relocated to a new tablespace. This tablespace could be on lower cost storage
  2. What is new in 12c regarding moving partitions?
    • They can now be moved online
    • ALTER TABLE MOVE PARTITION….ONLINE
  3. When moving a partition online, how are indexes maintained?
    Using the UPDATE INDEXES clause
  4. When moving a partition online, with UPDATE INDEXES which indexes are maintained?
    Local and Global
  5. When moving a partition online, how can space be saved?
    Using the COMPRESS command
  6. When moving a partition online, what types of compression are available?
    • COMPRESS - Basic can also be declared as COMPRESS BASIC
    • COMPRESS FOR OLTP - Advance compression
    • COMPRESS FOR QUERY HIGH|LOW - Default is HIGH
    • COMPRESS FOR ARCHIVE HIGH||LOW - Default is LOW
  7. What is Interval Reference Partitioning?
    The ability to use interval-partitioned tables as the parent tables for reference partitioning
  8. What does the Cascade option allow in TRUNCATE PARTITION and EXCHANGE PARTITION?
    In Reference Partitioning, when a record is deleted from the parent partition, the corresponding child record will also be deleted.
  9. To allow the Cascade option for function in Reference Partitioning, what must be defined?
    ON DELETE CASCADE on the referential constraint
  10. What are Multipartition Maintenance Operations.
    • In 12c the ability to maintain more than 1 partition or sub partition in a single command.
    • ADD
    • TRUNCATE
    • DROP
    • MERGE (this obv was as two partition operation < 12c)
  11. What command would be run to add 2 new range partitions to a table?
    • alter table sales add
    • partition sales_q1_2013 values less than
    • to_date('01-APR-2012', 'dd-MON-yyyy'))',
    • partition sales_q2_2013 values less than
    • (to_date('01-JUN-2012', 'dd-MON-yyyy')));
    • What command would be run to truncate 3 partitions?
    • alter table sales truncate partitions
    • PART_20140301, PART_20140301, PART_20140301;
  12. What happens to indexes when partitions are truncated;
    • Global Indexes must have UPDATE INDEXES to remain valid
    • Local Index are also truncated
  13. How are multiple partitions merged?
    • alter table sales merge partitions
    • PART1, PART2, PART3, PART4
    • INTO PARTITION PART1234;
  14. When multiple partitions are merged, what is specific to range partitioning?
    • The partitions must be adjacent and the in ascending order in the merge partitions
    • statement.
  15. How are multiple partitions dropped?
    alter table sales drop partitions PART1, PART2, PART3, PART4;
  16. What happens to indexes when partitions are dropped;
    • Global Indexes must have UPDATE INDEXES to remain valid
    • Local Index are also dropped
  17. What can be achieved when splitting into multiple partitions?
    • Can split a range or list partition into two or more partitions.
    • Can split a range or list subpartition into two or more subpartitions.
  18. What is the rule when splitting a range or list partition into multiple partitions?
    • Must specify N-1 values or the partitioning key column within the range of the partition
    • ie The last partition will NOT have range specified and act as the bucket for default and MAXVALUE
  19. What command would be split a list partitioned table?
    • ALTER TABLE PAINT
    • SPLIT PARTITION COLOUR INTO
    • PARTITION REDS ('CRIMSON', 'SCARLET')
    • PARTITION BLUES ('ROYAL', 'SKY')
    • PARTITION OTHERS;
  20. What command would be split a range partitioned table?
    • ALTER TABLE SALES
    • SPLIT PARTITION 2013 INTO
    • PARTITION 2013Q1 values less than….
    • PARTITION 2013Q2 values less than….
  21. PARTITION 2013Q3 values less than….
    PARTITION 2013Q4;
  22. What is Asynchronous Global Index Maintenance?
    • A global index isn’t rendered UNUSABLE by the DROP/TRUNCATE operations
    • The index entries remain, they are just ignored, they are cleaned up later by an Oracle Scheduler job
  23. When is Asynchronous Global Index Maintenance not supported?
    • Tables with object types
    • Tables with domain indexes
    • Tables owned by SYS
  24. What process is run to cleanup global indexes following Asynchronous Global Index Maintenance and when does it run by default?
    • SYS.PMO_DEFERRED_GIDX_MAINT_JOB
    • 2am
  25. What does SYS.PMO_DEFERRED_GIDX_MAINT_JOB run?
    DBMS_PART.CLEANUP_GIDX procedure
  26. How can the DBA find global indexes with stale entries as a result of Asynchronous Global Index Maintenance job?
    • DBA_INDEXES or DBA_IND_PARTITIONS new column ORPHANED_ENTRIES
    • YES - contains orphaned entries
    • NO - contains no orphaned entries
    • N/A - Index is not a type to contain ORPHANED_ENTRIES
  27. What does the DBMS_PART Package do?
    A new package that contains the CLEANUP_GIDX procedure
  28. Apart from DBMS_PART.CLEANUP_GIDX procedure how else can stale entries in a global index?
    • Using COALESCE CLEANUP clause of ALTER INDEX REBUILD
    • ALTER INDEX REBUILD [PARTITION] COALESCE CLEANUP
  29. What is a Partial Index?
    A global or local index created on a subset of table partitions
  30. Where is Partial indexing is not supported?
    Unique indexes or indexes used to enforce unique constraints
  31. What are default table indexing properties for a partitioned table?
    • INDEXING FULL (default)
    • INDEXING PARTIAL
  32. In Partial indexing, when is a Partial Index used?
    When INDEXING is ON
  33. How is the default Indexing Property for a table specified?
    • When the table is CREATED
    • INDEXING ON|OFF
    • NO ALTER CAPABILITY!!
    • INDEXING ON is default
  34. How is the default indexing policy defined at table level?
    • CREATE TABLE
    • …..
    • INDEXING ON|OFF
  35. How is the default indexing policy defined at partition level?
    • (PARTITION…..
    • …..
    • INDEXING ON|OFF)
  36. If a table has partitions with a mix of INDEXING ON and INDEXING OFF, how would a global index be created if global indexing partial was used?
    It would be a partial global index, only on those partitions with INDEXING ON
  37. If a table has partition with a mix of INDEXING ON and INDEXING OFF how would a local index be created if local indexing partial was used?
    Only on those partitions with INDEXING ON
  38. At what level can partial global index be created?
    Partition and Subpartition
  39. If the default policy for a table is INDEXING OFF and the partial index INDEXING FULL | PARTIAL clause is not specified how would the index be created?
    • A normal, INDEXING FULL is default and implicit.
    • The create index clause overrides the table INDEXING ON|OFF.
  40. How can the dba tell if a global index is a partial global index?
    • From INDEXING column of DAU_INDEXES
    • FULL or PARTIAL
  41. How can the dba tell if a local index is a local global index?
    • From DEF_INDEXING column of DAU_IND_PARTITIONS
    • Status USABLE if partitioned, UNUSABLE if not partitioned
  42. How can dba lookup the default partial indexing policy partition for a partitioned table?
    • DEF_INDEXING from DAU_PART_TABLES
    • ON or OFF

What would you like to do?

Home > Flashcards > Print Preview