efficiency in a query

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
sesh2002Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
rkogelheConnect With a Mentor Commented:
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
 
rkogelheCommented:
correction... ORDERED_PREDICATES is a hint, not a clause.
0
 
Mark GeerlingsDatabase AdministratorCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
kannanbCommented:
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
 
sesh2002Author Commented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
sesh2002Author Commented:
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
All Courses

From novice to tech pro — start learning today.