Solved

efficiency in a query

Posted on 2002-03-20
7
705 Views
Last Modified: 2008-03-06
select a.col1,b.col2,c.col3 from tab1 a,tab2 b,tab3 c where a.pk=b.fk and b.pk=c.fk 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
0
Comment
Question by:sesh2002
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 3

Accepted Solution

by:
rkogelhe earned 20 total points
ID: 6882667
Sesh,

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?

Regards,

Ryan
0
 
LVL 3

Expert Comment

by:rkogelhe
ID: 6882670
correction... ORDERED_PREDICATES is a hint, not a clause.
0
 
LVL 34

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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Expert Comment

by:kannanb
ID: 6884091
Just Use hint as,
select /*+ Ordered use_nl(c,b,a) index(b) index(a)
a.col1,
b.col2
c.col3
from
tab3 c,
tab2 b,
tab1 a
where c.col4='hello'
and b.pk=c.fk
and a.pk=b.fk;

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

Good luck.

Kannan
0
 

Author Comment

by:sesh2002
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
0
 
LVL 34

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.
0
 

Author Comment

by:sesh2002
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
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now