Improve company productivity with a Business Account.Sign Up


efficiency in a query

Posted on 2002-03-20
Medium Priority
Last Modified: 2008-03-06
select a.col1,b.col2,c.col3 from tab1 a,tab2 b,tab3 c where and and c.col4='hello';
If col4 value 'hello' of tab3 is search origin, should it come first of where or last. Does the order of selected items or where condition items affect the performance. We can have joins without relationship just to meet our requirement. In this case am I correct that the column in relation chain = not related column in where clause and not the other way round --- sesh2002
Question by:sesh2002
  • 2
  • 2
  • 2
  • +1

Accepted Solution

rkogelhe earned 80 total points
ID: 6882667

The answer to your question depends on whether you are using the cost-based optimizer (CBO). I assume you are, because it is only in rare instances that the rule-based optimizer is still used.

When using the CBO, the order of the tables or views in the FROM clause is important when using the ORDERED hint only. Otherwise the CBO doesn't pay attention.

The ORDERED hint tells the CBO to attempt to find an execution plan that retrieves the rows for the first table or view appearing in the from clause (that match the criteria conditions that can be applied), followed by the second, etc. This means that if you provide a bad order, the CBO could end up performing cartesian merges.

When using the CBO, the order of the predicates in your where clause is important when using the ORDERED_PREDICATES clause. Otherwise the CBO doesn't pay attention.

As a general rule, I put the tables in the from clause in the order that I expect them to be manipulated so that if I want, I can later add an ORDERED hint if necessary.

When using the rule-based optimizer (which you may wish to do when performing queries across heterogeneous databases or on the system tables), the order of the FROM clause is important. Oracle performs the query in the opposite manner to that of the ORDERED hint if there is more than one possible way to execute the query.

I didn't understand what you meant after "We can have joins without..." could you clarify?



Expert Comment

ID: 6882670
correction... ORDERED_PREDICATES is a hint, not a clause.
LVL 36

Expert Comment

by:Mark Geerlings
ID: 6883166
I agree with rkogelhe, the order of the tables in the "from" clause (as well as the order of the tables in the "where" clause - by this I mean which one is to the right of the =, and which one is to the left) can have a significant difference on performance.  And yes, Oracle's Cost-Based-Optimizer and the older Rule-Based-Optimizer use the order of the tables in the "from" clause oppositely.  So please tell use which optimizer mode you use.

I do not understand your last sentence: "In this case am I correct that the column in relation chain = not related
column in where clause and not the other way round."  Please state that another way to help us understand it.
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.


Expert Comment

ID: 6884091
Just Use hint as,
select /*+ Ordered use_nl(c,b,a) index(b) index(a)
tab3 c,
tab2 b,
tab1 a
where c.col4='hello'

Here you have selection on C only and
it shows c is bigger than b and b is bigger than a.

Good luck.


Author Comment

ID: 6885957
Markgeer, We are using cost based optimizer and the answer to your second doubt is: There is one table which is not related to others in anyway. Instead of adding a relation, we have joined it in the where clause condition i.e. if table A has colA and related to table B with colB but table C is having colC but not related and having dependence with table B, then B.colB=C.colC OR C.colC=B.colB which is correct OR we cannot have joins without a relation. Will this work or not in the long run. Right now we are getting the correct result --- sesh2002
LVL 36

Expert Comment

by:Mark Geerlings
ID: 6886554
I am still somewhat confused.  You said "There is one table which is not related to others in anyway."  If that is true, and if that table has multiple records and you include it in the "from" clause but not in the "where" clause, you will get a cartesion product (or a lot more records in the result set than there are in the tables that are related).

Or did you mean that table B is related to table A, and table C is only related to table B, but not table A.  That is no problem.

If you could post the actual SQL statement you use, that may make things clearer.

Author Comment

ID: 6890741
markgeer, here are the queries:
select a.ques_text, f.answer_text,f.answer_score from duke_ques a, duke_prod_super b,
duke_super_ques c ,duke_super d, duke_ques_ans e, duke_ans f where d.super_id=
b.super_id and b.prod_id='prod460009' and d.super_id=c.super_id and a.ques_id=c.ques_id
and a.ques_id=e.ques_id and f.answer_id=e.answer_id;

Alias e i.e. table duke_ques_ans is not related to any other table, but used in query since it meets the requirements. We thought of not creating the desired relation as of now due to application constraints. Will the query keep fetching correct data in future also. So, it should be a.ques_id=e.ques_id OR e.ques_id=a.ques_id?
--- sesh2002

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

595 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question