OraFAQ Forum: Performance Tuning » How get full data from table

Home » RDBMS Server » Performance Tuning » How get full data from table

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

How get full data from table [message #292965]

Thu, 10 January 2008 05:34

bornlooser
Messages: 20
Registered: September 2005
Location: Jakarta

Junior Member

Hi All,

Table fnd_user has column end_date that have some null and rest not-null values.

I m using this query in report and passing optional parameter for end_date. In case parameter is null, it shd show all rows from table, including rows where end_date is null.

select * from fnd_user where end_date = nvl(parameter,end_date);

Problem is that we can't format data of coulmn on left side of equal-to operator as we gets problem in getting this column based index in execution plan and query gets slow.

can't use like this...

select * from fnd_user where nvl(end_date,sysdate) = nvl(parameter,nvl(end_date,sysdate));

plz advise something else...
thanks a lot

Report message to a moderator

Re: How get full data from table [message #292970 is a reply to message #292965]

Thu, 10 January 2008 06:07

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

select * from fnd_user where end_date = parameter and parameter is not null
union all
select * from fnd_user where parameter is null

Regards
Michel

[Edit: add a missing letter]

[Updated on: Thu, 10 January 2008 22:50]

Report message to a moderator

Re: How get full data from table [message #293089 is a reply to message #292970]

Thu, 10 January 2008 20:25

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Am I missing something? What's wrong with:

select * 
from fnd_user 
where end_date = parameter 
or parameter is null

Ross Leishman

Report message to a moderator

Re: How get full data from table [message #293109 is a reply to message #293089]

Thu, 10 January 2008 22:48

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Am I missing something? What's wrong with:

Non optimal execution plan when parameter is not null (unless end_date is not indexed), I think.

Regards
Michel

[Updated on: Thu, 10 January 2008 22:49]

Report message to a moderator

Re: How get full data from table [message #293514 is a reply to message #293109]

Sun, 13 January 2008 20:39

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Oracle should expand the OR condition out into a UNION internally (these days).

Report message to a moderator

Re: How get full data from table [message #293534 is a reply to message #293514]

Mon, 14 January 2008 00:04

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Oracle should expand the OR condition out into a UNION internally

In this case why not express it directly in the query instead of relying on optimizer to rewrite it correctly?

Regards
Michel

Report message to a moderator

Re: How get full data from table [message #293620 is a reply to message #293534]

Mon, 14 January 2008 05:05

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Because an OR is more intuitive and less subject to maintenance error.

Report message to a moderator

Previous Topic:	sga_max_size: Oracle reserves all at ones?
Next Topic:	Newbie: Query uses index and runs slow

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jul 02 18:20:14 CDT 2024