Home » RDBMS Server » Performance Tuning » usuage of DECODE
usuage of DECODE [message #308738] Tue, 25 March 2008 05:44 Go to next message
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 Go to previous messageGo to next message
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 #308756 is a reply to message #308738] Tue, 25 March 2008 06:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no relation between the use of WHERE clause and DECODE function.
WHERE restricts the returned rows.
DECODE displays different result depending of values/expressions. It does not restrict anything.

Regards
Michel
Re: usuage of DECODE [message #308772 is a reply to message #308756] Tue, 25 March 2008 07:02 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 #308799 is a reply to message #308796] Tue, 25 March 2008 08:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"use DECODE in place of Where condition"
I understood "in place of" as "instead of", maybe it was "inside of"

Regards
Michel
Re: usuage of DECODE [message #308809 is a reply to message #308799] Tue, 25 March 2008 09:01 Go to previous message
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. Sad
Previous Topic: Performance of a query
Next Topic: altering rollback segment
Goto Forum:
  


Current Time: Thu Dec 08 22:33:18 CST 2016

Total time taken to generate the page: 0.06515 seconds