Deleting from RDB$USER_PRIVILEGES is too slow in InterBase 4.0.

By: Borland Staff

Abstract: The ODS changed from 7.2 to 8.0 between versions, and when the two additional fields (rdb$relation_name, rdb$user) were added to the table (rdb$user_privileges) they were not optimized.

Problem:
When trying to perform "delete from rdb$user_privileges the
completion time is tremendously slower in 4.0 than 3.x.


Solution:
The ODS changed from 7.2 to 8.0 between versions, and when the
two additional fields (rdb$relation_name, rdb$user) were added
to the table (rdb$user_privileges) they were not optimized.


NOTE:	These indices will not survive a gbak backup/restore, 
	so you'll have to run a script after restore which redefines
	these indices.  But beware, the indices will be created 
	automatically in the next release, so you won't want to 
	doubly define them. 

Test for On-Disk Structure(ODS)

      command line:  "gstat -h mydatabase.gdb"

* If InterBase version is 4.0 it should return
  an ODS of 8.0.

Test: (for System Indexes)

command line:  "SQL> show index rdb$user_privileges"

* There should be none.

Create: (new Indexes for table)

In ISQL:

SQL> 
SQL> create index myindex1 on rdb$user_privileges (rdb$relation_name);
SQL> create index myindex2 on rdb$user_privileges (rdb$user);
SQL> commit;

* Repeat the show index command to ensure that they are truly there.

Execute:

"delete from rdb$user_privileges"

Server Response from: ETNASC03