Query problem

[dba_admin]

I am trying to do the following query. Query1 will error out. Query2 works. Question: Does Query2 call the DoCalculate twice for every row processed? (One in the select and one in where clause.) If yes, how do I reduce the number of call to the function to 1?

Query1:

select DoCalculate(aLongitude,aLATITUDE,a.LONGITUDE,a.LATITUDE) mileage_calcualted
from TABLEx a
where mileage_calcualted is not NULL;


Query2:
select DoCalculate(aLongitude,aLATITUDE,a.LONGITUDE,a.LATITUDE) mileage_calcualted
from TABLEx a
where DoCalculate(aLongitude,aLATITUDE,a.LONGITUDE,a.LATITUDE) is not NULL;

[slimdave]

select * from
(
select DoCalculate(aLongitude,aLATITUDE,a.LONGITUDE,a.LATITUDE) mileage_calcualted
from TABLEx a
)
where mileage_calcualted is not null;

Better if you can to express the function behind DoCalculate in SQL

[jmodic]

Originally posted by dba_admin
Question: Does Query2 call the DoCalculate twice for every row processed? (One in the select and one in where clause.)

No, not for every row processed. But, yes, for every row returned.

For the rows where function DoCalculate() result in NULL, the function is executed only once (only in WHERE clause). Since in this case the result of the WHERE clause is "FALSE", Oracle doesn't bother to process that row further, so the DoCalculate() in selct column list is not executed. But for rows where DoCalculate() result in NOT NULL, function is executed twice - first time for the WHERE clause and the second time for column list.

If yes, how do I reduce the number of call to the function to 1?

I'm not sure if it can be done, at least not with pure SQL. First I thought that the following will help:

select mileage_calcualted from
(select
   DoCalculate(aLongitude,aLATITUDE,a.LONGITUDE,a.LATITUDE) mileage_calcualted
 from TABLEx a
)
where mileage_calcualted is not NULL;

however it turned out that it behaves exactly as the original query.

[jmodic]

Originally posted by slimdave
select * from
(
select DoCalculate(aLongitude,aLATITUDE,a.LONGITUDE,a.LATITUDE) mileage_calcualted
from TABLEx a
)
where mileage_calcualted is not null;

Better if you can to express the function behind DoCalculate in SQL

That was my initial thougt too, however it turned out that DoCalculate() is still called twice for any row returned! At least on 9.0.1.

[slimdave]

Grrr.

OK, how about a subquery factoring clause?

with sfc as
   (
   select
      DoCalculate(
         aLongitude,
         aLATITUDE,
         a.LONGITUDE,
         a.LATITUDE) mileage_calculated
   from
      TABLEx
   )
select
   mileage_calculated
from
   sfc
where
   mileage_calculated is not null;

'Owzat?

[jmodic]

Originally posted by slimdave
Grrr.

OK, how about a subquery factoring clause?

with sfc as
   (
   select
      DoCalculate(
         aLongitude,
         aLATITUDE,
         a.LONGITUDE,
         a.LATITUDE) mileage_calculated
   from
      TABLEx
   )
select
   mileage_calculated
from
   sfc
where
   mileage_calculated is not null;

'Owzat?

Gee! I didn't even know such a beast of a construct exists or is possible in Oracle (I wouldn't be surprised if I saw it in some Celco's article, though)! I see, it's realy time for me to dig into Oracle Manuals and start studying SQL again.

Anyway Slimdave, as much as I hate to dissapoint or irritate you (Grrr?) - it still calls the function twice for any row returned !

[dba_admin]

Originally posted by jmodic
That was my initial thougt too, however it turned out that DoCalculate() is still called twice for any row returned! At least on 9.0.1.

How did you find out, even with an inline view, it still run twice for any row returned?

[jmodic]

Originally posted by dba_admin
How did you find out, even with an inline view, it still run twice for any row returned?

Quite simply, I created a dummy function with DBMS_OUTPUT cals in it and observed how it behaves.

CREATE OR REPLACE FUNCTION foo (p NUMBER) RETURN VARCHAR2
IS
BEGIN
  dbms_output.put_line('EnterED function');
  IF p = 0 THEN
    dbms_output.put_line('Returned NULL');
    RETURN NULL;
  ELSE
    dbms_output.put_line('Returned NOT NULL');
    RETURN 'x';
  END IF; 
END;
/

SET SERVEROUTPUT ON

SELECT c1, xyz FROM
(SELECT c1, foo(c1) xyz FROM t1)
WHERE xyz IS NOT NULL;

[slimdave]

I am the master of obscure syntax

Doesn't work eh? Hmmm. It's supposed to create a temporary result, defined as sfc here, then query that. How odd.

In the case of the first attempt i made, it might be doing some kind of "optimization" by pushing the predicate into the inline view.

It's a real long shot, but maybe ...

select /*+ NO_PUSH_PRED(Tablex) */ * from
(
select DoCalculate(aLongitude,aLATITUDE,a.LONGITUDE,a.LATITUDE) mileage_calcualted
from TABLEx a
)
where mileage_calcualted is not null

Probably won't work because the hint is really to do with join predicates.

Here's another desperate attempt.

select * from(select (select DoCalculate(aLongitude,aLATITUDE,a.LONGITUDE,a.LATITUDE) 
from dual) mileage_calcualted
from TABLEx
)
where mileage_calculated is not null

[jmodic]

Originally posted by slimdave
It's a real long shot, but maybe ...
Probably won't work because the hint is really to do with join predicates.

Nope, I've tried to fool it with some of the hints too (like NO_MERGE), but it doesn't work.

Originally posted by slimdave
Here's another desperate attempt.

select * from(select (select DoCalculate(aLongitude,aLATITUDE,a.LONGITUDE,a.LATITUDE) 
from dual) mileage_calcualted
from TABLEx
)
where mileage_calculated is not null

Kudos!

Now that's what I call "master of obscure syntax", but hey, it works! It realy calls function only once per row processed.

Who cares if noone understands what the query is supposed to do, as long as it runs realy fast.