Re: Is this the sanctioned way to ascertain a table's existence?
From: Palooka <nobody_at_nowhere.invalid>
Date: Mon, 19 Oct 2009 01:58:39 +0100
Message-ID: <3ROCm.9912$Az3.3869_at_newsfe19.ams2>
On 19/10/09 01:29, Mark D Powell wrote:
> On Oct 18, 5:49 pm, Serge Rielau<srie..._at_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.
Date: Mon, 19 Oct 2009 01:58:39 +0100
Message-ID: <3ROCm.9912$Az3.3869_at_newsfe19.ams2>
On 19/10/09 01:29, Mark D Powell wrote:
> On Oct 18, 5:49 pm, Serge Rielau<srie..._at_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.
Palooka Received on Sun Oct 18 2009 - 19:58:39 CDT