Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 35

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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.


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 35

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

722 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