Re: Performance question: Varchar select versus Join

On 3 Apr 2002 20:14:55 GMT, fishbowl_at_famine.phx3.mindspring.net (james mcgill) wrote:

>Hello, I hope someone can help settle a debate I'm having
>currently regarding performance.
>
>To illustrate the question with an example, let there be two tables such
>as these:
>
>
>CREATE TABLE PREFIX (
> prefix VARCHAR2(80),
> prefix_id NUMBER(20) NOT NULL,
> CONSTRAINT pk_prefix_id PRIMARY KEY(prefix_id)
>);
>
>CREATE TABLE SUFFIX (
> suffix VARCHAR2(80),
> suffix_id NUMBER(20) NOT NULL,
> CONSTRAINT pk_suffix_id PRIMARY KEY(suffix_id)
>);
>
>
>Now, it seems to me that the following select should not
>really be a performance problem:
>
>SELECT PREFIX.prefix, SUFFIX.suffix FROM PREFIX, SUFFIX
> WHERE (SUFFIX.suffix = 'whatever' AND PREFIX.prefix_id = SUFFIX.suffix_id);
>
>
>The argument is that it is "obvious" that it will be far higher
>performance to denormalize the PREFIX table by adding a field
>for PREFIX+SUFFIX and query on it, like so:
>
>
>CREATE TABLE PREFIX (
> prefix VARCHAR2(80),
> aggregate VARCHAR2(160),
> prefix_id NUMBER(20) NOT NULL,
> CONSTRAINT pk_prefix_id PRIMARY KEY(prefix_id)
>);
>
>
>then to select like this:
>
>SELECT PREFIX.aggregate FROM PREFIX WHERE (PREFIX.aggregate = 'thiswhatever');
>
>Is it really so obvious that a single table character match on a longer
>varchar will significantly outperform a two-table join with a shorter
>varchar match and numeric primary keys? In our implementation, the
>tables will have 200,000 records each, and this query will be made very
>frequently.
>
>Thank you for any advice you have to offer.
>
>James
>Tempe AZ

Looks like there is a serious problem. You are referring to the suffix column as if it were a primary key. According to your table design it isn't, the surrogate suffix_id is your primary key. Hence if you didn't index the suffix column your query will end up in a full table scan.
The same comments apply to the denormalized scenario. And, oh yes: putting everything in one table will definitely outperform any multiple table scenario. You will have numerous update anomalies however, and your database will develop in a sinking ship very soonish. Whoever makes wants to denormalize is trading in a minimal performance gain if any, for a deluge of problems: he doesn't know anything about normalization and denormalization.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed Apr 03 2002 - 14:55:39 CST