Re: Performance question: Varchar select versus Join

In article <npqmaus2hngbk9rdo0hg9i5fh5o6gbeau1_at_4ax.com>, Sybrand Bakker <postbus_at_sybrandb.demon-verwijderdit.nl> wrote:

>Looks like there is a serious problem. You are referring to the suffix
>column as if it were a primary key.

I'm afraid I clouded the issue by trying to put together an example.

>Hence if you didn't index the suffix column your query will end up in
>a full table scan.

I see. I think my example falls short of representing the actual case; apologies.

>The same comments apply to the denormalized scenario.
>And, oh yes: putting everything in one table will definitely
>outperform any multiple table scenario.

Always? Do character matches on longer varchar fields become proportionally more expensive, or are these matches constant somehow?

>You will have numerous update
>anomalies however, and your database will develop in a sinking ship
>very soonish.

That's why I'm trying to find ways to either stop this decision, or else understand that it is correct.

>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.

I wish I could go to them and say exactly that; unfortunately there is a good chance that I am wrong and they are right. Trying to get the facts straight. If this one item amounts to a measurable performance gain, I'm all for it as well. But if there is Oracle magic that would let us have our cake and eat it too, obviously that would be better.

I'm working on a better example that comes from our actual schema. Apologies for the quick/dirty approach I took initially.

Thank you, sincerely, for your help.

James
Tempe AZ

--