Re: using constants

On Thu, 29 Nov 2007 19:09:05 +0100, Robert Klemme <shortcutter_at_googlemail.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.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.

B.

>
>Cheers
>
> robert
Received on Fri Nov 30 2007 - 08:18:01 CST