Hotsos Symposium Speaker – Tim Gorman

Tim Gorman is a returning Hotsos Symposium presenter.

Biography

Tim GormanTim Gorman began his IT career in 1984 as a "C" programmer on UNIX and VMS systems, and now focuses on performance tuning applications, databases, and systems, as well as data warehouse design and implementation, database backup and recovery, architecture and infrastructure, and database administration.

Mr. Gorman co-founded Evergreen Database Technologies, Inc. and is widely recognized as an expert in tuning and trouble-shooting advanced technology, large-scale, highly-complex, secured systems. Tim co-authored Expert Oracle Practices — Database Administration from the OakTable (Dec 2009, published by Apress) and Oracle Insights — Tales of the OakTable (July 2004, published by Apress).

Tim is an Oracle ACE Director .

Presentation Title

Fastest UPDATE is an INSERT

Abstract

Many people are well aware of the basic EXCHANGE PARTITION technique for loading into data warehouses, but that basic technique doesn't cover nearly all of the load scenarios. Besides the matter of quickly loading data into a time-variant copy-on-write type-2 fact or dimension table, there is always a need to perform MERGE or "up-sert" (i.e., update-else-insert) logic into a point-in-time type-1 fact or dimension table.

This can usually be accomplished with a MERGE statement or PL/SQL procedure performing explicit UPDATE and INSERT statements. These mechanisms are easy to implement and fulfill the business requirement, but they can be dreadfully slow, especially with large amounts of data changes, or when bitmap or bitmap-join indexes are affected.

This presentation presents the problem, in the form of the standard solution, and the resulting effects on performance, and then thoroughly lays out the solution, in the form of a variation of the EXCHANGE PARTITION load technique, which accomplishes the same result much faster, with less impact on end-users, and also enables the effective use of features like compression and HCC. This presentation is the result of almost ten years of large-scale data warehouse implementation under extreme data volumes, and will include re-usable code, scripts, and references.

Presentation Materials

Presentation materials are available online to attendees only.

Schedule

The speaker schedule is as follows: