OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » usuage of DECODE

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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.

Report message to a moderator

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 #308756 is a reply to message #308738]

Tue, 25 March 2008 06:32

Michel Cadot
Messages: 68665
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

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?

Report message to a moderator

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;

Report message to a moderator

Re: usuage of DECODE [message #308792 is a reply to message #308780]

Tue, 25 March 2008 08:21

Michel Cadot
Messages: 68665
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

Report message to a moderator

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

Report message to a moderator

Re: usuage of DECODE [message #308799 is a reply to message #308796]

Tue, 25 March 2008 08:36

Michel Cadot
Messages: 68665
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

Report message to a moderator

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

Report message to a moderator

Previous Topic:	Performance of a query
Next Topic:	altering rollback segment

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 01 11:19:07 CDT 2024