DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: query question

  1. #1
    Join Date
    Nov 2000
    Posts
    440

    query question

    I use a query like:

    select *
    from table_1
    where 0 != function
    and client_number = 10;

    i have like 16000 records in the table.
    i have like 25 records with client number 10.
    My function does some query and bring a result.
    The query in the function is very simple and return only 1 record.
    simple like select 1 from dual;

    If i tkprof my session, i see that my query in the function are executed like 8500 times.
    Why its not executing 25 times, since if i make
    select *
    from table_1
    where client_number = 10;

    it return only 25 records.

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Why is it doing that? Dunno, ask the optimizer.

    How can you stop it? Several ways.

    First, however, you should avoid such functions where possible and put the SELECT that the function does directly into the SQL statement. If you build your SQL dynamically, then this sub-select could still be centralized in a function. That function would simply return the SQL code in a string rather than executing it and returning a result - make sense?

    Anyway, force the optimizer's hand. Here's one way:

    SELECT * FROM (
    select T.*, ROWNUM RN
    from table_1 T
    where client_number = 10
    )
    WHERE
    0 != function


    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Another way that might work (because sub-selects are almost always done last as a filter operation):

    select *from table_1 T
    where client_number = 10 AND
    0 != ( SELECT function from dual )

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

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