Home » RDBMS Server » Performance Tuning » How get full data from table
How get full data from table [message #292965] Thu, 10 January 2008 05:34 Go to next message
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
Re: How get full data from table [message #292970 is a reply to message #292965] Thu, 10 January 2008 06:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: How get full data from table [message #293109 is a reply to message #293089] Thu, 10 January 2008 22:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
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 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Oracle should expand the OR condition out into a UNION internally (these days).
Re: How get full data from table [message #293534 is a reply to message #293514] Mon, 14 January 2008 00:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
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
Re: How get full data from table [message #293620 is a reply to message #293534] Mon, 14 January 2008 05:05 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Because an OR is more intuitive and less subject to maintenance error.
Previous Topic: sga_max_size: Oracle reserves all at ones?
Next Topic: Newbie: Query uses index and runs slow
Goto Forum:
  


Current Time: Wed Dec 07 04:44:12 CST 2016

Total time taken to generate the page: 0.08970 seconds