An application issues many expensive join aggregations type queries. Examine the Exhibit to view the queries. Which two could help improve the performance of these SQL statements without changing…

Question

Exhibit

Options

• ACreate B*-Tree indexes on the join columns.
• BCreate a materialized view with query rewrite enabled for the first statement and nested MVIEWs for
• CCollect histogram statistics on columns for which aggregating functions are performed.
• DCreate an STS for these queries and use SQL Access Advisor, which may generate advice about MVIEWs.
• ECreate an STS for these queries and use SQL Performance Analyzer, which may generate advice about

• A
  4% (1)
• B
  70% (16)
• C
  17% (4)
• E
  9% (2)

Explanation

B: Materialized views and indexes are essential when tuning a database to achieve optimum performance for complex, data-intensive queries. * STS ?SQL tuning set. * A SQL Tuning Set is a database object that includes one or more SQL statements and their execution statistics and execution context. You can use the set as an input source for various advisors, such as SQL Tuning Advisor, SQL Access Advisor, and SQL Performance Analyzer. * SQL Access Advisor: Materialized views and indexes are essential when tuning a database to achieve optimum performance for complex, data-intensive queries. The SQL Access Advisor helps you achieve your performance goals by recommending the proper set of materialized views, materialized view logs, and indexes for a given workload. Understanding and using these structures is essential when optimizing SQL as they can result in significant performance improvements in data retrieval. The advantages, however, do not come without a cost. Creation and maintenance of these objects can be time consuming, and space requirements can be significant. The SQL Access Advisor recommends bitmap, function-based, and B-tree indexes. A bitmap index offers a reduced response time for many types of ad hoc queries and reduced storage requirements compared to other indexing techniques. B-tree indexes are most commonly used in a data warehouse to index unique or near-unique keys. * Conventional wisdom holds that bitmap indexes are most appropriate for columns having low distinct values--such as GENDER, MARITAL_STATUS, and RELATION. This assumption is not completely accurate, however. In reality, a bitmap index is always advisable for systems in which data is not frequently updated by many concurrent systems. A bitmap index on a column with 100- percent unique values (a column candidate for primary key) is as efficient as a B-tree index. * By default, the Oracle creates a b_tree index. In a b-tree, you walk the branches until you get to the node that has the data you want to use. In the classic b-tree structure, there are branches from the top that lead to leaf nodes that contain the data. not E: SQL Performance Analyzer enables you to assess the performance impact of any system change resulting in changes to SQL execution plans and performance characteristics. Examples of common system changes for which you can use SQL Performance Analyzer include: Database upgrade Configuration changes to the operating system, hardware, or database Database initialization parameter changes Schema changes, for example, adding new indexes or materialized views Gathering optimizer Validating SQL tuning actions, for example, creating SQL profiles or implementing partitioning

No community discussion yet for this question.