Oracle Sql Tuning

65,110 pts.
Tags:
Database
DB2
Oracle
Windows
Hi everybody. I need some help with Sql Tuning. I have a query that returns information from many tables (let's say 5 for the example). 2 of that tables are big tables (20 million rows each one). I usually write 'where' clauses putting first the join conditions and after them, the table specific conditions, but now I'm not sure that's the best way to do it. Example: -- select * from tab1 t1,tab2 t2,tab3 t3, tab4 t4, tab5 t5 where t1.col_a = t2.col_a and t1.col_a = t5.col_a and t1.col_c = t4.col_c and t2.col_b = t3.col_b and t2.col_c = t4.col_c and t1.col_a = 'xxx' and t1.col_b = 'uuuu' and t2.col_b = 'yyyy' and t4.col_x = 999; -- Question 1: Could be better to write the specific conditions (like t1.col_a = 'xxx') for the big tables and then the join conditions ? What is the best way to write a query like that ? Question 2: If I've created an index on col_a,col_b,col_c of table_1, how must I construct the where clause so that Oracle use that index when executing the query ? Question 3: Do you know some place in the web where I can get some documentation on sql tuning (best if free :-)) I'm using Oracle 8i Ok, that's all, Sorry if my english is not so good. Thanks in advance.
ASKED: February 3, 2006  2:03 PM
UPDATED: February 6, 2006  1:28 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

This linke will provide some useful informaton:

http://asktom.oracle.com/pls/ask/f?p=4950:8:8488073385483584370::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:8764517459743

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • MrOracle
    Oracle doesn't take the ordering of the WHERE clause into consideration, as such. Believe it or not, it is the order of the FROM clause that matters - all things being equal. In RULE mode, that is the database set to RULE or NONE of the tables involved in the query having been analyzed (...and no partitioning and no parallel... 8^( ), then Oracle optimizes from the LAST entry in the FROM clause backwards to the FIRST. If you use an ORDERED hint, then it is backward! First to last table as listed in the FROM clause. It always pays to run an explain plan to be sure the tables and indexes that you intended to use are in fact BEING used. If not, Oracle can occasionally surprise you by finding a BETTER way. Unfortunately, it doesn't usually come to our attention unless it did a MUCH WORSE job of it! 8^)) I hope this helps. BTW, there is a tuning guide as part of the Oracle manual set. It might not have been a separate book back in version 8, but all this is out there if you have a LOT of patience. ;^> Good luck! ((MrO))
    0 pointsBadges:
    report
  • Saratusthra
    I do not believe the order of the joins is that important when join tables because in general, the system optimizes the query as long as the same makes sense. If your index on table_1 is called Table_1_Indx, then use that name throughout the statement. As a matter of preference, I like the following approach when coding. select * from tab1 t1 inner join tab2 t2 on t1.col_a = t2.col_a inner join tab5 t5 on t1.col_a = t5.col_a tab4 t4 on t1.col_c = t4.col_c inner join tab3 t3 on t2.col_b = t3.col_b inner join tab4 t4 on t2.col_c = t4.col_c where t1.col_a = 'xxx' and t1.col_b = 'uuuu' and t2.col_b = 'yyyy' and t4.col_x = 999; This way, I keep the entire join in one place (FROM) and I can control whether I want an INNER, LEFT or RIGHT type of JOIIN, which in Oracle is done by using an asterisk (*) next to the table where you want to preserve all the records. The WHERE clause I leave it for the specific required conditions of the statement.
    0 pointsBadges:
    report
  • SheldonLinker
    There is a stage in the execution of a query called the Optimizer. It decides how the query needs to be executed. Usually, it does a pretty good job. You can see how things are going to be done using SHOW PLAN. You can alter how things are going to be done with HINTs. Hints are placed immidiately after SELECT, INSERT, or DELETE. They look like this: /*+ command list */. Example hints: /*+ ORDER */ This causes the FROM clauses to be used in reverse order /*+ FULL(xxx) INDEX(yyy) INDEX(zzz aaa) */ This causes xxx to be table-scanned, then yyy brough in using one of its indeces, then xxx to be brought in using index aaa. There's a whole manual secction on this type of thing. My other favorite hints are APPEND and HASH. --- Shelon Linker (sol@linker.com) Linker Systems, Inc. (www.linkersystems.com) 800-315-1174 (+1-949-552-1904)
    30 pointsBadges:
    report
  • Jolora
    There are many ways to manipulate the optimizer to ge the best execution plan (to me, "tuning sql" means "manipulating the optimizer," so I think it is better to just start off thinking of it that way). But this other responders here are correct, the order of things in the WHERE clause is completely irrelevamt to the optimizer. For that reason, I've always felt is best to organize my WHERE clauses in such a was as to make sense to a person trying to read it, so that is clearly specifies a) what I want from each table or view; and b) how I am relating multiple tables pr views. So normally, I go down the WHERE clause in the same order that I list the tables in the FROM clause, starting with the master or driving table, and adding subordinate tables as I go. For example, say I need to use TAB1 as a driving table, and join in TAB2 and TAB3 off of TAB1. Then suppose I need to join TAB4 to TAB2, and finally bring in TAB5 to columns from both TAB2 and TAB3. My query would look like this: SELECT .... FROM TAB1 T1 ,TAB2 T2 ,TAB3 T3 ,TAB4 T4 ,TAB5 T5 WHERE T1.COL1 = 'WHATEVER' /*put any restricting criteria for TAB1 first */ AND T1.COL2 = 'JUNK' /*But this is for readability, not tuning */ AND T2.PK_COL = T1.FK_COL2 /*put the JOIN condition for TAB2 */ AND T2.COL3 = 'MOREJUNK' /*put any restricting criteria for TAB2 here */ AND T3.PK_COL = T1.FK_COL3 /*put the JOIN condition for TAB3 */ AND T3.COL4 = 'MOREJUNK' /*put any restricting criteria for TAB3 here */ AND T4.PK_COL = T2.FK_COL4 /*put the JOIN condition for TAB4 */ AND T4.COL5 = 'MOREJUNK' /*put any restricting criteria for TAB4 here */ AND T5.PK_COL2 = T2.FK_COL5 /*put the JOIN condition for TAB5 */ AND T5.PK_COL3 = T3.FK_COL5 AND T5.COL6 = 'WHATEVER' /*put any restricting criteria for TAB5 here */ / this would be my first cut at it, anyway. More analysis may reveal some changes that might get a better execution plan. If possible, I always prefer to specify the indexes for each table. But this also has to make sense. You can usually get good results using the primary key indexes, especially for subordinate tables (like TAB2 and TAB3 in this example). But suppose tables TAB1 and TAB3 have 10 million rows, and index TAB1_COL2_INDX narrows down TAB1 to 1 million rows, but TAB3_COL4_INDX narrows TAB3 to just 100 rows. Then it would make more sense to use index TAB1_FK_COL3_INDX on TAB1, thus using TAB3 as the driver. Specifying these indexes is enough, though. I would not change the FROM or WHERE clauses in my first cut. Leave the query readable and understandable. How do you specify the indexes? Using optimizer HINTs. So for this example, I would add the following hint: SELECT /* index( t1 tab1_fk_col3_indx ) index( t2 tab2_pk_indx ) index( t3 tab3_col4_indx ) index( t4 tab4_pk_indx ) index( t5 tab5_col2_col3_indx ) */ .... FROM ... So you have to examine the data and the available indexes. Most tables with foreign keys should have indexes on those key columns. Sometime you'll need to make them if they do not. But playing around with the indexes is a good way to manipulate the optimizer into the execution plan you want.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following