Table / Index Rebuild,, less data but slower

Table / Index Rebuild Problem, less data but slower response.

This is a real challenge question. Two 20 million row tables, X and Y, were created from two 40 million row tables, A and B. The 40 million row tables were dropped and the 20 miliion row tables were renamed to A and B respectively. The one PK index on A was recreated and the 4 indexes on B were recreated. The tables and indexes reside on the same file systems as before the rebuild, but the application runs 10 times slower. It now takes 12 seconds to process a claim as opposed to approx. 1 second.

The X and Y tables were built from the A and B tables using an insert into A select * from A where date_vbl > in_date type of strategy.

The pararell query option is in use, but has been turned off with degree set to 1. The DB is 7.1.4. Cost based optimization is being used.

The following has been tried, indexes have been validated, indexes have been rebuilt (with noparallel), rule based has been tried (response was greater than 40 seconds), tables have been analyzed with compute statistics (a 20 second or greater response time resulted so that analyze estimate had to be used to return response time to 10 seconds).

The application architecture includes Tuxedo and C code with embedded SQL. Because of this being a production system, and due to the volume of transctions tkprof has not been tried. The next attempt will be to try an import export - simply because no other ideas come to mind.

WHAT CAN WE TRY THAT HAS not been tried already? If anyone has the cure for this one, I think they should be president!

Regards,

Alexander Davis

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet