SQL Queries - Common SQL Queries - Queries on Indexes - How to get indexes and how to compare them with Standard CAST Database Indexes

Purpose

This page provides queries that will help you to get indexes and then compare the indexes with Standard CAST Database Indexes.

Applicable in CAST Version

ReleaseYes/No
8.3.x✅
8.2.x✅
8.1.x✅
8.0.x✅
7.2.x✅
7.0.x✅

Details

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

  1. Run the query given in Getting the indexes list and all their properties
  2. 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
  3. Compare the standard CAST database indexes number with the customer’s database indexes number
  4. Apply the solution

Compare the standard CAST database indexes number with the customer’s database indexes number

  1. 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 ‘’

2. 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:

1. Get the drop/create script for indexes and Primary Keys. To get it, use Release Builder as follow
   1. Connect to the server hosting the standard schema
   2. Choose **Script extraction** (also accessible from the menu 'Oracle')
   3. Check the option **Live schema**
   4. Choose the standard schema from which the script will be extracted then --> click next
   5. In the **Object Selection** wizard, check **Indexes** and **PK/U constraints**
   6. 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
   7. in the **Script Extraction** Options wizard, uncheck all the options in section **Script contains** then click **OK**
   8. Click **Extract**
2. Execute the script in the customer's database:
   1. Go to Start > run > type **cmd**
   2. Type: sqlplus database\_user/pwd@server\_name (replace the database\_user, pwd and server\_name with their values), then  

      ```text
spool c:\temp\execution_log_file.txt
@path\to\the\<destination file name>.sql
spool off
  1. Check the log file to ensure that the script was executed without error
  2. 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:

  1. 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

2. Compare the two files **customer\_indexes.txt** and **standard\_indexes.txt** in UltraEdit for instance**.**
3. If the extra indexes are in CAST standard tables, drop them by executing the following query:

   ```text
Drop index index_name;
  1. 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

  1. Launch a SQL Session and connect with the database user

  2. 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
3. Once all the invalid indexes have been rebuilt, check again for invalid indexes by running the query

   ```sql
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

  1. Before following the instructions given in this paragraph, install a new standard database in the same version as the customer’s database.
  2. Compare the standard CAST database indexes with the customer’s database indexes
  3. Apply the solution

Compare the standard CAST database indexes with the customer’s database indexes

  1. 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

2. 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:

```sql
ALTER INDEX {{index_name }}SET UNIQUENESS='<uniqueness value>'

For instance ALTER INDEX {{ACCRAW_IDACCACCRAW }}SET UNIQUENESS= ‘UNIQUE’

Notes / Comments

Note

Run the queries in this page with the database user.

Related Pages