RE: Table Naming Conventions

I'm confused here. Are you saying that 'date_column' will be converted to varchar2 or that it was taught that the column would be converted?

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_SLAC.Stanford.edu

-----Original Message-----
Sent: Tuesday, July 30, 2002 9:53 PM
To: Multiple recipients of list ORACLE-L

That's a classic one, taught in various courses ever since version 5. The most famous example was

select...
where date_column='12-31-99'

where date_column would be implicitly converted to varchar2. A little 'explain
plan' effort and all the confusion is easily avoided.

On 2002.07.31 00:08 Cary Millsap wrote:
> Rachel, one of the SQL statements in our Clinic that people find the
> hardest to optimize is one that has a thing that looks like "id_number
> =
> 10000" in the where clause. "id_number" is the table's primary key,
> yet
> the query spends 20 seconds executing a full-table scan. Any guesses?
>
> It's because "id_number" was actually defined as a varchar2 column.
> Oracle's implicit type coercion converts the predicate into
> "to_number(id_number) = 10000". Presto: the PK index is useless.
>
> This and dozens of other unnecessarily pathological problems await
> people who try to embed too much information into their names.
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
>
> Upcoming events:
> - NCOAUG Training Day, Aug 16 Chicago
> - Miracle Database Forum, Sep 20-22 Middlefart Denmark
> - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas
>
>
>
> -----Original Message-----
> Carmichael
> Sent: Tuesday, July 30, 2002 8:09 PM
> To: Multiple recipients of list ORACLE-L
>
> I can see your point, In the data warehouse we are building here, the
> modeler is planning on prefixing tables with the type of table (D_ for
> dimension tables, F_ for fact, etc)
>
> Hm, you mean we have to go back and revisit the naming standards that
> they developed? Can I please suffix the column names with an indicator
> of the datatype? :)
>
> The biggest problem is that most management wants "naming
> standards"...
>
>
> Rachel
>
>
> --- Cary Millsap <cary.millsap_at_hotsos.com> wrote:
> > I just think it's a waste. You can tell by context what kind of
> thing
> > a
> > thing is. For example, consider: "select a.flarg from bloing a where
> > a.croopoo > 7". This can be understood by syntactical context (even
> > with
> > the nonsense names), without having to rename "bloing" to
> > "bloing_table".
> >
> > Most of the embedding of type names into object names that I've seen
> > has
> > been implemented by users who were inexperienced at the time they
> > created the standard. They were worried that without embedding the
> > type
> > name into the object name, they might forget what kind of object it
> > was.
> > ...Most such naming conventions become onerous over time, long after
> > you
> > find out that you can find the type of something in the data
> > dictionary,
> > but after it's too late to save the thousands of extra characters of
> > typing that'll waste people's lifespans over time.
> >
> > In my old OFA paper, I made a joke about how we don't embed type
> > names
> > into object names in daily life, with just a few exceptions (Billy
> > the
> > Kid, Winnie the Pooh, Atilla the Hun, and the younger family members
> > of
> > the old Walton Family TV show are a few examples). If you have both
> a
> > dog and a child named "Rex," though, it's probably a good idea to
> > expect
> > them both to come when you call. With SQL, though, I can't think of
> a
> > case in which it's not easy to tell by syntactic context what kind
> of
> > thing you're talking about...
> >
> >
> > Cary Millsap
> > Hotsos Enterprises, Ltd.
> > http://www.hotsos.com
> >
> > Upcoming events:
> > - NCOAUG Training Day, Aug 16 Chicago
> > - Miracle Database Forum, Sep 20-22 Middlefart Denmark
> > - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12
> > Dallas
> >
> >
> >
> > -----Original Message-----
> > Carmichael
> > Sent: Tuesday, July 30, 2002 4:49 PM
> > To: Multiple recipients of list ORACLE-L
> >
> > Cary,
> >
> > you said
> > "* Don't embed the object type in the object's name. I used to see
> > this
> > all the time with tablespaces called XYZ_TS, indexes called
> > IND_THING,
> > and so on."
> >
> > what's your logic behind that?
> >
> > Rachel
> >
> >
> >
> > --- Cary Millsap <cary.millsap_at_hotsos.com> wrote:
> > > Here's a start. Not a checklist by any means, just kind of a micro
> > > pet
> > > peeves list.
> > >
> > > * Decide today whether table names will be singular or plural. Do
> > you
> > > want a THING (singular) table? Or a THINGS (plural) table?
> > >
> > > * Don't use case-sensitive names. E.g., use THING (without quotes)
> > in
> > > your CREATE (DDL) statement, which can be spelled "THING",
> "thing",
> > > "Thing", or even "tHiNG" in your developers' SQL. But don't make
> > > developers type stuff like this...
> > >
> > > select "Name" from "Thing" where "Id" = y /* won't work
> > without
> > > ""
> > > */
> > >
> > > * Don't embed the object type in the object's name. I used to see
> > > this
> > > all the time with tablespaces called XYZ_TS, indexes called
> > > IND_THING,
> > > and so on.
> > >
> > > * Decide today whether you want to abbreviate or not. If you do,
> > then
> > > construct a formal, standard, consistent list of accepted
> > > abbreviations.
> > > Don't name one table CUSTOMER_THING and another CUST_HISTORY.
> > >
> > >
> > > Cary Millsap
> > > Hotsos Enterprises, Ltd.
> > > http://www.hotsos.com
> > >
> > > Upcoming events:
> > > - NCOAUG Training Day, Aug 16 Chicago
> > > - Miracle Database Forum, Sep 20-22 Middlefart Denmark
> > > - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12
> > > Dallas
> > >
> > >
> > >
> > > -----Original Message-----
> > > Chambers
> > > Sent: Tuesday, July 30, 2002 3:37 PM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > > All...
> > >
> > > Will some of you please provide some insight on your table naming
> > > conventions? I'm in the very early planning stages of what will
> > > likely
> > > be a large and complex schema (IT asset inventory). I have a
> > chance
> > > to
> > > start it correctly. TIA
> > >
> > > Gary Chambers
> > >
> > > //-------------------------------------
> > > // Lucent Technologies GIO/Unix
> > > // 4 Robbins Road, Westford, MA 01886
> > > // 978-399-0481 / 888-480-6924 (Pager)
> > > // Nothing fancy and nothing Microsoft
> > > //-------------------------------------
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > --
> > > Author: Gary Chambers
> > > INET: gc22_at_lucent.com
> > >
> > > Fat City Network Services -- (858) 538-5051 FAX: (858)
> 538-5051
> > > San Diego, California -- Public Internet access / Mailing
> > > Lists
> > >
> > --------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and
> in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from). You
> may
> > > also send the HELP command for other information (like
> > subscribing).
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > --
> > > Author: Cary Millsap
> > > INET: cary.millsap_at_hotsos.com
> > >
> > > Fat City Network Services -- (858) 538-5051 FAX: (858)
> 538-5051
> > > San Diego, California -- Public Internet access / Mailing
> > > Lists
> > >
> > --------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and
> in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from). You
> may
> > > also send the HELP command for other information (like
> > subscribing).
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Yahoo! Health - Feel better, live better
> > http://health.yahoo.com
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Rachel Carmichael
> > INET: wisernet100_at_yahoo.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing
> > Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Cary Millsap
> > INET: cary.millsap_at_hotsos.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> >
> === message truncated ===
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Health - Feel better, live better
> http://health.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Rachel Carmichael
> INET: wisernet100_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Cary Millsap
> INET: cary.millsap_at_hotsos.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Mladen Gogala
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mladen Gogala
  INET: mgogala_at_adelphia.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: MacGregor, Ian A.
  INET: ian_at_SLAC.Stanford.EDU

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).