Home » RDBMS Server » Performance Tuning » usuage of DECODE
| usuage of DECODE [message #308738] |
Tue, 25 March 2008 05:44  |
vkrn
Messages: 18
Registered: March 2008
|
Junior Member |
|
|
Hi all,
I have a conditon in my where clause which selects person name based on the criteria specified.If i use DECODE in place of Where condition will that effect my performance.
Thanks.
kiran.
|
|
|
|
| Re: usuage of DECODE [message #308742 is a reply to message #308738] |
Tue, 25 March 2008 05:50  |
coleing
Messages: 213
Registered: February 2008
|
Senior Member |
|
|
Depends.
you need to try it and see.
Keep in mind.
How many rows will the decode statement filter out. If not many, impact may be minimal. If most of the table, then oracle may join your objects in the wrong order (it tries to join smallest after filtering first).
Will it join to different objects depending on the decode statement? This will mean that oracle cannot know the true execution path and can only estimate it (badly sometimes).
PS: This question is probably in the wrong forum.
[Updated on: Tue, 25 March 2008 05:51] Report message to a moderator |
|
|
|
|
|
| Re: usuage of DECODE [message #308772 is a reply to message #308756] |
Tue, 25 March 2008 07:02 |
vkrn
Messages: 18
Registered: March 2008
|
Junior Member |
|
|
suppose i have a condition like this
select sum(p.price) from product p where p.supplier_id=2
union all select sum(p.price) from product p where p.supplier_id=3;
here product is the child for Supplier and p.supplier_id is the foriegn key for product table.
can i rewrte the query like this
select sum(decode(p.supplier_id,2,p.price) pr1,sum(decode(p.supplier_id,3,p.price) pr2 from product p ;
will it be performance efficent?
|
|
|
|
| Re: usuage of DECODE [message #308780 is a reply to message #308738] |
Tue, 25 March 2008 07:29 |
coleing
Messages: 213
Registered: February 2008
|
Senior Member |
|
|
"There is no relation between the use of WHERE clause and DECODE function."
Consider this:-
select *
from table_a a, table_b b, table_c c
where decode(a.field1,1,b.field1, 2, b.field2, 3, c.field1, c.field2);
Depending on the values of table_a.field1, the query will join to different columns and even different tables.
The optimiser cannot optimally choose a plan for this.
And This:-
select *
from table_a a, table_b b, table_c c
where a.field1 = b.field1
and a.field1 = c.field1
and decode(a.field2,1,'ALL_ROWS','SOME_ROWS') = b.field2;
This will restrict rows separate frm the join depending on the decoded value of a.field2.
[Updated on: Tue, 25 March 2008 07:30] Report message to a moderator |
|
|
|
| Re: usuage of DECODE [message #308781 is a reply to message #308738] |
Tue, 25 March 2008 07:32 |
coleing
Messages: 213
Registered: February 2008
|
Senior Member |
|
|
select sum(p.price) from product p where p.supplier_id=2
union all select sum(p.price) from product p where p.supplier_id=3;
Can be rewritten:-
select p.supplier_id, sum(p.price)
from product p
where p.supplier_id in (2,3)
group by p.supplier_id;
|
|
|
|
| Re: usuage of DECODE [message #308792 is a reply to message #308780] |
Tue, 25 March 2008 08:21 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
| Quote: | "There is no relation between the use of WHERE clause and DECODE function."
Consider this:-
select *
from table_a a, table_b b, table_c c
where decode(a.field1,1,b.field1, 2, b.field2, 3, c.field1, c.field2);
|
This is not a valid statement.
And you don't replace WHERE by DECODE, you use DECODE in WHERE.
Regards
Michel
|
|
|
|
| Re: usuage of DECODE [message #308796 is a reply to message #308738] |
Tue, 25 March 2008 08:28 |
coleing
Messages: 213
Registered: February 2008
|
Senior Member |
|
|
yes but you understood what I meant. the statement is not quite valid, but as soon as you join it to something, the point is made.
select *
from table_a a, table_b b, table_c c
where decode(a.field1,1,b.field1, 2, b.field2, 3, c.field1, c.field2) = decode(b.field2,1,a.field2, b.field3);
Ahh, you are thinking he meant that he would actually not have a WHERE clause.
The question doesnt make sense unless you assume that he meant IN instead of INSTEAD of the where clause.
Either that or you would have to assume he meant putting decode in the select list (which is what he really meant).
|
|
|
|
|
|
| Re: usuage of DECODE [message #308809 is a reply to message #308799] |
Tue, 25 March 2008 09:01 |
vkrn
Messages: 18
Registered: March 2008
|
Junior Member |
|
|
Consider this:-
select *
from table_a a, table_b b, table_c c
where decode(a.field1,1,b.field1, 2, b.field2, 3, c.field1, c.field2);
can u explain the condition given in the decode .i am not gettting it. 
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 02:49:01 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
