Re: Table / Index Rebuild,, less data but slower

On Fri, 14 Nov 1997 14:12:11 -0600, al.davis_at_ns.sympatico.ca wrote:

>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

What was the outcome of this? I would be very interested to know. Received on Fri Nov 28 1997 - 00:00:00 CST