I got the impression, he *is* checking metadata (more precise -
user_tables).

Sometimes my code needs to verify whether a table exists. Years ago, I
was about to post that particular questions here. I then realized
that the following statements achieve the desired results:

EXEC SQL SELECT COUNT(1) INTO :howMany FROM user_tables WHERE

Mark D Powell wrote:
There is an American expression that comes to mind in regards to not
using a select count(*) to verify existance just because some other
developer might copy the code at some future date: you can't fix
stupid.

I didn't want to use teh S-word.. But even in the case of know (even
modeled!) uniqueness COUNT will be slower that limiting the number of rows.
Let's first assume the DBMS is operating straight forward without any
fancy optimizations.
The attempt to read the second row costs CPU. There are various tricks
one can use to peek ahead and cut that codepath, but it's still CPU.
Also teh COUNT requires group by processing (also not free). So there is
a runtime cost to be paid for asking teh DBSM a question that has only a
loose correlation (# of tables) to the business need (existance of at
least one table).
Now let's assume that the DBMS is clever. It realizes that USER_TABLES
(in Oracle) implies a specific schemaname and tablename + schemaname are
unique. and it therefor can throw out the COUNT and replace it with
something niftier.
That at least costs extra compile time in the optimizer - aside from
making the code somewhat dependent on the version of the DBMS that is at
least that smart.
A simple tule holds: Ask the DBMS what you want to know. Don't ask for
more information than you need.

I do not think I would let what someone might do stop me from doing
what I need to do.  What I might do if Serge's concern came to mind is
code a database stored function that perrforms the table exists check
and returns 0 or 1 to indicate existance.

How about simply assuming the table exists and handle the non existence
as a PL/SQL exception.
Note that unless the SQL in question is dynamic the whole PL/SQL block
using the non existing table is likely in an invalid state to begin with.
Either way the compiler will holler if the table doesn't exist and that
check will be executed by the DBMS anyway.

Cheers
Serge

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

There is an American expression that comes to mind in regards to not
using a select count(*) to verify existance just because some other
developer might copy the code at some future date: you can't fix
stupid.
I didn't want to use teh S-word.. But even in the case of know (even

I do not think I would let what someone might do stop me from doing
what I need to do. What I might do if Serge's concern came to mind is
code a database stored function that perrforms the table exists check
and returns 0 or 1 to indicate existance.
How about simply assuming the table exists and handle the non existence

On Oct 18, 5:49 pm, Serge Rielau<srie... at (no spam) ca.ibm.com> wrote:
Mark D Powell wrote:
There is an American expression that comes to mind in regards to not
using a select count(*) to verify existance just because some other
developer might copy the code at some future date: you can't fix
stupid.

I didn't want to use teh S-word.. But even in the case of know (even
modeled!) uniqueness COUNT will be slower that limiting the number of rows.
Let's first assume the DBMS is operating straight forward without any
fancy optimizations.
The attempt to read the second row costs CPU. There are various tricks
one can use to peek ahead and cut that codepath, but it's still CPU.
Also teh COUNT requires group by processing (also not free). So there is
a runtime cost to be paid for asking teh DBSM a question that has only a
loose correlation (# of tables) to the business need (existance of at
least one table).
Now let's assume that the DBMS is clever. It realizes that USER_TABLES
(in Oracle) implies a specific schemaname and tablename + schemaname are
unique. and it therefor can throw out the COUNT and replace it with
something niftier.
That at least costs extra compile time in the optimizer - aside from
making the code somewhat dependent on the version of the DBMS that is at
least that smart.
A simple tule holds: Ask the DBMS what you want to know. Don't ask for
more information than you need.

I do not think I would let what someone might do stop me from doing
what I need to do. What I might do if Serge's concern came to mind is
code a database stored function that perrforms the table exists check
and returns 0 or 1 to indicate existance.

How about simply assuming the table exists and handle the non existence
as a PL/SQL exception.
Note that unless the SQL in question is dynamic the whole PL/SQL block
using the non existing table is likely in an invalid state to begin with.
Either way the compiler will holler if the table doesn't exist and that
check will be executed by the DBMS anyway.

