Databases Reference
In-Depth Information
Chapter 12
1.
What GUI tool analyzes a SQL statement and identifies the steps used to process the query?
Answer:
The Explain Plan 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.
Answer:
B-tree, bitmap
3.
Which type of index is best for columns with a low cardinality?
Answer:
A bitmap index is best for columns with a low cardinality.
4.
Which dynamic performance view can assist the DBA in sizing the buffer cache appropriately?
Answer:
The dynamic performance view V$DB_CACHE_ADVICE 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 improv-
ing the manageability of the table for the DBA and potentially increasing the performance of queries on
the table?
Answer:
A partitioned table divides the contents of a very large table into more manageable chunks.
6.
Which data dictionary views contain information about table indexes and the table columns indexed?
Answer:
The data dictionary views DBA_INDEXES and DBA_IND_COLUMNS contain information about table
indexes and the table columns indexed.
7.
Name the six steps in Oracle's Tuning Methodology in order of priority.
Answer:
The six steps in Oracle's Tuning Methodology are data design, application design, memory allo-
cation, I/O and physical structures, resource contention, and underlying platform.
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?
Answer:
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.
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?
Answer:
The pseudo-column ROWID exists for every row of every table in the database and is unique across
the entire database.
10.
Name the two different optimizer modes in Oracle9
i
and identify which one uses statistics from tables and
indexes to derive an execution plan; identify two of the most common modes in Oracle 10
g
.
Answer:
The two different optimizer modes for Oracle9
i
are rule-based and cost-based. The cost-based
method uses statistics from tables and indexes to derive an execution plan. For Oracle 10
g
, the two most
common optimizer modes are ALL_ROWS and FIRST_ROWS.
