-
What happens when moving partitions?
They are relocated to a new tablespace. This tablespace could be on lower cost storage
-
What is new in 12c regarding moving partitions?
- They can now be moved online
- ALTER TABLE MOVE PARTITION….ONLINE
-
When moving a partition online, how are indexes maintained?
Using the UPDATE INDEXES clause
-
When moving a partition online, with UPDATE INDEXES which indexes are maintained?
Local and Global
-
When moving a partition online, how can space be saved?
Using the COMPRESS command
-
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
-
What is Interval Reference Partitioning?
The ability to use interval-partitioned tables as the parent tables for reference partitioning
-
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.
-
To allow the Cascade option for function in Reference Partitioning, what must be defined?
ON DELETE CASCADE on the referential constraint
-
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)
-
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;
-
What happens to indexes when partitions are truncated;
- Global Indexes must have UPDATE INDEXES to remain valid
- Local Index are also truncated
-
How are multiple partitions merged?
- alter table sales merge partitions
- PART1, PART2, PART3, PART4
- INTO PARTITION PART1234;
-
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.
-
How are multiple partitions dropped?
alter table sales drop partitions PART1, PART2, PART3, PART4;
-
What happens to indexes when partitions are dropped;
- Global Indexes must have UPDATE INDEXES to remain valid
- Local Index are also dropped
-
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.
-
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
-
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;
-
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….
-
PARTITION 2013Q3 values less than….
PARTITION 2013Q4;
-
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
-
When is Asynchronous Global Index Maintenance not supported?
- Tables with object types
- Tables with domain indexes
- Tables owned by SYS
-
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
-
What does SYS.PMO_DEFERRED_GIDX_MAINT_JOB run?
DBMS_PART.CLEANUP_GIDX procedure
-
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
-
What does the DBMS_PART Package do?
A new package that contains the CLEANUP_GIDX procedure
-
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
-
What is a Partial Index?
A global or local index created on a subset of table partitions
-
Where is Partial indexing is not supported?
Unique indexes or indexes used to enforce unique constraints
-
What are default table indexing properties for a partitioned table?
- INDEXING FULL (default)
- INDEXING PARTIAL
-
In Partial indexing, when is a Partial Index used?
When INDEXING is ON
-
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
-
How is the default indexing policy defined at table level?
- CREATE TABLE
- …..
- INDEXING ON|OFF
-
How is the default indexing policy defined at partition level?
- (PARTITION…..
- …..
- INDEXING ON|OFF)
-
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
-
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
-
At what level can partial global index be created?
Partition and Subpartition
-
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.
-
How can the dba tell if a global index is a partial global index?
- From INDEXING column of DAU_INDEXES
- FULL or PARTIAL
-
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
-
How can dba lookup the default partial indexing policy partition for a partitioned table?
- DEF_INDEXING from DAU_PART_TABLES
- ON or OFF
|
|