Review Questions | Oracle9i DBA JumpStart

1.	What GUI tool analyzes a SQL statement and identifies the steps used to process the query?
2.	The two general categories of indexes are indexes and indexes.
3.	Which type of index is best for columns with a low cardinality?
4.	Which dynamic performance view can assist the DBA in sizing the buffer cache appropriately?
5.	Which type of table divides the contents of a very large table into more manageable chunks, both improving the manageability of the table for the DBA and potentially increasing the performance of queries on the table?
6.	Which data dictionary views contain information about table indexes and the table columns indexed?
7.	Name the six steps in Oracle’s Tuning Methodology in order of priority.
8.	Which feature associated with materialized views rewrites a query to use the materialized view instead of using the tables that are the source for the materialized view?
9.	What is the name of the pseudo-column that exists for every row of every table in the database and is unique across the entire database?
10.	Name the two different optimizer modes and identify which one uses statistics from tables and indexes to derive an execution plan.

Answers

1.	The Explain Plan GUI tool analyzes a SQL statement and identifies the steps used to process the query.
2.	B-tree, bitmap
3.	A bitmap index is best for columns with a low cardinality.
4.	The dynamic performance view V$DB_CACHE_ADVICE can assist the DBA in sizing the buffer cache appropriately.
5.	A partitioned table divides the contents of a very large table into more manageable chunks.
6.	The data dictionary views DBA_INDEXES and DBA_IND_COLUMNS contain information about table indexes and the table columns indexed.
7.	The six steps in Oracle’s Tuning Methodology are data design, application design, memory allocation, I/O and physical structures, resource contention, and underlying platform.
8.	The QUERY REWRITE feature rewrites a query to use the materialized view instead of using the tables that are the source for the materialized view.
9.	The pseudo-column ROWID exists for every row of every table in the database and is unique across the entire database.
10.	The two different optimizer modes are rule-based and cost-based. The cost-based method uses statistics from tables and indexes to derive an execution plan.