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

Applicable in CAST Version
Release
Yes/No
8.3.x(tick)
8.2.x (tick) 
8.1.x (tick) 
8.0.x (tick) 
7.2.x(tick)
7.0.x(tick)
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 '<database>'
  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
      spool c:\temp\execution_log_file.txt
      @path\to\the\<destination file name>.sql
      spool off
  3. Check the log file to ensure that the script was executed without error
  4. 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:

    Drop index index_name;
  4. 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

    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:

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