efficiency in a query

Posted on 2002-03-20
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 20 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.
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now


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

Independent Software Vendors: 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 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

635 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