-
Query problem
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;
-
Re: Query problem
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
-
Re: Query problem
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:
Code:
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Re: Re: Query problem
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Grrr.
OK, how about a subquery factoring clause?
Code:
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?
-
Originally posted by slimdave
Grrr.
OK, how about a subquery factoring clause?
Code:
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 !
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Re: Re: Re: Query problem
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?
-
Re: Re: Re: Re: Query problem
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.
Code:
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;
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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 ...
Code:
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.
Code:
select * from(select (select DoCalculate(aLongitude,aLATITUDE,a.LONGITUDE,a.LATITUDE)
from dual) mileage_calcualted
from TABLEx
)
where mileage_calculated is not null
-
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.
Code:
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|