Database indexing

Problem: Audits getting slower over time but nothing has changed.
Solution: Re-index your database audit tables.

At UCI, we use In-progress classes and update them on a daily basis. With 30,000 students in DW, a lot of database records get changed, thus database indexing becomes overwhelming.

DW and Oracle doesn't know when or how to re index the audit tables so it would be wise to add this to your quarterly maintenance. If I don't re index, our audits take twice as long to process.

The tables - dap_audit_dtl, dap_audtree_dtl

The indexes - for dap_audit_dtl: (via xsql, select index_name from dba_ind_columns where table_name='DAP_AUDIT_DTL';)

for dap_audtree_dtl: (via xsql, select index_name from dba_ind_columns where table_name='DAP_AUDTREE_DTL';)

Two options in re indexing:

A) Simply rebuild the index in xsql:

1. login to the account.
2. login to xsql.
3. type the rebuild index command for each of the indexes: "alter index DAP_AUDIT_DTL_A rebuild unrecoverable;"
4. Do this for each index. (DAP_AUDIT_DTL_A, DAP_AUDIT_DTL_B... etc.)

B) Rebuild the index in a new tablespace (recommended for very large FTE's):

1. Same as above but add the tablespace command: "alter index DAP_AUDIT_DTL_A rebuild tablespace (tablespace name) unrecoverable;"

Option B recommended by Oracle DBA for very large tables because instead of using temp space to create the new indexes, it just builds them in the new tablespace. Plus this is a much faster process.

Our I alternate between two tablespaces that are 6 GB large just for dap audit indexes. Yes, that is very large. Our tablespaces just for audits are 20 GB.

** Please consult with your IT staff at your site before experimenting with this. ***