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
Received on Fri Nov 30 2007 - 15:41:38 CST
