Re: A Design Question

Vijay Mayadas wrote:
>
> Hi
>
> I am currently trying to decide how to design by tables within a database.
> The two choices I basically have are:
(snip....snip)

I suspect your single-table query would actually look like:

SELECT * from TABLE 1, TABLE 2, TABLE 3 ... WHERE
1.NAME = VAL_1
AND
2.NAME = VAL_2
...

as opposed to what you wrote, which was:

SELECT * from TABLE 1, TABLE 2, TABLE 3 ... WHERE
1.COL_A = VAL_1
AND
2.COL_B = VAL_2
...

AFAIK there is no reason not to reference the same table multiple times in a query -- it seems to me the real performance impact depends more on the size & structure of the table rather than the fact that a single table is being aliased multiple times. It's even possible you could see some benefit if the locality of reference in a given query is such that the desired blocks from the multiple table aliases happen to overlap in the db block cache. I think more depends on how big the table is & how it is indexed, and how large the temp tables created in response to the joins turn out to be. If the table has many rows then self-joining it like this could be a real problem & indexing into a single table once would almost certainly perform better (again, if poperly indexed...) I guess I'm wondering why you are concerned with adding columns to a table --as far as I know this owrks fine as long as you either have values to populate the new columns for all existing rows, or allow nulls in the new columns...
--

Vincent Ventrone    |  The MITRE Corp.
DBA, Dept. R101     |  M/S C020
vav_at_mitre.org       |  202 Burlington Rd.
(781) 271-7048      |  Bedford, MA 01730