sesh2002
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
correction... ORDERED_PREDICATES is a hint, not a clause.
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.
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.
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
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
ASKER
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
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.
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.
ASKER
markgeer, here are the queries:
select a.ques_text, f.answer_text,f.answer_sco re 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
select a.ques_text, f.answer_text,f.answer_sco
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