Re: using constants

From: <fitzjarrell_at_cox.net>
Date: Thu, 29 Nov 2007 08:10:13 -0800 (PST)
Message-ID: <0041e176-e5ea-43bc-8285-d27815f4860d@y43g2000hsy.googlegroups.com>

Comments embedded.
On Nov 29, 9:44 am, Marten Lehmann <lehmannmap..._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.

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.

> However, these values wouldn't be
> self-explaining any more.
>

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

> 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.

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

>
> 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
>
> ?
>

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 :old.state = 'pending' then
                         :new.pending := 1;
                         :new.finished := 0;
             elsif :old.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 :old.state = 'pending' then
 12                           :new.pending := 1;
 13                           :new.finished := 0;
 14               elsif :old.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>
> Regards
> Marten

Others may offer more elegant solutions.

David Fitzjarrell Received on Thu Nov 29 2007 - 10:10:13 CST