Hotsos Symposium Speaker – Richard Foote

Richard Foote is a new Hotsos Symposium presenter.

Biography

Richard FooteAfter migrating to Canberra, Australia from his native England in 1982, Mr. Foote obtained a Bachelor of Arts degree in Computing Studies in 1986. His first IT position was as a Cobol Programmer with the Australian Public Service. Richard worked in various roles, including Programming, Project Management, Data Modeling, Database Design, and finally specializing in Application Tuning and Technical Training.

In late 1996, Richard joined Oracle Corporation, as a DBA Specialist and Technical Trainer, based in the local Canberra office. He is regarded as one of the top DBA specialists and instructors in the Australasia region and has won many internal awards and accolades.

In 2002, Richard left Oracle Corporation to become an independent consultant. He worked at various Oracle sites in the Canberra region helping to diagnose and resolve numerous performance issues. During this time, he served as President of the Australian Capital Territory Oracle User Group (ACTOUG) where he also presented regularly. In 2002, Mr. Foote also became a member of the OakTable Network.

In 2005, Mr. Foote was permanently employed by the Australian Government as a DBA specialist, supporting mission critical, large scale Oracle databases. He regularly contributes to various Oracle-related newsgroups and forums.

Richard is an Oracle Ace Director.

Presentation Title

Session #1: Oracle Indexing Myths
and
Session #2: Oracle Indexing Tricks and Traps

Abstract

Session #1, Oracle Indexing Myths: There are a great number of Oracle related myths, many specifically in relation to Oracle indexes and how indexes are commonly misunderstood. Many of these misconceptions are often due to a basic misunderstanding in how Oracle indexes actually work. This can have a direct and detrimental impact in the index related practices of both DBAs and developers, which can consequently impact the performance of the database and related applications.

This presentation will dispel many of these myths such as: deleted space not being reused within B-Tree indexes, indexes not required for small tables, unique indexes required to police PK/UK constraints or conversely that unique indexes are redundant, it's more efficient to put columns with high cardinality first in a composite index, indexes always prevent sort operations, a bitmap index is useful with low cardinality columns while not useful with high cardinality columns, to name but some of the myths.

All topics include test cases and examples to illustrate why they're not true and to highlight the correct behaviour of Oracle indexes. With such knowledge comes understanding and consequently the implementation of index related best practices.

Session #2, Oracle Indexing Tricks and Traps: This presentation discusses a number of useful little tricks and dangerous little traps in relation to Oracle indexes that are important to know as the tricks can make life a lot easier and the traps a lot harder for both DBAs and developers.

Topics include such things as: knowing how to effectively index null values within a b-tree index, how to only index values in a column that are worth indexing, how the introduction of a single value can both make an index substantially less efficient and the CBO far less capable of using the index appropriately, how index monitoring can provide deceptive information regarding the usage of an index, how a simple missing or novalidate constraint can result in a valid index being ignored by the CBO, how ASSM can make many indexes useless, how index compression can result in making indexes significantly larger, not smaller, how to make the CBO use an index that doesn't actually exist, to name but some index tricks and traps.

All topics include test cases and examples to illustrate the point. With such knowledge comes understanding and consequently the implementation of index related best practices.

Presentation Materials

Presentation materials are available online to attendees only.

Schedule

The speaker schedule is as follows: