What recommendations does the SQL Access Advisor provide for optimizing SQL queries? (Choose all that apply.)
A.
selection of SQL plan baselines
B.
partitioning of tables and indexes
C.
creation of index-organized tables
D.
creation of bitmap, function-based, and B-tree indexes
E.
optimization of materialized views for maximum query usage and fast refresh
Please explain this
To achieve optimum performance for data-intensive queries, materialized views and indexes are essential when tuning SQL statements
A materialized view provides access to table data by storing query results in a separate schema object. Unlike an ordinary view, which does not take up storage space or contain data, a materialized view contains the rows resulting from a query against one or more base tables or views. A materialized view log is a schema object that records changes to a master table’s data, so that a materialized view defined on the master table can be refreshed incrementally. SQL Access Advisor recommends how to optimize materialized views so that they can be rapidly refreshed and take advantage of the general query rewrite feature. For more information about materialized views and view logs, see Oracle Database Concepts.
SQL Access Advisor also recommends bitmap, function-based, and B-tree indexes. A bitmap index provides a reduced response time for many types of ad hoc queries and reduced storage requirements compared to other indexing techniques. A function-based index derives the indexed value from the table data. For example, to find character data in mixed cases, a function-based index can be used to look for the values as if they were all in uppercase characters. B-tree indexes are most commonly used to index unique or near-unique keys.