wallacel -- Thanks for the question regarding "design the table", version 8.1.6

Followup July 4, 2002 - 3pm Central time zone:

if an entity has 100 attributes, it has 100 attributes -- it'll be a single table.  hopefully the 
average row (or max row actually) fits on a block -- but that is true whether you have 10, 100 or 
more columns

A full scan will take longer -- but you should be asking "what is the performance impact if we 
split them out".  Well, you'll have two (or N) primary key indexes to maintain, not just 1.  You'll 
have n*(m+1) additional IO's for every row retrieve (where n = number of tables you choose, m = 
height of your index).  It could really impact performance badly.

do no use longs, period.

when you use clobs, they are moved out of the table into their own segment when they exceed 4000 
bytes, the work is done for you, no need to factor them out special.

I size objects by small, medium, large and extra large, using a locally managed tablespace with 
uniform extents.  Alternatively, you can use a LMT with system manged extents and not even worry 
about it yourself. 

Followup July 4, 2002 - 5pm Central time zone:

You can certainly use composite keys if you choose. 

The smaller the key, the faster the compare -- however, you have to outweigh the costs of using a 
surrogate key and maintaining two unique indexes if you decide to 'shorten' the key by using a 
single ID field populated via a sequence for example. (there are tradeoffs).

I tend to go with natural keys if the natural key is 1 to 3 columns (and NEVER changes), surrogate 
keys (id field populated via sequence) otherwise. 

Followup July 5, 2002 - 10am Central time zone:

As I said before:

...
when you use clobs, they are moved out of the table into their own segment when 
they exceed 4000 bytes, the work is done for you, no need to factor them out 
special
.......


It doesn't matter that lobs are big, they are not stored in the table segment.

If someone is updating the LOB, yes the row will be locked -- but that is the way it SHOULD BE.  

I believe this is one table, with one row per thing.  LOBS do not take an inordinate amount of time 
to modify -- the row is protected from other updates, but reads of the data are not blocked.  I do 
not view this as an issue.

I would not introduce the complexity you describe, no.
 

Followup July 5, 2002 - 11am Central time zone:

NO, you are missing the point of how lobs are stored.

they are stored in 3 pieces

o in the table, there is a lob locator (pointer)
o in the same tablespace as the table, there is a lob index (more pointers to all of the chunks)

o in another segment which may be in another tablespace (or not, upto you), are the lob chunks.

When you create a table with a LOB, there are 3 segments (objects) created -- the table itself, the 
lob index (which will be in the same tablespace) and the lob segment (which you control)


We already "broke" all of this stuff out physically.  There is NEVER a need for two tables here.  

Look at the create table statement for a lob as shown by exp/imp:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x int, y clob );
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> !exp userid=/ tables=t
Export: Release 8.1.7.3.0 - Production on Fri Jul 5 11:12:50 2002
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table                              T          0 rows exported
Export terminated successfully without warnings.

ops$tkyte@ORA817DEV.US.ORACLE.COM> !imp userid=/ full=y indexfile=t.sql
Import: Release 8.1.7.3.0 - Production on Fri Jul 5 11:13:02 2002
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production
Export file created by EXPORT:V08.01.07 via conventional path
import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
. . skipping table "T"                                

Import terminated successfully without warnings.

ops$tkyte@ORA817DEV.US.ORACLE.COM> !cat t.sql

