Oracle import - indexes not being used?!

From: Russell Fray <news_at_it-logic.com>
Date: Fri, 7 Feb 2003 23:53:03 -0000
Message-ID: <b21ia3$um2$1@news6.svr.pol.co.uk>

My main database is Oracle 8.1.6.4.0

I am migrating to a new server which is running Oracle 8.1.7.4.0 enterprise.

I used a full export on the 8.1.6.4 DB, and used a full import on the 8.1.7.4 DB. It appeared to work fine - a few small errors in the import log but nothing significant.

Now, when I run a particular select on the new DB, it takes about 5 minutes compared to 5 seconds on the old machine. Hardware is identical. I used explain plan, and the migrated DB isn't using the indexes for the query. But, the indexes DO exist! I even dropped them and recreated them and it simply doesn't use them.

Is there a known bug, or something that I am obviously doing wrong? The indexes have definitely both been exported and imported, and I'm not doing anything clever with indexfile= or indexes=y/n etc.

My 8.1.7.4 DB has the extra features of that release, namely partitioning and extra index functions (ie bitmaps etc), global/local indexes, but if I am doing a straight import without anything clever, what could possibly be going wrong?

Thanks for any pointers/clues...!!
Russ. Received on Fri Feb 07 2003 - 17:53:03 CST