Query problem
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Query problem

  1. #1
    Join Date
    Mar 2001
    Posts
    286

    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;

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    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
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    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?

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    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?

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  7. #7
    Join Date
    Mar 2001
    Posts
    286

    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?

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    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?

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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
  •  



Click Here to Expand Forum to Full Width