REM  CREATE TABLE "OPS$TKYTE"."T" ("X" NUMBER(*,0), "Y" CLOB) PCTFREE 10 
REM  PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING STORAGE(INITIAL 524288 
REM  FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "USERS" LOB ("Y") STORE AS 
REM  (TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 
REM  NOCACHE NOLOGGING STORAGE(INITIAL 524288 FREELISTS 1 FREELIST GROUPS 
REM  1)) ;
REM  ... 0 rows


You already have all of the control you need over the location of the lob, one table -- period. 

Followup July 6, 2002 - 1pm Central time zone:

#1) telepathy I suppose?  

#2) I would get database people and teach them Java/JSP.  Or I would get database people -- let 
them do the database logic in finely tuned stored procedures and have the java/jsp guys just call 
the plsql code (NO SQL in java)...  (of course, the java jsp programmers will forget to use bind 
variables and refuse to use prepared/callable statements more then once but hey -- at least the sql 
would run good)

If you are setting out to write a high performance database application, write a database 
application.  Don't write a "cool java/jsp" that happens to use a database -- the database is going 
to be where you succeed or fail, don't work AROUND it, with with it. 

Followup July 8, 2002 - 8am Central time zone:

don't access the whole object then -- don't be lazy, don't code "select *" (which in my humble 
opinion is a BUG in your code if you do!  The fastest way to a broken program is to code:

select *    <<<<==== use of "*" should be limited to sqlplus testing

insert into t values (   <<<==== didn't list the columns
insert into t select ...   <<<<===== ditto


a simple drop and re-create with the columns in a different order or the addition or deletion of a 
column in the table BREAKS your application)


SELECT only that data which you actually NEED.  Don't use "*" and this entire conversation is 
rendered MOOT.
 

Followup July 8, 2002 - 7pm Central time zone:

It isn't a stupid question -- it is actually more complex then you probably anticipate and takes a 
couple of points to fully answer

o first, there is the "BUG" inherit in the "select *" code. A simple addition or deletion of a 
column will generally "break" an application. One of the glaring exceptions to this is PLSQL with 
implicit cursors.  

   for x in (select * from t)

will not break but is unnecessarily inefficient.  

o second, there are different paths the optimizer can take to answer a query and it is very 
dependent on the columns you select.  For example:

big_table@ORA817DEV.US.ORACLE.COM> select object_name from big_table where rownum < 100000;

Execution Plan
----------------------------------------------------------
   0  SELECT STATEMENT Optimizer=CHOOSE (Cost=212 Card=1459904 Bytes=36497600)
   1    0  COUNT (STOPKEY)
   2    1    INDEX (FAST FULL SCAN) OF 'OBJECT_NAME_IDX' (NON-UNIQUE) (Cost=212 C
ard=1459904 Bytes=36497600)

Statistics
----------------------------------------------------------
          0  recursive calls
         45  db block gets
        583  consistent gets
          0  physical reads
          0  redo size
    3228183  bytes sent via SQL*Net to client
       2534  bytes received via SQL*Net from client
         21  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      99999  rows processed

big_table@ORA817DEV.US.ORACLE.COM> select * from big_table where rownum < 100000;

Execution Plan
----------------------------------------------------------
   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=577 Card=1459904 Bytes=150370112)
   1    0  COUNT (STOPKEY)
   2    1    TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=577 Card=1459904 Bytes=1503
70112)

Statistics
----------------------------------------------------------
          0  recursive calls
         18  db block gets
       1397  consistent gets
          0  physical reads
          0  redo size
   11972872  bytes sent via SQL*Net to client
       2534  bytes received via SQL*Net from client
         21  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      99999  rows processed

big_table@ORA817DEV.US.ORACLE.COM> 


Look at the huge differences in consistent gets (blocks processed).  This is due entirely to the 
one query being answered via an index and the other full scanning.  If you include columns in your 
select list that FORCE us to goto the base table -- when you don't need to -- you are forcing a 
less optimal query plan on us

o third, look at the difference in data transfered!.  Almost 4x as much -- that could be huge.

o and the last thing I'll mention will be chained rows...  The impact from this is subtle.  If 
you have chained rows -- and you do a select *, we'll be forced to construct the entire row (which 
will take additional random IO's to find the "end" block for your row - we have to piece it 
together).  Now, if you select only the columns you need AND these columns are on the head 
rowpiece, we'll never assemble the entire row -- we do just what we need to do to get your data.


So, yes, Oracle reads an entire block -- but by selecting only the columns you need you can:

o avoid reading that block in the first place (skipping the table access by rowid step in an index 
range scan for example)

o avoid reading multiple blocks to construct the entire row

o open up better query plans, more query plans

o have less buggy code -- code that is much more resilient to change

o transmit lots less data over the network

and so on.... 

Followup July 9, 2002 - 12pm Central time zone:

We read a block -- if you have 20 columns or 100 columns we read the block.

If you only need 20 of them, and we need to read the block, we'll still read the block.  We'll only 
process 20 of them.

The select * bug is that if you have a program that does a select *, the * is resolved at runtime.  
If the number, order or types of the columns CHANGE over time -- your program breaks.  If you 
selected ONLY those columns of interest -- only those changes that really truly affect you -- in 
fact affect you.  We can ADD columns and you do not break.  If we drop a column and you don't 
select that column -- you don't break -- if you did a SELECT * however, you would have in your 
FETCH command a place for this missing column and you would break.   

Followup July 12, 2002 - 8am Central time zone:

If P has a 1-1 relationship with 3 of the C* tables and either a 1-1 or 1-m (one to many) with 1 of 
the C* tables, you can write it as:

select * 
  from p, c1, c2, c3, c4
 where p.key = c1.fkey 
   and p.key = c2.fkey
   and p.key = c3.fkey
   and p.key = c4.fkey

if any of the relationships are OPTIONAL, add the outer join (+) to the query.

If more then 1 of the child tables is 1-m with P, then you cannot do it with a join (since if C1 
has two rows that match with P and C2 has 3 rows -- you'll end up with 6 rows -- a cartesian 
production).  You'll want to use a UNION ALL -- better then UNION

 