Cheers
Serge

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

Serge, I do not understand yoru point. In this case the OP is reading
the rdbms dictionary view user_tables which has one and only one row
for every table the user owns to see if the table exists for the
user. The result will always be zero or one and it is an efficient
query.

The OP has stated why he wants to use the query. I would rather the
table always exist, but for what the OP wants to do this is a good way
to test existance.

Assuming that your PL/SQL has half decent error handling, Serge's way is

Maxim Demenko wrote,on my timestamp of 17/10/2009 11:24 PM:



I got the impression, he *is* checking metadata (more precise -
user_tables).

He is counting the rows of a table to check its existence, according to his own
words:

 >>> EXEC SQL SELECT COUNT(1) INTO :howMany FROM user_tables WHERE

I can't possibly understand how you got that impression...

On 19/10/09 01:29, Mark D Powell wrote:
On Oct 18, 5:49 pm, Serge Rielau<srie... at (no spam) ca.ibm.com> wrote:
Mark D Powell wrote:
There is an American expression that comes to mind in regards to not
using a select count(*) to verify existance just because some other
developer might copy the code at some future date: you can't fix
stupid.

I didn't want to use teh S-word.. But even in the case of know (even
modeled!) uniqueness COUNT will be slower that limiting the number of
rows.
Let's first assume the DBMS is operating straight forward without any
fancy optimizations.
The attempt to read the second row costs CPU. There are various tricks
one can use to peek ahead and cut that codepath, but it's still CPU.
Also teh COUNT requires group by processing (also not free). So there is
a runtime cost to be paid for asking teh DBSM a question that has only a
loose correlation (# of tables) to the business need (existance of at
least one table).
Now let's assume that the DBMS is clever. It realizes that USER_TABLES
(in Oracle) implies a specific schemaname and tablename + schemaname are
unique. and it therefor can throw out the COUNT and replace it with
something niftier.
That at least costs extra compile time in the optimizer - aside from
making the code somewhat dependent on the version of the DBMS that is at
least that smart.
A simple tule holds: Ask the DBMS what you want to know. Don't ask for
more information than you need.

I do not think I would let what someone might do stop me from doing
what I need to do. What I might do if Serge's concern came to mind is
code a database stored function that perrforms the table exists check
and returns 0 or 1 to indicate existance.

How about simply assuming the table exists and handle the non existence
as a PL/SQL exception.
Note that unless the SQL in question is dynamic the whole PL/SQL block
using the non existing table is likely in an invalid state to begin
with.
Either way the compiler will holler if the table doesn't exist and that
check will be executed by the DBMS anyway.

Cheers
Serge

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

Serge, I do not understand yoru point. In this case the OP is reading
the rdbms dictionary view user_tables which has one and only one row
for every table the user owns to see if the table exists for the
user. The result will always be zero or one and it is an efficient
query.

The OP has stated why he wants to use the query. I would rather the
table always exist, but for what the OP wants to do this is a good way
to test existance.

Assuming that your PL/SQL has half decent error handling, Serge's way is
much better IMHO. Anyway, as he he says, if the table does not exist/is
not visible, the block will not compile.

yes, he is counting the rows of one very specific table -
*user_tables* .
But i think, it is irrelevant as long as we agree on the subject - one
parse call should be more efficient than query the data dictionary.

Maxim Demenko wrote,on my timestamp of 19/10/2009 6:51 PM:



yes, he is counting the rows of one very specific table -
*user_tables* .
But i think, it is irrelevant as long as we agree on the subject - one
parse call should be more efficient than query the data dictionary.

Yes, absolutely.  My apologies, I misread the OP's question.

Internally, the command 'nrec' actually is a simple:

SELECT COUNT(1) from :someTable;

Assuming that your PL/SQL has half decent error handling, Serge's way is
much better IMHO. Anyway, as he he says, if the table does not exist/is
not visible, the block will not compile.