| Index a system view [message #357577] |
Wed, 05 November 2008 22:36  |
serkzc
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?
Thanks.
|
|
|
|
| Re: Index a system view [message #357583 is a reply to message #357577] |
Wed, 05 November 2008 23:29  |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
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.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
