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.
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.
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.