How to get an empty resultset
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: How to get an empty resultset

  1. #1
    Join Date
    Sep 2001
    Location
    SWEDEN
    Posts
    70

    Post

    Hi,

    I have a 3-tier architecture using JDBC where i sometimes want an empty resultset from the tatabase in order to create a serializable RowSet datatype using the metadata.

    If I use an SQL statement like Select ... from X where PK='unreachable'
    the DB has to perform an indexed scan of the table, right?

    I got a tip that i might trick Oracle by saying Select ... from X where 1=2
    to make the question return immediately. However it seems like the DB has to perform a full table scan here, not grasping that the where clause is unreachable.

    So the question is : How to immediately get a resultset consisting of 0 rows with the metadata etc?

    Thanks in advance,
    Regards/
    Magnus

  2. #2
    Join Date
    Feb 2000
    Location
    Brisbane, Australia
    Posts
    46
    How about:
    Select ... from X where 1=2
    and rownum < 1




  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    select * from X where 1 = 0 always worked for me

    where rownum < 1 as well

    [Edited by pando on 10-18-2001 at 03:47 AM]

  4. #4
    Join Date
    Jul 2001
    Location
    Singapore(Asia)-WebCentre business company
    Posts
    456
    HI Mageri,

    Are u that Magnus Ericsson the one that i know back in Singapore with
    "STMD" previously??

    if u are wonder if u know who am i?

    rgds,
    ngwh,
    Singapore.

  5. #5
    Join Date
    Sep 2001
    Location
    SWEDEN
    Posts
    70
    Hi,

    Thanks for the tips, however it doesen't seem to help:

    To clarify: I get the desired result using where PK='unreachable' and EXPLAIN PLAN says that an INDEX RANGE SCAN is performed

    When I use where 1=0 and ROWNUM < 1, EXPLAIN PLAN says that a FULL TABLE SCAN is performed (which of course is associated with a cost...)

    What I want is for Oracle to immediatle shortcut and return the answer without having to scan the table at all. Is this possible?

    And sorry, I haven't been in Singapore.

    Thanks
    Regards/
    Magnus

  6. #6
    Join Date
    Feb 2000
    Location
    Brisbane, Australia
    Posts
    46
    Magnus,

    If you trace the session and run the select statements you will see that both the 1=0 and the rownum< 1 statements do not access any data blocks:


    select * from ......... where 1=0

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.02 0.04 0 0 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 1 0.00 0.00 0 0 0 0
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 3 0.02 0.04 0 0 0 0

    Misses in library cache during parse: 1
    Optimizer goal: CHOOSE
    Parsing user id: 33 (MCDD)

    Rows Row Source Operation
    ------- ---------------------------------------------------
    0 FILTER
    0 TABLE ACCESS FULL IMMAS_BASELINE_ACCOUNTS_TB


    Rows Execution Plan
    ------- ---------------------------------------------------
    0 SELECT STATEMENT GOAL: CHOOSE
    0 FILTER
    0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
    'IMMAS_BASELINE_ACCOUNTS_TB'


  7. #7
    Join Date
    Jul 2001
    Location
    Singapore(Asia)-WebCentre business company
    Posts
    456
    alright...i guess there must hv been many swedishs with "Magnus Ericsson" as their
    name
    ngwh,
    Singapore.

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    if you use rownum you almost always see full table scan but in practice Oracle is pretty clever, it doesnt actually do the FTS

    Also if you see statsitics you see consistent gets and db block gets and phy read are all 0

  9. #9
    Join Date
    Sep 2001
    Location
    SWEDEN
    Posts
    70
    Thanks for your help!

    When profiling after emtying the cache it was clear that no data was fetched when using where 1=0 opposed to using where PK='unreachable'
    Regards/
    Magnus

  10. #10
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Try

    select null from dual where 1=2;

    this is very small table

    if need X table then

    select * from X where ROWID='000000000000000';

    check number of 0 simbols.

    I'M SORRY THIS ISN'T WORK WITH ROWID.
    (only valid ROWID from table X)



    [Edited by Shestakov on 10-18-2001 at 10:44 AM]

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