Oracle Scratchpad

Oracle Structures

Back to main Tutorial Page

Oracle Structures – Agenda

This course is new for 2013 and if you ever read Practical Oracle 8i you will recognise that this course is going to do for the latest versions of Oracle what that book did for Oracle 8i.

Unlike previous courses, however, which had a fixed timetable and were strictly set to four sessions of equal length each day, this course will have a flexible timetable where the number and duration of pauses will depend on the amount of interaction from the attendees. There will, however, be a lunch break in the middle of the day, and at least one break mid-morning and another mid-afternoon. Throughout the sessions we will always try to focus on the questions – how does knowing this information help me either make the application more efficient, or reduce the amount of maintenance work I need to do.

Topic 1

What are we trying to achieve with this course, and what topic areas are we aiming to cover. How does better information about available data structures allow us to improve the efficiency of the database and help us spend less time on maintenance operations.

Topic 2

Tablespaces and Files
The large-scale structures in Oracle, where they fit together to make a manageable, recoverable, database. Thoughts on big files vs. small files block sizes, extent and segment space management, logging, default storage, transportable tablespaces, and encryption.

Topic 3

Simple Heap Tables
The basic structure; column types, design considerations and storage mechanisms. Some internals, column ordering and chained rows. The importance of constraints. he costs and benefits of compression.

Topic 4

Simple B-tree indexes
What they are for, how they are structured. How we need to design indexes. The significance of the clustering_factor, the relevance of number of rows per key value. Maintenance issues and strategies.

Topic 5

Views are neither good nor bad, they are just a convenience tool that can be used or abused. How views work, how to manage views.

Topic 6

Bitmap Indexes
What they are, when they are relevant, how they are structured. Maintenance issues and strategies. The implications of “btree/bitmap conversions”. Since bitmap Indexes are available only in Enterprise Edition we review an option for “emulating” bitmap plans with btree indexes.

Topic 7

Clusters and Index Organized Tables
Before we get to partitioning, there are structural options in Oracle that allow us to consider “co-location” of similar data items to allow for maximum efficiency during retrieval. We examine the costs, benefits and suitability, and some of the internals, of these structures.

Topic 8

Advanced indexing
Function-based indexes, descending indexes, reverse key indexes; case-insensitive indexes, linguistic indexes, index compression costs and benefits. Problems with indexes.

Topic 9

Clones, Triggers and Materialized Views
Performance comes from doing the least work at the right time. We can design a system to do more work when we have spare capacity so that we can do less work when it really matters. We see how materialized views – and “home-grown” variants on the technology – can help.

Topic 10

If we can break big objects into little pieces we may be able to work more efficiently by ignoring the pieces we’re not interested in, and changing the way we handle the ones we are interested in. For backup and recoverability it’s helpful to be able to copy large objects in pieces, and even to make as many pieces as possible read-only to reduce the maintenance overheads. Partitioning is a big topic, but we will cover the critical ideas. Partitioning is an extra option on top of the Enterprise Edition.

Topic 11

Objects and Large Objects
If we have time at the end of the seminar, this is an optional extra session to cover two of the less frequently used areas of the Oracle database.
The “Object-Relational” database never really acquired much momentum. This is probably a good thing since all it did (at least in Oracle’s case) was decompose objects into a relational form and hide a number of mechanical aids inside the server – making it hard for the developer or DBA to work out how to make their code efficient. We will look at a few details of Oracle’s implementation to explain where the threats lie.
LOBS (large objects) are becoming more popular as CRM (customer relation management) systems start to include geographical information, and companies move to electronic document management. We will examine a few details of how Oracle manages LOBs (and the separately licensed securefiles) and consider the strategies we need to avoid the traps of maintaining very large data items.

Back to main Tutorial Page


  1. How do you sign up for these seminars?

    Comment by tongelja — July 11, 2013 @ 8:21 pm BST Jul 11,2013 | Reply

    • tongelja,

      If you check the “Public Appearances” page it show my schedule. When events are confirmed and bookable they will show a live URL for further details and registration. At present, this course is provisionally scheduled for 26th / 27th Sept in Minneapolis – with a live web feed for off-site attendees; but registration is not yet open.

      Comment by Jonathan Lewis — July 12, 2013 @ 8:11 am BST Jul 12,2013 | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Powered by