Followup January 10, 2003 - 10am Central time zone:

No version info.  Ok, assuming you are using the latest software:


option four
create table alert_record( alert_code int, alert_date date, alert_info XMLTYPE )



or, in earlier releases

option four
create table alert_record( alert_code int, alert_date date, alert_info clob )


if you don't need to have the data structured.  You can use Oracle text (intermedia) to index the 
xmltype or clob for searching if that is needed. 

Followup February 4, 2003 - 4pm Central time zone:

original question was:

...
  I have a system where we need to generate alerts.  The alert will be stored 
in an alert table, and end user will see them based on queries.  For each alert, 
we need some basic information, such as alert code, and create date.  We also 
need to store the detailed information for the event that causes the alert, and 
that detailed information varies depending on the alert.  In another words, 
different kinds of detailed information need to be captured for different alert. 
 I am now considering three options, but  not sure what is the best way to 
handle this.


1. Create one ALERT_RECORD table, and create columns that will be needed to 
capture different types of alert detail information. ...

2. Create one ALERT_RECORD table for each alert type....

3. Create one ALERT_RECORD table, (then a table with variable name/value pairs).....


#1 is the easiest

#2 can be thought of as a variation on #1 -- think "views"

#3 would be avoided by me if at all possible.  People are going to want to query "where product_id 
= 55 and acknowledge date between A and B or packing_list = C".  Try doing that with variable 
name/values


So, I guess I vote for #1 with the #2 variation on a theme (views) to remove the ambiguity of who 
needs what data...


 

Followup May 30, 2003 - 12pm Central time zone:

you'd have to ask the people wanting the separate tablespace "why".

the goal is to achieve even IO over all devices.  If segrating this way helps that, that is good.


That nologging is a bit dangerous no?  do you not care about these proteins?  (there is a word I 
never thought I would use myself in a sentence here ;) 

Followup July 22, 2003 - 7am Central time zone:

depends -- do you know for 100% sure how many lanaguages there will be.

surveys are an example where the "funky data model" as I call it -- where you have this very 
generic model -- might make sense.  


It allows very flexible storage -- it stinks for querying - you might have to denormalize for 
quering later -- so you would have columns spanish, english, french (not lang1, lang2, lang3).  so 
you can query "select count(*) from t where survey_id = 55 and english = 1 and spanish = 1" to find 
all surveys filled out by people that speak spanish and english. 

Followup July 22, 2003 - 8pm Central time zone:

1) do it in the funky data model and then DENORMALIZE if possible for reporting.

2) a table per lookup type. 

Followup August 11, 2003 - 2pm Central time zone:

if they all store the same set of information -- then your "type" column is nothing more then yet 
another attribute -- no more or less relevant then col1, col2, col3


so, unless there is more then meets the eye here -- one table. 

Followup October 14, 2003 - 7am Central time zone:

use a real global temporary table

no concurrency issues whatsoever

read about them in the sql reference guide "create global temporary ...." 

Followup October 22, 2003 - 6am Central time zone:

1) i wrote about this in my new book "Effective Oracle By Design".  Some of the things I said about 
such nonsense was:

...
In addition to using the proper base datatype such as number, date, or string, you should also use 
the most specific type you can. For example, use VARCHAR2(30) for a field that is up to 30 
characters in length; do not use VARCHAR2(4000). 

Ask Tom

"I work with a modelers group. My modeler would like to define every VARCHAR2 field with the 
maximum length, which means that a table with 20 VARCHAR2 fields will all be defined with a maximum 
of 2000 or 4000 bytes. I tried to talk to him about the reasons we identify data with correct 
lengths and names in order to understand what we have in our database. He told me that it doesn't 
matter, since Oracle just stores the length, etc., and there is no overhead. I don't believe this 
is true, but have been jumping between so many databases that I cannot find a document on the 
internals of Oracle storage. Can you help me out here with this question?"

My gut response was, "This is your data modeler, my goodness!" They are the ones who are supposed 
to be telling you that it is vital to use the appropriate length when defining fields! That is 
their job. Let's just forget about things like storage for a minute, why don't we ask him:

. What is going to happen when users pull this up in a query tool that formats each field based on 
the width of the column in the database? They'll see one column and need to scroll way over to see 
the second, the third, and so on.

. Say the code prepares a query that selects ten columns that are VARCHAR2. The developers, for 
performance, would like to array fetch (very important). They would like to array fetch say 100 
rows (very typical). So, you have 4,000 ´ 10 ´ 100 = almost 4MB of RAM the developers must 
allocate! Now, consider if that were ten VARCHAR2(80) fields (it's probably much smaller than 
that). That's about 78KB. Ask the data modeler how much RAM he is willing to kick in for this 
system.

