At 09:04 AM 15/10/2004 +0200, you wrote:

>Hi
>
>I did a data pump to onto a blank database to get the metadata of my live
>database the same as the development server. Seemed like a good idea at the
>time, but now the live server is generating plans different to the dev
>server, even though the data is the same. The system has become unusable
>because the queries take so long to run. I suspect it has something to do
>with the fact that a pump adds data with the indexes active, while a restore
>adds the indexes on complete data thereby creating more accurate stats for
>selectivity. Would disabling and then renenabling an index do the same
>thing, or maybe dropping and recreating it? The data is very big so I dont
>want to do a backup/restore unless I really have to.

Your assessment of the cause of the problem is correct. Next time, set the
indexes inactive before you pump. Actually, if you have some "costly"
foreign keys, you should defer adding those constraints until after the
pump is complete.

Right now, you can set your indexes inactive then active to rebuild
them. If you have any of those problem foreign keys, then you should
simply remove those constraints and then reapply them (you can write a
script to do that).

This exercise could take quite some time. Use scripts. If you have some
really large tables and a lot of indexes, it's a bit debatable whether
backup and restore would not be faster by the stopwatch.

btw, this isn't an IBO issue - it's really off-topic here.

Helen