Re: using constants

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Thu, 29 Nov 2007 19:09:05 +0100
Message-ID: <5r8di2F13begrU3@mid.individual.net>

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?

Cheers

        robert Received on Thu Nov 29 2007 - 12:09:05 CST