Re: using constants

On Fri, 30 Nov 2007 22:41:38 +0100, Robert Klemme <shortcutter_at_googlemail.com> wrote:

>On 30.11.2007 15:18, Brian Tkatch wrote:
>> 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.
>
>But performance was his primary concern.
>
> robert

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

B. Received on Mon Dec 03 2007 - 10:12:18 CST