Re: Slow SQL_Statement

Hello again Thomas,

I reckon you should go with Jonathan's solution on this. Not just because he is a highly recognised authority in this area,but also due to the reasons mentioned by William Robertson. Lothar is correct to say that a function-based index is one way around this issue, but you need to be careful for the reasons highlighted by William.

My own personal belief is that you should not use a special Oracle feature like function based indexes, if there is a more simplistic approach. In your case, there is certainly that.

The approach I mentioned would require the creation of a composite index (ideally unique if that's possible) on the mandant, kndid, ktoid columns in the ACCT_RELATION table, if one doesn't already exist. However, I feel composite indexes should be avoided for storage and performance reasons wherever possible (whenever anything changes in ACCT_RELATION, Oracle would need to update the index for three seperate columns) , and Jonathan's approach would enable you to do this.

Just one thing. Are you using the Cost Based or Rule Based Optimiser? If you have the necessary privs, you can find out by typing SHOW PARAMETER optimizer_mode: on the command line in SQLplus. If you're using Rule Based, you need to consider that Oracle have discontinued support for this in 10g. You should ideally be using Cost. If you get anything like CHOOSE or FIRST ROWS as the output of this statement, you are using the Cost Based Optimiser.

The reason I ask this is that if you're using Cost Based Optimisation, you need to have up to date statistics in place on the tables you're joining in order for the Optimiser to make accurate decisions about which query execution plan to choose. You should consider using the DBMS_STATS package to gather these statistics.

Hope this helps.

Cheers.

James Received on Tue Dec 13 2005 - 04:02:30 CST