||Oracle Concepts by Burleson
Study Multiple Choice Questions
The following questions are purely for
you to measure your level of understanding. They are not graded.
After reading each of the following questions, choose the most
appropriate answer. After you have answered the question, click
on the button to display the hidden answer.
What was a problem with navigational data access languages?
user had to have knowledge of the table and index structures.
Navigational data access was far slower than declarative access.
Navigational access languages required the coder to embed their
queries inside a procedural language shell.
Navigational languages were far slower then SQL
Answer is A: A navigational data
language requires knowledge of the internal tables and index
structures. Prior to SQL, the person writing the query would
have to specify the table join order and the access path to the data.
2. Which is a major problem with
a) SQL cannot support
b) The same query can be written
in many ways, each with vastly different execution plans.
c) SQL syntax is too difficult
for non-computer professionals to use
d) SQL creates excessive locks
within the Oracle database
Answer B: the declarative nature of
SQL makes it possible to write an individual query in many different
forms, each with identical results. For example, a query he can
be written with a standard join, a non-correlated sub-query or a
correlated sub-query, each producing identical results but with widely
varying internal execution plans.
3. Which of the following is an
important feature of relational databases and SQL?
a) Independence of table
b) High speed of SQL
c) Powerful GUI front-end
d) Easy to install and use
Answer A: Prior to the invention of
relational databases, it was very difficult to establish relationships
with other database objects. Pre-relational database management
systems relied on pointers in order to establish data relationships.
With the advent of relational database management, it became possible
to establish ad-hoc relationships using SQL joins, even if the
databases resided in geographically dispersed areas.
4. Which of the following is an
important consideration when tuning an SQL
The number of CPUs on the server
The degree of parallelism on the tables
The use of bitmap indexes
The quality of the SQL optimization
Answer D: One of the hallmarks of SQL
is the fact that the writer is no longer concerned with specifying the
physical access methods to extract the data. For example, and
SQL coder need not be concerned with the proper worker in which to
join the tables together and the most appropriate indexes to use when
servicing their query. In reality, complex SQL code often failed
to generate optimum execution plans, and the database professional
must investigate and improve the execution plans in order to make the
SQL queries run faster.
of the following database design features is most important to SQL
Removal of data redundancy
The introduction of data redundancy
The introduction of non-first normal form relations
The introduction of SQL*Plus
Answer B: The degree of normalization
in the database design is critical to SQL performance. With disk
prices falling dramatically every year, the savvy database
administrator will deliberately introduce redundancy into their data
model in order to avoid expensive SQL table joints. The ideal
candidates for the introduction of redundancy are small data items
that are updated very infrequently. It is generally not
appropriate to redundantly introduce large data items, or data items
that are updated on a frequent basis.
Why is searching for
large-table full-table scans critical to SQL tuning?
a) They indicate an optimized
b) They may be able to be tuned
to use an index
The full-table scan should be normalized from the database design
A full-table scan is always sub-optimal.
Answer B: Whenever the SQL optimizer
gets confused its fallback position is to simply read every single row
within the table. This full-scan results in unnecessary disk I/O
and slow performance. It is an important duty of the Oracle
database administrator to identify unnecessary large table full table
scans, and alter the execution plans for the SQL statements to force
them in order to use the appropriate indexes.
What best describes the
relationship between indexes and SQL performance?
Indexes are only used in special cases
Indexes are used to make table storage more efficient
Indexes rarely make a difference in SQL performance
Indexes exist solely to improve query speed.
Answer D: The sole purpose of indexes
in any relational database management system is to reduce the access
time for SQL statements. The SQL optimizer can detect when and
indexes present, and use sophisticated algorithms in order to
determine the fastest way of accessing the data. If indexes did
not exist in a relational database the Oracle database SQL optimizer
would have no choice except to read every single row in the table.
For more details,
see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA