Help with Oracle Sql tuning anybody

[Brian Crabtree]

Mel,

Make sure that your tables have been analyzed recently.

Get off of Oracle 7.

Thanks,

Brian

[Indira Aramandla]

Yes, I would suggest on the lines of analyzing the tables.

In your query, you have a lot of not null values (nvl). Yoy can redice this checking, it the field had a not null constraint, If the some of these fields can be defined as not null when the data is inserted intot he table (like define a not null constraint on this field in the table), then you do not have to check for nvl each time you query.

[Raynald Boucher]

Hello Mel,

Look at the from clause.

Under 7.3.4 with "RULE", I think the tables are scannned starting from the last one to the first one. So your "sl" table should be specified last.

Organize the table list so the tables are scanned from last to first.

There could be a little problem with that when you migrate to higher versions with "COST" where the order will be reversed. Learn the use of hints.

Take care.
Ray

[Mel_12]

Thanks guys. I'll take your advice to heart especially that of Ray's. We are in a tight budget here and migrating to newer version of Oracle is not an option for us now.

Mel

[Rory R Hammond]

Here is a oldie but goody for sql tips.



http://www.wsl.ch/relics/rauminf/riv/datenbank/general/tinalondon.html

All of the ANDS mean exclusivity. I would consider changing the where clause to have the AND that is likely to "excludes" most of the data first. You of course knows your data.