Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  Oracle Concepts by Burleson Consulting

Self 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. 

1.       What was a problem with navigational data access languages? 

a)      The user had to have knowledge of the table and index structures.

b)      Navigational data access was far slower than declarative access.

c)      Navigational access languages required the coder to embed their queries inside a procedural language shell.

d)      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 SQL?

a)     SQL cannot support object-orientation

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 relationships

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                   statement?

a)       The number of CPUs on the server

b)       The degree of parallelism on the tables

c)       The use of bitmap indexes

d)       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.

5.           Which of the following database design features is most important to SQL performance? 

a)       Removal of data redundancy

b)       The introduction of data redundancy

c)       The introduction of non-first normal form relations

d)       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.

6.            Why is searching for large-table full-table scans critical to SQL tuning?

 a)      They indicate an optimized execution plan.

 b)      They may be able to be tuned to use an index

 c)       The full-table scan should be normalized from the database design

 d)       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.

7.            What best describes the relationship between indexes and SQL performance?

a)       Indexes are only used in special cases

b)       Indexes are used to make table storage more efficient

c)       Indexes rarely make a difference in SQL performance

d)       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:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 

 

email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.