How to get an empty resultset

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,

How about:
Select ... from X where 1=2
and rownum < 1

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]

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,

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

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'

alright...i guess there must hv been many swedishs with "Magnus Ericsson" as their
name ;)

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

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'

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]