This page provides queries that will help you to get indexes and then compare the indexes with Standard CAST Database Indexes.
Release | Yes/No |
---|---|
8.3.x | |
8.2.x | |
8.1.x | |
8.0.x | |
7.2.x | |
7.0.x |
Updating Indexes number and Primary keys
CAST databases have indexes that enable performance tuning when running analysis, Snapshot preparation assistant, snapshot generation, dashboard browsing, etc. The indexes number in the customer's database should be the same as the indexes number in a standard CAST database.
Action plan
- Run the query given in Getting the indexes list and all their properties
- Before following the instructions given in this paragraph, install a new standard database in the same version as the customer's database.
Note: the result of the indexes for following CAST version are already available so there is no need to install fresh CAST Versions - Compare the standard CAST database indexes number with the customer's database indexes number
- Apply the solution
Compare the standard CAST database indexes number with the customer's database indexes number
Get the standard CAST database indexes number and the customer's database indexes number by running the following query on each database
select count ( * ) from ALL_INDEXES where TABLE_OWNER like '<database>'
- Compare the indexes' number in the customer's database with the indexes' number in a standard CAST database
to compare
Apply the solution
The customer's database indexes number is smaller than the standard CAST database indexes number
Update the customer's database's indexes and primary keys by doing the following:
- Get the drop/create script for indexes and Primary Keys. To get it, use Release Builder as follow
- Connect to the server hosting the standard schema
- Choose Script extraction (also accessible from the menu 'Oracle')
- Check the option Live schema
- Choose the standard schema from which the script will be extracted then --> click next
- In the Object Selection wizard, check Indexes and PK/U constraints
- In the Script Destination File wizard, check Create Statements, Drop Statement, set the target output file, check the option Single file, give a name to the destination file then click the Extraction Options button
- in the Script Extraction Options wizard, uncheck all the options in section Script contains then click OK
- Click Extract
- Execute the script in the customer's database:
- Go to Start > run > type cmd
- Type: sqlplus database_user/pwd@server_name (replace the database_user, pwd and server_name with their values), then
spool c:\temp\execution_log_file.txt @path\to\the\<destination file name>.sql spool off
- Check the log file to ensure that the script was executed without error
- Check again the customer's database indexes number by running the query given in Compare the standard CAST database indexes number with the customer's database indexes number
The customer's database indexes number is greater than the standard CAST database indexes number
Update the indexes (drop the extra indexes) by doing the following:
Get the customer's database's indexes and the standard database's indexes by running the following query on each database:
spool c:\temp\customer_indexes.txt select INDEX_NAME, STATUS, UNIQUENESS, TABLE_NAME, TABLESPACE_NAME, TABLE_OWNER from ALL_INDEXES where TABLE_OWNER like 'database' order by INDEX_NAME; spool off
- Compare the two files customer_indexes.txt and standard_indexes.txt in UltraEdit for instance.
If the extra indexes are in CAST standard tables, drop them by executing the following query:
Drop index index_name;
- If the extra indexes are not in CAST standard tables, then they might be added for other purposes like custom packages, views. You will need to investigate their purpose with the customer.
Updating the indexes' validity
Check
Get the customer's database indexes by running the following query:
spool c:\temp\customer_indexes.txt select INDEX_NAME, STATUS, TABLESPACE_NAME from ALL_INDEXES where TABLE_OWNER = 'database_name' and STATUS='INVALID' order by INDEX_NAME; spool off
Solution
When INVALID indexes are identified, follow below steps to rebuild them
- Launch a SQL Session and connect with the database user
Run the following query for all the invalid indexes
ALTER INDEX index_name REBUILD TABLESPACE database_tablespace_name;
The tablespace name is reported in the column TABLESPACE_NAME of the query result
Once all the invalid indexes have been rebuilt, check again for invalid indexes by running the query
select INDEX_NAME, STATUS, TABLESPACE_NAME from ALL_INDEXES where TABLE_OWNER = 'database_name' and STATUS='INVALID' order by INDEX_NAME;
The query must return NO ROW
Updating the indexes' uniqueness
The standard existing indexes in the customer's repository must all have the same properties as in the standard CAST version.
Action plan
- Before following the instructions given in this paragraph, install a new standard database in the same version as the customer's database.
- Compare the standard CAST database indexes with the customer's database indexes
- Apply the solution
Compare the standard CAST database indexes with the customer's database indexes
- Get the customer's database indexes and the CAST standard database indexes by running the following query on each database:
spool c:\temp\customer_indexes.txt select INDEX_NAME, UNIQUENESS, TABLE_NAME, TABLESPACE_NAME, TABLE_OWNER from ALL_INDEXES where TABLE_OWNER like 'database' order by INDEX_NAME; spool off
- Compare the two files customer_indexes.txt and standard_indexes.txt
Apply the solution
If the customer's database's indexes do not have the same properties as the standard database, then repair the failed indexes. To do this, use the following command:
ALTER INDEX {{index_name }}SET UNIQUENESS='<uniqueness value>'
For instance ALTER INDEX {{ACCRAW_IDACCACCRAW }}SET UNIQUENESS= 'UNIQUE'
Note
Run the queries in this page with the database user.