Re: Is this the sanctioned way to ascertain a table's existence?
From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Sun, 18 Oct 2009 23:49:10 +0200
Message-ID: <7k1gqnF3423nlU1_at_mid.individual.net>
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.
Date: Sun, 18 Oct 2009 23:49:10 +0200
Message-ID: <7k1gqnF3423nlU1_at_mid.individual.net>
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 LabReceived on Sun Oct 18 2009 - 16:49:10 CDT