Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

Help with Oracle Sql tuning anybody

Mel_12
Advisor

Help with Oracle Sql tuning anybody

 
5 REPLIES
Brian Crabtree
Honored Contributor

Re: Help with Oracle Sql tuning anybody

Mel,

Make sure that your tables have been analyzed recently.

Get off of Oracle 7.

Thanks,

Brian
Indira Aramandla
Honored Contributor

Re: Help with Oracle Sql tuning anybody

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.
Never give up, Keep Trying
Raynald Boucher
Super Advisor

Re: Help with Oracle Sql tuning anybody

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
Advisor

Re: Help with Oracle Sql tuning anybody

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
Trusted Contributor

Re: Help with Oracle Sql tuning anybody

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.
There are a 100 ways to do things and 97 of them are right