Index Rebuild

Yesterday our loading software came to a halt in the production environment. The DBA team came to the rescue and determined some tables had indexes that were just out of date. A quick real time rebuild got things rolling again.

Now our DBA team is looking to be more proactive. We had a weekly job that rebuilds the indexes for some major tables. However we started looking for other tables to rebuild indexes for.

The DBAs told us that they were looking for tables that were frequently updated. I found one such example. A DBA chimed in and agreed the table I identified was a trouble table from a prior performance issue.

You would think that Oracle would have some built in support to keep indexes up to date.

Virtual Private Database

Oracle has a behind the scenes functionality called the virtual private database. It is also known as fine grained access control (FGAC). The database will modify queries automatically to present a partial view of a database table. There is policy_function which returns the WHERE clause that masks the query results.

This functionality can be as simple as hiding certain rows from a result set using a more restrictive WHERE clause. It can also mask out some columns. This effectively creates a customized virtual object that is generated on the fly. This is very similar to a view. It is just that each user gets a custom view.

The FGAC can implement row level security. As such you can lock down data as you see fit. An application context is set at sign on. This is accomplished via a LOGON trigger. The rest is plug and chug by the Oracle database. Essentially you are getting logical security with the virtual private database. Good stuff. We might be using this on our project at work to implement some of the customer’s data hiding business needs.

Global Temporary Tables

Global Temporary Tables are a feature from Oracle 8i. The tables are managed by the database server. They hold data which is private per session. The data is automatically deleted at the end of the session.

These tables can improve query speed. This is because there is no redo or rollback information collected. They also prevent private data sets from interfering with each other. You can consider these tables the ultimate work tables.

Tom Kyte advises against their use. However there is a difference between a temp table and a global temporary table. A temp table is one you create on the fly to do some complex work on. These global temporary tables are permanent tables which users can only see a portion of the data.