nerdexam
Oracle

1Z0-060 · Question #108

A warehouse fact table in your Oracle 12c Database is range-partitioned by month and accessed frequently with queries that span multiple partitions The table has a local prefixed, range partitioned…

The correct answer is A. Using a partial local Index on the warehouse fact table month column with indexing disabled to C. Using a partitioned view that does a UNION ALL query on the partitions of the warehouse fact table, E. Using a partial global index on the warehouse fact table month column with indexing disabling for. Oracle 12c partial indexes allow selective per-partition indexing so the optimizer can use index access for sparse partitions and full scans for dense ones. Partitioned views with UNION ALL achieve the same adaptive access without changing the index structure.

New Features for Performance

Question

A warehouse fact table in your Oracle 12c Database is range-partitioned by month and accessed frequently with queries that span multiple partitions The table has a local prefixed, range partitioned index. Some of these queries access very few rows in some partitions and all the rows in other partitions, but these queries still perform a full scan for all accessed partitions. This commonly occurs when the range of dates begins at the end of a month or ends close to the start of a month. You want an execution plan to be generated that uses indexed access when only a few rows are accessed from a segment, while still allowing full scans for segments where many rows are returned. Which three methods could transparently help to achieve this result?

Options

  • AUsing a partial local Index on the warehouse fact table month column with indexing disabled to
  • BUsing a partial local Index on the warehouse fact table month column with indexing disabled for
  • CUsing a partitioned view that does a UNION ALL query on the partitions of the warehouse fact table,
  • DConverting the partitioned table to a partitioned view that does a UNION ALL query on the monthly
  • EUsing a partial global index on the warehouse fact table month column with indexing disabling for
  • FUsing a partial global index on the warehouse fact table month column with indexing disabled for

How the community answered

(26 responses)
  • A
    69% (18)
  • B
    8% (2)
  • D
    19% (5)
  • F
    4% (1)

Why each option

Oracle 12c partial indexes allow selective per-partition indexing so the optimizer can use index access for sparse partitions and full scans for dense ones. Partitioned views with UNION ALL achieve the same adaptive access without changing the index structure.

AUsing a partial local Index on the warehouse fact table month column with indexing disabled toCorrect

A partial local index with INDEXING OFF on the high-density full-month partitions causes Oracle to skip index maintenance and index scan for those segments while retaining index access for the low-density boundary partitions. This is fully transparent to applications because the optimizer automatically chooses the correct access method per partition based on the INDEXING attribute.

BUsing a partial local Index on the warehouse fact table month column with indexing disabled for

This choice disables indexing for the boundary partial-month partitions rather than the full-month ones, which is the opposite of what is needed - index access would be suppressed exactly where few rows are returned, forcing unwanted full scans on the sparse segments.

CUsing a partitioned view that does a UNION ALL query on the partitions of the warehouse fact table,Correct

A partitioned view that unions individual monthly tables allows the Cost-Based Optimizer to evaluate each component table independently, selecting index range scans for tables with few matching rows and full table scans for dense tables. No application changes are needed because the view presents the same interface as the original table.

DConverting the partitioned table to a partitioned view that does a UNION ALL query on the monthly

Converting the existing partitioned table to a UNION ALL view is a destructive DDL operation that changes the underlying storage model and is not transparent - dependent objects, privileges, and application SQL referencing the original table name must all be revised.

EUsing a partial global index on the warehouse fact table month column with indexing disabling forCorrect

A partial global index with INDEXING OFF on the full-month partitions similarly lets Oracle apply index lookups against INDEXING ON partitions while doing full scans against INDEXING OFF partitions. Global partial indexes extend this benefit across partition boundaries for queries spanning months.

FUsing a partial global index on the warehouse fact table month column with indexing disabled for

This choice applies INDEXING disabled to the wrong set of partitions compared to E, leaving the dense full-month partitions indexed and the sparse boundary partitions without an index, which prevents the optimizer from choosing the faster indexed access path for low-row segments.

Concept tested: Oracle 12c partial indexes and partitioned view optimization

Source: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/index-partitioning.html

Topics

#partial index#partition pruning#local prefixed index#hybrid scan

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 1Z0-060 Practice