Rebuilding Maximo Text Indexes due to Query performance issues

Sometimes it is necessary to rebuild text indexes after database move or copy from Prod to other instances. IBM does have an article for Rebuilding Oracle Text Indexes. Basically this process drops all the domain indexes and recreates them. 

These are the steps are just walkthrough with the same process in detail.

1. Follow the steps in the above article to remove any DBMS jobs

Remove any occurrences of DBMS_JOB

Determine how many DBMS_JOBS are running by executing this from SQLPLUS logged in as the schema owner:

select job, substr(what,1,80) from user_jobs;

For each result where "what" contains maximo_ts_job, execute the following commands:

begin
dbms_job.remove(####);
commit;
end;
/

Substituting one of the job numbers from the select for the ####.

2. Change the job 

Change to use DBMS_SCHEDULER

Recreate the job submission procedure and run it:

CREATE OR REPLACE PROCEDURE maximo_ts_job_call AS
BEGIN
  dbms_scheduler.create_job(
  job_name => 'MAXIMOTSSYNC',
  job_type => 'STORED_PROCEDURE',
  job_action => 'maximo_ts_job',
  start_date => SYSDATE,
  repeat_interval => 'SYSDATE + 5/1440',
  enabled => TRUE);
END;
/
exec maximo_ts_job_call();

3. IBM Recommends stopping the application server, as our server was for only a Development server I decided to try it without stopping it and it didn't cause any issue as there weren't many developers using it.

4. To execute the script copy the attached script and place it somewhere in the local drive. I placed it in c:\maximo folder.

5. Connecting to Maximo database using SQLPlus

SQLplus exe on windows can be found usually in the oracle installation directory. In our case it was located here

C:\app\administrator\product\11.2.0\client_1\BIN\sqlplus.exe

To start SQLPLUS and connect to database you might also have to add an entry about the details about the database connection in the tnsnames.ora  file.

tnsnames.ora file which was located below

C:\app\administrator\product\11.2.0\client_1\network\admin\tnsnames.ora

 I added an entry in the file with details about our database

MAXIMO_DEV =
    (DESCRIPTION=
        (ADDRESS_LIST=
           (LOAD_BALANCE=YES)
              (FAILOVER=YES)
                (ADDRESS=(PROTOCOL=tcp)(HOST=165.165.165.165)(PORT=1562))
            )
        (CONNECT_DATA=
           (SERVICE_NAME= MAXDEV)
        )
    )

6.  After adding entry in tnsnames.ora, open up command prompt and then type below command to connect to the database

C:\app\administrator\product\11.2.0\client_1\BIN>sqlplus maxusername/maxpassword@MAXIMO_DEV 

where 

maxusername is the usename

maxpassword is the password

MAXIMO_DEV is the name of the connection entry in tnsnames.ora file

7. After successful connection you should see the SQL> prompt

8. you can run the copied script by executing the below command on the SQL prompt

SQL> @c:\maximo\rebuildctx.sql

8. It might take several minutes to hours depending on the size of your database and you'll see below screen till the process completes.

If seeing that screen for a long time causes too much anxiety you can also execute the below query on your database and see the number of records increase as the indexes are created. Just don't close the SQLPLUS window.

select index_name,index_type,status,domidx_status,domidx_opstatus from user_indexes

where index_type = 'DOMAIN';

9. After completion it will return back to the SQL prompt displaying the number of indexes it rebuilt

10. Prior to rebuilding the text indexes

Simple query like below 

select * from asset where assetnum like '%AST-01%'

was taking 30 seconds.

After rebuilding the text indexes  it took 2 seconds.