Re: Query with LONG RAW

From: Randolf Geist <mahrah_at_web.de>
Date: Wed, 3 Mar 2010 03:09:17 -0800 (PST)
Message-ID: <449fbc84-319e-442d-a3c1-c3a28c294504_at_t41g2000yqt.googlegroups.com>

On Mar 2, 9:46 pm, "bob123" <bob..._at_gmail.com> wrote:
> How can I speed up the query below (third party software)
> it takes 30 min to execute.
> Oracle 9.2.0.6
> LONG RAW on PFMQ_MESSAGEDATASTORAGE

I forgot to mention that Jonathan Lewis was so kind to publish a ready- -use script that allows to calculate an approximate index size which might help you if you can't do the ANALYZE INDEX ... VALIDATE STRUCTURE on your system:

http://jonathanlewis.wordpress.com/2010/02/28/index-efficiency-2/

Please read the comments and notes how to use it and interpret the results of the script.

Also - as mentioned in the blog post's comments you could use EXPLAIN PLAN in 10g on the CREATE INDEX statement to get an index size estimation from Oracle itself.

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

Co-author of the "OakTable Expert Oracle Practices" book: http://www.apress.com/book/view/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684 Received on Wed Mar 03 2010 - 05:09:17 CST