Home » SQL & PL/SQL » SQL & PL/SQL » Problem with cost based optimizer?
Problem with cost based optimizer? [message #189087] Wed, 23 August 2006 02:57 Go to next message
GoGoRooney
Messages: 4
Registered: August 2006
Junior Member
Hello,

I have the following select :-

ALTER SESSION SET OPTIMIZER_MODE ='FIRST_ROWS_10';

select * from bov_viewname where (1=0) and pprocnr != 0;

The view has about 3 million rows and takes a very long time to execute even thought the 1=0 can never return a row. If I change the optimizer mode to default (all rows?) the query runs very fast - as expected.

Is this an error in the optimizer?

Thanks in advance
Richard
Re: Problem with cost based optimizer? [message #189096 is a reply to message #189087] Wed, 23 August 2006 03:38 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
That's pretty interesting, but "error" is a strong word when talking about the optimizer. To my knowlege, Oracle makes no guarantees about when constant predicates are performed, although I'll be stonkered if I can think of a good reason to perform them anytime other than first.

At a guess, I'd say it is a bug of sorts, but good luck bringing it to Oracle's attention. On past experience, it would take about 10 emails to get an SR like this past level 1 support, as you cannot demonstrate a genuine failure.

Ross Leishman
icon12.gif  Re: Problem with cost based optimizer? [message #189104 is a reply to message #189087] Wed, 23 August 2006 04:17 Go to previous messageGo to next message
GoGoRooney
Messages: 4
Registered: August 2006
Junior Member
of course I didn't mean to write "error" in optimizer - one just doesn't mention error and optimizer together, but thanks for the input Ross.... I hoped I wouldn't have to fight with oracle support on this one.
Re: Problem with cost based optimizer? [message #189113 is a reply to message #189104] Wed, 23 August 2006 04:41 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Just an idea: Did you really provide the literals 1 and 0 or did you use variables containing 1 and 0?
Re: Problem with cost based optimizer? [message #189126 is a reply to message #189087] Wed, 23 August 2006 05:08 Go to previous messageGo to next message
GoGoRooney
Messages: 4
Registered: August 2006
Junior Member
literals, no variables.
Re: Problem with cost based optimizer? [message #189208 is a reply to message #189087] Wed, 23 August 2006 11:09 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
try writing the query as the following and see what happens.

select * from bov_viewname where pprocnr != 0 and 1=0;
Re: Problem with cost based optimizer? [message #189562 is a reply to message #189208] Fri, 25 August 2006 02:06 Go to previous message
GoGoRooney
Messages: 4
Registered: August 2006
Junior Member
Hi Bill,

I Tried it out but it didn't change anything, still very slow - the pprocnr is part of an index but to honest I can always add indices and tune up the view however 1 = 0 can never return any rows - so why doesn't the optimizer realise that and return "no rows selected".

Another interesting point, if I select count(*) from... instead for "*" the query runs quickly - I thought it's a problem with one of the table attribute so I tried select 1 from ... but this ran slowly too.


Cheers
Richard
Previous Topic: merge invalid identifier
Next Topic: identify exception
Goto Forum:
  


Current Time: Tue Dec 06 12:02:55 CST 2016

Total time taken to generate the page: 0.09373 seconds