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 [EMAIL PROTECTED] -----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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > > > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
