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

Comments embedded.
On Nov 29, 9:44 am, Marten Lehmann <lehmannmap... (AT) cnm (DOT) de> wrote:
Why would you think that? The values are indexed, and it doesn't
matter to Oracle, really, whether those data values are varchar2 or
number, they all point back to rowids. I can't understand why you'd
expect indexing numbers to be faster than indexing varchar2s.

So why even think of using them? Possibly this is a holdover from SQL
Server/Sybase experiences with enumerated data?

Again, using the enumerated data type (which Oracle doesn't have).

Certainly it's possible, by creating two additional columns in your
table named, oddly enough, PENDING and FINISHED and populating them
via a trigger based upon your existing state strings:

create or replace trigger job_enum_state
before insert or update on jobs
for each row
begin
if :new.state = 'pending' then
:new.pending := 1;
:new.finished := 0;
elsif :new.state = 'finished' then
:new.pending := 0;
:new.finished := 1;
elsif ld.state = 'pending' then
:new.pending := 1;
:new.finished := 0;
elsif ld.state = 'finished' then
:new.pending := 0;
:new.finished := 1;
end if;
end;
/

SQL> create table jobs (jobid number, state varchar2(12), pending
number, finished number);

Table created.

SQL> create or replace trigger job_enum_state
2 before insert or update on jobs
3 for each row
4 begin
5 if :new.state = 'pending' then
6 :new.pending := 1;
7 :new.finished := 0;
8 elsif :new.state = 'finished' then
9 :new.pending := 0;
10 :new.finished := 1;
11 elsif ld.state = 'pending' then
12 :new.pending := 1;
13 :new.finished := 0;
14 elsif ld.state = 'finished' then
15 :new.pending := 0;
16 :new.finished := 1;
17 end if;
18 end;
19 /

Trigger created.

SQL> insert into jobs (jobid, state) values (1, 'pending');

1 row created.

SQL> insert into jobs (jobid, state) values (2, 'finished');

1 row created.

SQL> select * from jobs;

JOBID STATE PENDING FINISHED
---------- ------------ ---------- ----------
1 pending 1 0
2 finished 0 1

SQL> update jobs set state = 'finished' where jobid = 1;

1 row updated.

SQL> select * from jobs;

JOBID STATE PENDING FINISHED
---------- ------------ ---------- ----------
1 finished 0 1
2 finished 0 1

SQL>


Others may offer more elegant solutions.


David Fitzjarrell

Marten Lehmann wrote:
The chance of you having enough activity on these tables to be able to
measure the difference between your strings and an integer is remarkably
close to zero.

But, as with all things, test and see. My guess is any difference you
find will be measured in milliseconds.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Thu, 29 Nov 2007 16:44:20 +0100, Marten Lehmann
<lehmannmapson (AT) cnm (DOT) de> wrote:

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.

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.

B.

On 29.11.2007 17:15, DA Morgan wrote:

LOL - The wording is just gorgeous. :-)

Thanks for making my day, David!

robert

On 29.11.2007 18:33, Brian Tkatch wrote:
You are sure this is faster? How would introducing one more layer of
indirection make this faster? Am I missing something?

Cheers

robert

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

create package states_pkg as
function pending return number ;
end states_pkg;

create package body states_pkg as
function pending return number is begin return 1; end pending;
end states_pkg;



select * from jobs where state = States_pkg.PENDING


I wouldn't assume it is faster.

I like to do this, even with readable string values, because a function
can be checked at compile time whereas strings are not

select * from jobs where state = 'Pending' -- whoops!

Hello,

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.

Regards
Marten

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

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.

On Thu, 29 Nov 2007 23:19:08 +0100, Marten Lehmann
<lehmannmapson (AT) cnm (DOT) de> wrote:

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.