1. You are advised to index only the columns with good selectivity. How do you determine a columns
selectivity?

a . subtract the number of rows in the table from the number of distinct indexed values.

b . multiply the number of rows in the
table by the number of distinct indexed values.

c . divide the number of rows in the table by the number of distinct indexed values.

d . add
the number of rows in the table to the number of distinct indexed values.

e . None of the choices.


Ans: divide the number of rows in the
table by the number of distinct indexed values.

2. You are the Database Administrator for your company. You are managing an Oracle 8i database
solution. You always want to tune up the solution. You want to create indexes. Which of the following is the correct strategy to use?

a . Build
indexes in advance

b . Build indexes only when required

c . Build indexes on all the frequently accessed columns

d . Build indexes on
all the columns that have keys

e . None of the choices.


Ans: Build indexes only when required


3. You want to create indexes. Which
aspect of index usages may potentially lead to drawbacks in your database administrative works?

a . Index creation

b . Index maintenance


c . Index upgrade

d . Index selection

e . None of the choices.


Ans: Index maintenance

4. You want to drop the indexes that are
not used. How do you detect the indexes that are not referenced in any execution plan?

a . By processing all of the application SQL through
EXPLAIN PLAN and capturing the resulting plans.

b . By processing all of the application SQL through SQL TRACE.

c . By processing all of the
application SQL through ORACLE EXPERT and capturing the resulting plans.

d . By processing all of the application SQL through Performance
Console of OEM.

e . None of the choices.


Ans: By processing all of the application SQL through EXPLAIN PLAN and capturing the resulting
plans.



5. You are the Database Administrator for your company. You are managing an Oracle 8i database solution. You always want to tune up
the solution. As a general guideline according to Oracle, you should create indexes on tables when:

a . They are often queried for less than 12%
or 6% of the tables rows.

b . They are often queried for less than 8% or 4% of the tables rows.

c . They are often queried for less than 2%
or 4% of the tables rows.

d . They are often queried for less than 12% or 24% of the tables rows.

e . None of the choices.


Ans: They
are often queried for less than 2% or 4% of the tables rows.


6. You are using distributed query. Some of the remote objects are partitioned
for performance optimization. Why may cost-based optimization be less than optimal?

a . Cost-based optimization can only recognize some of the
remote objects that are partitioned.

b . Cost-based optimization cannot recognize that a remote object is partitioned.

c . Cost-based
optimization may produce resource conflicts.

d . Cost-based optimization may produce dead locks.

e . None of the choices.


Ans: Cost-
based optimization cannot recognize that a remote object is partitioned.



7. Your solution involves distributed queries. What type of
optimization should you use?

a . Index-based

b . Cost-based

c . Loop-based

d . Rule-based

e . Structure-based

f . None of
the choices.


Ans: Cost-based


8. You want to use hints. In a distributed query, what hints are supported for local tables?

a . only
join order hints

b . only join operation hints

c . only access methods

d . only parallel hints

e . ALL hints

f . None of the
choices.


Ans: ALL hints

9. You choose to omit the ORDERED hint from a SQL statement performing a join. What will the optimizer do?

a .
It will choose the use of the ORDERED hint.

b . It will choose the use of the UNORDERED hint.

c . It will produce an error.

d . It will
produce a warning.

e . It will choose the order in which to join the tables.

f . None of the choices.


Ans: It will choose the order in
which to join the tables.


10. You want to use hint to force Oracle to join tables in the order in which they appear in the FROM clause. What
hint should you use?

a . ORDERED

b . JOIN

c . OUTJOIN

d . RIGHTJOIN

e . LEFTJOIN

f . None of the choices.


Ans: ORDERED

---