using constants

#11

Marten Lehmann wrote:
[snip]

Quote:

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.

Go for the intuitive part - your math is off.
First of all, it's not math, it's electronics: 2 registers
are being compared, and the outcome is what we would interpret
as 0 or 1. The actual state would be absence or presence of
electrical tension.
It does not matter if there are 4 bytes to be compared, or 8.

Besides, you would have these columns indexed (primary keys are!).
Check how indexes are built (dump them) and redo your math.

--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

#12

On 30.11.2007 15:18, Brian Tkatch wrote:

Quote:

On Thu, 29 Nov 2007 19:09:05 +0100, Robert Klemme
shortcutter (AT) googlemail (DOT) com> wrote:

On 29.11.2007 18:33, Brian Tkatch wrote:
On Thu, 29 Nov 2007 16:44:20 +0100, Marten Lehmann
lehmannmapson (AT) cnm (DOT) de> wrote:

Hello,

currently, we are using strings like "pending" or "finished" in some
tables to specify the state of certain jobs stored in these tables.
These values are self-explaining. These columns are indexed, of course.

But I guess, lookups of jobs with certain states would be much faster,
if the states would be stored is integer values, e.g. pending = 1,
finished = 2 and so on. However, these values wouldn't be
self-explaining any more.

As long as I'm working with Java, I could defined constants within a
class, so instead of integer values, I could use constants like
State.PENDING or State.FINISHED.

Is it possible to create such constants directly in Oracle, so I can use
them within SQL-queries like

select * from jobs where state = State.PENDING

?

Regards
Marten
If you do switch to numbers, a VIEW can be used that automatically
changes the numbers to words, either with a CASE expression, or by
joining to a TABLE that defiunes them. Then, the search could be
directly on the words.
You are sure this is faster? How would introducing one more layer of
indirection make this faster? Am I missing something?

I was addressing his actual question "Is it possible to create such
constants directly in Oracle...". I answered that a VIEW could do it.
Not that it would be faster, however.

But performance was his primary concern.

robert

#13

On Nov 30, 9:24 am, Brian Tkatch <N/A> wrote:

Quote:

On Thu, 29 Nov 2007 23:19:08 +0100, Marten Lehmann

lehmannmap... (AT) cnm (DOT) 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 --

#14

On Fri, 30 Nov 2007 22:41:38 +0100, Robert Klemme
<shortcutter (AT) googlemail (DOT) com> wrote:

Quote:

On 30.11.2007 15:18, Brian Tkatch wrote:
On Thu, 29 Nov 2007 19:09:05 +0100, Robert Klemme
shortcutter (AT) googlemail (DOT) com> wrote:

On 29.11.2007 18:33, Brian Tkatch wrote:
On Thu, 29 Nov 2007 16:44:20 +0100, Marten Lehmann
lehmannmapson (AT) cnm (DOT) de> wrote:

Hello,

currently, we are using strings like "pending" or "finished" in some
tables to specify the state of certain jobs stored in these tables.
These values are self-explaining. These columns are indexed, of course.

But I guess, lookups of jobs with certain states would be much faster,
if the states would be stored is integer values, e.g. pending = 1,
finished = 2 and so on. However, these values wouldn't be
self-explaining any more.

As long as I'm working with Java, I could defined constants within a
class, so instead of integer values, I could use constants like
State.PENDING or State.FINISHED.

Is it possible to create such constants directly in Oracle, so I can use
them within SQL-queries like

select * from jobs where state = State.PENDING

?

Regards
Marten
If you do switch to numbers, a VIEW can be used that automatically
changes the numbers to words, either with a CASE expression, or by
joining to a TABLE that defiunes them. Then, the search could be
directly on the words.
You are sure this is faster? How would introducing one more layer of
indirection make this faster? Am I missing something?

I was addressing his actual question "Is it possible to create such
constants directly in Oracle...". I answered that a VIEW could do it.
Not that it would be faster, however.

But performance was his primary concern.

robert

I thought listing the options was a good idea, instead of saying
nothing at all.

B.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

using constants

comp.databases.oracle.misc comp.databases.oracle.misc

Re: using constants - 11-30-2007 , 02:03 PM

Re: using constants - 11-30-2007 , 03:41 PM

Re: using constants - 12-01-2007 , 09:51 AM

Re: using constants - 12-03-2007 , 10:12 AM