Re: Is this the sanctioned way to ascertain a table's existence?
From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Mon, 19 Oct 2009 07:56:05 +0200
Message-ID: <7k2dbmF387qncU1_at_mid.individual.net>
On 10/19/2009 02:58 AM, Palooka 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.
>>
Date: Mon, 19 Oct 2009 07:56:05 +0200
Message-ID: <7k2dbmF387qncU1_at_mid.individual.net>
On 10/19/2009 02:58 AM, Palooka wrote:
> 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.
I do not agree. Here's why: assuming you have an application that automatically deals with schema migration. If you follow the approach "use it and handle the case that the table is not there" requires to mix different types of code (schema treatment with core logic). IMHO that is a bad idea. For applications like this you want to do the existence check upfront (e.g. during application start) and not when the application is running. I agree to Mark: there are completely valid reasons to check for existence of database objects independent of usage and for that querying the data dictionary is the proper way to go. (If you are using JDBC the driver will typically do that for you anyway when using the meta data interface).
Kind regards
robert
-- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/Received on Mon Oct 19 2009 - 00:56:05 CDT