Re: using constants

On Nov 30, 9:24 am, Brian Tkatch <N/A> wrote:
> On Thu, 29 Nov 2007 23:19:08 +0100, Marten Lehmann
>
>
>
>
>
> <lehmannmap..._at_cnm.de> wrote:
> >Hello,
>
> >> To your first question, as others have said, it is not likely to make
> >> a difference. If the word defines a well known state, and the name
> >> will never change, it is just fine. A lookup table may be nice, but
> >> that depends on the way it is used.
>
> >would you say the same is true for joins? What would be faster,
>
> >this one?
>
> >domains
> >-------
> >name (pk)
> >...
>
> >records
> >-------
> >id (pk)
> >name (fk)
> >...
>
> >for a query like
>
> >select * from domains d, records r where d.name = r.name and d.name =
> >'whatever'
>
> >or this one:
>
> >domains
> >-------
> >id (pk)
> >name (unique)
> >...
>
> >records
> >-------
> >id (pk)
> >domain_id (fk)
> >...
>
> >for a query like
>
> >select * from domains d, records r where d.id = r.domain_id and d.name =
> >'whatever'
>
> >Mathematically it should be faster to compare two 4 byte non-zero
> >integer values than two at least 8 byte strings. But the first solution
> >is more elegant and intuitive.
>
> If you are asking what would be faster in a "real life" situation, as
> others have pointed out, it is unlikely to make a noticeable
> difference (that you would care about). If you are speaking
> academically, perhaps it is faster, but i simply do not know enough to
> answer that question.
>
> As for your queries, as i am sure you know, the first query does a
> redundant join, unless you are checking for existence of the record in
> the domain TABLE. Though, perhaps an EXISTS would be clearer and
> faster in that case.
>
> Personally, i like the second case better. Because names change (or
> split), ids do not. And anything that can change, should not be used
> as the PK.
>
> B.
>
>
> >Regards
> >Marten- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>

Martin, Oracle number datatypes are a form of scientific notation and require library operations to be performed on them. Thinking of them as integers is wrong.

Also going back to my mainframe assembler there was a binary compare operation. The time to compare 4 bytes of numeric or character data was exactly the same because to the computer it is just n bytes to compare.. The performance difference between integer and character generally comes about because when character keys are used the keys are generally longer in nature so the compare operations are longer. Though to use integer keys in your application will usually require extra join operations to get the keys and the presence of extra indexes in the physical implementation negating any access performance advantage gained from having integer keys to begin with.

A couple of years ago an Oracle support analyst performed a test of numeric verse character keys. According to the analyst he needed 100,000 rows to be able to identify a measurable difference in performance using a single column PK. So unless you are selecting 100,000 rows by PK in a single task there should be no performance reason to choose a numeric key over a character key. I did not save the link but if you have metalink support you could try searching for it. I think I posted on the thread so you could add my name to the search list to try to narrow the results down. There have been multiple threads on this topic on various Oracle boards over the years including this newsgroup so some of the archives might be of interest.

HTH -- Mark D Powell -- Received on Sat Dec 01 2007 - 09:51:45 CST