DBMS_REDEFINITION, clustering and how an outline helps to make it completely ONLINE

At last a new post from my hand after months of silence. Well, silence in writing then: in the mean time we got our third child and I participated in the World Championships Double Decathlon in Delft. Those two things were enough for me to handle next to regular work 😉

In this post I want to share good experience I had in the use of DBMS_REDEFINITION to speed up the Siebel OLTP application I am responsible for (at least, for the performance part of it). It is about clustering of data and rebuilding tables as ONLINE as can be and how I used a trick with a stored outline to overcome a performance problem during rebuild of one table.

Eliminating workload from the system using materialized views

The last months I have been looking into a customer’s ETL process. They have a Siebel OLTP and Analytics (now OBI 10) system (Data warehouse) and every night an ETL process runs in order to maintain the Data warehouse. When I arrived at this customer, the ETL was not a big problem: it ran within the window although at first sight it did look like there were some inefficiencies. But since it was not the highest priority performance problem, I did not watch it very closely. In a Siebel environment, there are always several performance issues to look at, so I got busy enough. However, at one time the volume of the data in this fast growing system became a problem for the ETL: with more frequency, the ETL ran into the morning.

Take advantage of a Function based index in First_rows mode with an ORDER BY on NULLable columns

One of the things I encounter a lot in a Siebel application I support, is the fact that there are lots of queries with ORDER BY’s that take too long to return the first screen full of records OR are executed just to open a Business Component without any filtering. A typical OLTP application mostly wants the first screen full of records to return as fast as possible, so a FIRST_ROWS optimization is the default way. In Siebel, this FIRST_ROWS optimization mode is hard-coded in the DLL, so no way to even circumvent it.

However especially with the default ORDER BY defined on business components (Sort specification) or the ability for users to define their own ORDER BY, you run into situations where this ORDER BY makes the first records to return too slowly, since the whole result set will mostly be materialized, sorted and then the first rows can be returned, but…