. Now, the developers start to build a data-entry screen to put data into the database. Wow, that 
code field can be 4,000 characters long and that first name can be 4,000 characters long. How the 
heck is anyone going to know what sort of data can really go in there?

Tell your data modeler group members that they need to consider the length as a constraint. Just as 
they use primary and foreign keys, they should use the proper and correct length on fields. You can 
always expand a field via a command like alter table t modify c varchar2(bigger_number). There is 
no reason to use the maximum length everywhere. It will hurt the applications you develop, because 
they will mistakenly allocate many megabytes of RAM. Just think of the array fetch example with an 
application server. Now, it's not just 4MB; it's 4MB ´ number of connections. You are talking some 
real memory here for a single query, and you'll be doing a lot of them at the same time.

End Ask Tom

A CHAR(2000) will consume 2,000 bytes of storage whether you put in the letter a, the string `hello 
world', or 2,000 characters. A CHAR is always blank-padded. Additionally, are you thinking about 
using an occasional index in your system? If so, beware of storage-related problems. Consider the 
following:

tkyte@ORA817.US.ORACLE.COM> create table t ( a varchar2(4000), b varchar2(4000));
Table created.

tkyte@ORA817.US.ORACLE.COM> create index t_idx on t(a);
create index t_idx on t(a)
                      *
ERROR at line 1:
ORA-01450: maximum key length (3218) exceeded
I've noticed that 9i seems to have magically improved upon this CM
NOTE: In Oracle9i, the maximum key length is larger - but the restriction still exists.  For 
example, an index on T(a,b) would raise: ORA-01450: maximum key length (6398) exceeded in Oracle9i. 


My system has an 8KB block size. I would need to use at least a 16KB block size to index a single 
column, but even then, if I tried to create a concatenated index on T(A,B), it would fail there! 

The same holds true for your numbers and the new Oracle9i TIMESTAMP datatypes: When appropriate, 
use scales and precisions on those fields in order to better define your data integrity and to give 
applications that much more information about the data itself.

In short, never be tempted to use anything other than a DATE or TIMESTAMP datatype to hold a date 
value, and never be tempted to use a VARCHAR2 to hold a number. Use the appropriate and correct 
type for each to ensure maximum performance, and more important, to protect your data integrity.


2) it won't affect raw performance, the costs of doing such a thing are found elsewhere. 

Followup October 22, 2003 - 7am Central time zone:

put the most frequently accessed columns "first"

put the columns most likely to be null "last"

but most importantly -- don't over analyze it.  it won't make a tremendous amount of difference at 
the end of the day.

I just always put the primary keys first (convention) and the follow with the columns as they "make 
sense".  when I think of a person for example:

person( unique_id primary key,
        first_name,
        last_name,
        middle_initial,
        address,
        .....

the data just follows in the "natural order" i see it in my head.   

Followup October 22, 2003 - 12pm Central time zone:

confusing but not a big deal 

Followup October 23, 2003 - 8am Central time zone:

they will not be stored any differently, no.

the effects are all in the client, in the loss of data integrity, in the loss of meaning, in the 
sloppiness of the implementation. 

Followup October 23, 2003 - 1pm Central time zone:

i remember once working as a coder -- doing some Ada programming.

the "senior reviewers" wanted me to code all subroutines in alphabetical order, that last comment 
reminded me of that.  I hated that!  (i made them really made by using "renames" in ada to give the 
appearance of compliance without really reordering any code -- i just added AA_ AB_ AC_ to the 
front of each routine and then renamed AA_proc1 to proc1 :) 

Followup October 23, 2003 - 1pm Central time zone:

just the ones that are bigger then 4000 

Followup October 23, 2003 - 1pm Central time zone:

they are not numbers as you said.  zip codes in particular are "postal codes" in general and are 
alpha numeric in reality.

so, since they are not numbers, store in a string -- you'll never treat them as numbers so it's ok. 

Followup October 31, 2003 - 7am Central time zone:

ops$tkyte@ORA920LAP> select rpad('9',27,'9')/60/60/24/366/1000 from dual;

RPAD('9',27,'9')/60/60/24/366/1000
----------------------------------
                        3.1623E+16


27 9's is a big big number.  that shows it would take 3.16e16 YEARS to exhaust the sequence 
generating 1000 numbers a second


the number of digits is not going to have much bearing on anything.  it'll be just fine to use a 
single number column as big as it gets.

You should not have a single sequence.  see

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:2985886242221

Followup October 31, 2003 - 8am Central time zone:

you know what, i didn't read the entire thing.  thanks -- i'll finish my answer now :) 

Followup November 3, 2003 - 8am Central time zone:

well, table API's to me are somewhat weak in the first place -- or just have special use cases 
(simple CRUD