Home » RDBMS Server » Performance Tuning » Index a system view (10g on IBM AIX)
Index a system view [message #357577] Wed, 05 November 2008 22:36 Go to next message
Messages: 1
Registered: November 2008
Junior Member
Hi all,

One of the DBA's at a site which I am working on has suggested that we create an index on a system view (sys.dba_audit_trail). Reasons why they have suggested this is because of auditing requirements. We run an SQL statement against this view on hourly intervals to collect database auditing information. The sql uses the EXTENDED_TIMESTAMP column to determine its last position in the view. At present, doing this method seems to take upto 5 minutes and uses a fair amount of CPU. We are looking at way to optimise this to decrease the impact on the system. So my question is, 1. is it possible to create an index on the system view? 2. Where does Oracle stand with support if we were to go down this path?

Re: Index a system view [message #357583 is a reply to message #357577] Wed, 05 November 2008 23:29 Go to previous message
Michel Cadot
Messages: 65138
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1. You can't create an index on a view (system or not):
SQL> create index v_i on v(id);
create index v_i on v(id)
ERROR at line 1:
ORA-01702: a view is not appropriate here

2. Creating an index on audit base table will slow down every audited operation. For Oracle support, you have to ask them as only Oracle can answer.

Previous Topic: Dirty database_Performance issues
Next Topic: Performance: partition/index not used
Goto Forum:

Current Time: Fri Aug 18 00:13:15 CDT 2017

Total time taken to generate the page: 0.09605 seconds