Re: Is this the sanctioned way to ascertain a table's existence?

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Sun, 18 Oct 2009 17:29:24 -0700 (PDT)
Message-ID: <10b37648-95b1-4081-bdc4-9db0e053aa2e_at_m11g2000yqf.googlegroups.com>



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.

IMHO -- Mark D Powell -- Received on Sun Oct 18 2009 - 19:29:24 CDT

Original text of this message