Re: Horrible Query Performance, Simple Query -- Performance Tuning Help Needed

One of the other posters came up with a less invasive suggestion - which was to create an index on (index unit_number,event_id) so that the query could operate through the index without visiting the event table. This would eliminate most of the 2,000 physical reads recorded against the "event" identification.

You would still have the political problem that this is a structural change on the active part of the database - though slightly less alarming, perhaps.

As far as materialized views are concerned - there are strategies you could use to make the event access path more efficient through the use of 'prebuilt' MVs and user-defined triggers. But the bottom line on this one would still be that you are picking up 1,000 documents selected randomly from a large table. Until you address that problem, the performance will never be very good.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html


"WhiteDog" <cchenoweth56_at_msn.com> wrote in message 
news:1145659062.096525.11530_at_g10g2000cwb.googlegroups.com...


> Jonathan-

>

> I'm not the 'owner' of this database, so I doubt that they will let us

> create a different table structure for anything.  It looks like

> creating clustered tables or IOTs require that we recreate the

> underlying tables?  The database was originally built by 3M, and I

> don't think we can make changes to the underlying structure, but only

> add indexes to it.

>

> Is there any index that we could add to these tables that could speed

> them up?

>

> Another idea we are thinking about is to create a materialized view

> based upon these tables.  We could then put the appropriate indexes on

> the view to get the data we want quickly.  However, we wanted to avoid

> having to do this.

>

> Thanks for all your help everyone!

>

> -- Chad

>