Advanced Tuning   «Prev  Next»
Lesson 1

Introduction to Tuning with Oracle Data Structures and Advanced Tuning Concepts

This module deals with the new data type structures and explains their performance implications for Oracle databases. Prior to the introduction of Oracle, critics were comparing the added functionality of the new object-oriented databases with the traditional relational database model.
With Oracle we see several new object-oriented data structures that promise faster performance. In this module, we will take a closer look at these new features. The next lesson reviews the new Oracle data structures.
Oracle includes numerous data structures to improve the speed of Oracle SQL queries. Taking advantage of the low cost of disk storage, Oracle includes indexing algorithms that dramatically increase the speed with which Oracle queries are serviced. This module explores the internals of Oracle indexing and reviews the standard
  1. b-tree index,
  2. bitmap indexes,
  3. function-based indexes, and
  4. index-only tables (IOTs); and
demonstrates how these indexes may dramatically increase the speed of Oracle SQL queries.
Oracle uses indexes to avoid the need for large-table, full-table scans and disk sorts, which are required when the SQL optimizer cannot find an efficient way to service the SQL query.

Module Objectives

By the time you finish this module, you will be able to:
  1. List the functionality of each of the Oracle data structures
  2. Create an Abstract Data Type (ADT)
  3. Create a nested table
  4. Define a table with a VARRAY

Every organization has information that it must store and manage to meet its requirements. For example, a corporation must collect and maintain human resources records for its employees. This information must be available to those who need it. An information system is a formal system for storing and processing information.
An information system could be a set of cardboard boxes containing manila folders along with rules for how to store and retrieve the folders. However, most companies today use a database to automate their information systems. A database is an organized collection of information treated as a unit. The purpose of a database is to collect, store, and retrieve related information for use by database applications.

Database Management System (DBMS)

A database management system (DBMS) is software that controls the storage, organization, and retrieval of data. Typically, a DBMS has the following elements:
  1. Kernel code: This code manages memory and storage for the DBMS.
  2. Repository of metadata: This repository is usually called a data dictionary.
  3. Query language: This language enables applications to access the data.
A database application is a software program that interacts with a database to access and manipulate data. The first generation of database management systems included the following types:
  1. Hierarchical: A hierarchical database organizes data in a tree structure. Each parent record has one or more child records, similar to the structure of a file system.
  2. Network: A network database is similar to a hierarchical database, except records have a many-to-many rather than a one-to-many relationship. The preceding database management systems stored data in rigid, predetermined relationships. Because no data definition language existed, changing the structure of the data was difficult. Also, these systems lacked a simple query language, which hindered application development.