How to manage constants within an Oracle Database

oracleoracle-11g

This question touches on a few different parts of Oracle that I'm not particularly familiar with so bear with me:

I am trying to redesign some of the procedures and functions within the database I manage. One of the most annoying things is the use of integers to express the status of rows.

For example rows that need to be processed get the number 3, rows that have been processed get the number 0 and rows that are in the middle of processing get number 1. This is a simplified example.

What I was hoping to do was code those integers into constants so when the procedures are written the words would be self-documenting…

I've tried to use packages and functions to manage these constants. Everything comes out a bit messy. The cleanest I've found is:

CREATE OR REPLACE FUNCTION PROCESSED RETURN NUMBER AS BEGIN RETURN 0;    END;
/

This allows you to type the sql below which looks relatively neat.

SELECT rows
FROM table
WHERE status = PROCESSED;

The problem I've found is that these columns are indexed to return quickly but the functions mean the indexes aren't used.

With that background my question is: How should constants be managed in Oracle effectively? What solution has the best trade off for visual simplicity, logical organisation and database performance.

Best Answer

The reason your index isn't being used is that the two following SELECT are fundamentally different to the optimizer:

SELECT rows FROM table WHERE status = PROCESSED;

SELECT rows FROM table WHERE status = 0;

In the first case the database will interpret PROCESSED as a variable, and its value, even if constant won't be learned until execution time. An index on status will be used only if status has a strong selectivity for all values (ie there are many different values). Since you have only 3 values, Oracle makes a FULL SCAN since from its point of view any of the 3 values could be used.

In the second case, an index will be used if you have statistics on this column that show that the value 0 is very selective (ie there are few rows processed). Oracle knows at compile time that this value won't change thus an index scan will always be effective.

So, if you're sure that there are few rows processed, you will have to help the optimizer, for example with an hint like /*+ FIRST_ROWS*/.

Or you could use comments in your SQL: 0 -- PROCESSED.

Also remember that FULL SCAN are not evil.

Related Solutions

Oracle 11g – Best Way to Delete Very Large Recordsets

The logic with 'A' and 'B' might be "hidden" behind a virtual column on which you could do the partitioning:

alter session set nls_date_format = 'yyyy-mm-dd';
drop   table tq84_partitioned_table;

create table tq84_partitioned_table (
  status varchar2(1)          not null check (status in ('A', 'B')),
  date_a          date        not null,
  date_b          date        not null,
  date_too_old    date as
                       (  case status
                                 when 'A' then add_months(date_a, -7*12)
                                 when 'B' then            date_b
                                 end
                        ) virtual,
  data            varchar2(100) 
)
partition   by range  (date_too_old) 
( 
  partition p_before_2000_10 values less than (date '2000-10-01'),
  partition p_before_2000_11 values less than (date '2000-11-01'),
  partition p_before_2000_12 values less than (date '2000-12-01'),
  --
  partition p_before_2001_01 values less than (date '2001-01-01'),
  partition p_before_2001_02 values less than (date '2001-02-01'),
  partition p_before_2001_03 values less than (date '2001-03-01'),
  partition p_before_2001_04 values less than (date '2001-04-01'),
  partition p_before_2001_05 values less than (date '2001-05-01'),
  partition p_before_2001_06 values less than (date '2001-06-01'),
  -- and so on and so forth..
  partition p_ values less than (maxvalue)
);

insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('B', date '2008-04-14', date '2000-05-17', 
 'B and 2000-05-17 is older than 10 yrs, must be deleted');


insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('B', date '1999-09-19', date '2004-02-12', 
 'B and 2004-02-12 is younger than 10 yrs, must be kept');


insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('A', date '2000-06-16', date '2010-01-01', 
 'A and 2000-06-16 is older than 3 yrs, must be deleted');


insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('A', date '2009-06-09', date '1999-08-28', 
 'A and 2009-06-09 is younger than 3 yrs, must be kept');

select * from tq84_partitioned_table order by date_too_old;

-- drop partitions older than 10 or 3 years, respectively:

alter table tq84_partitioned_table drop partition p_before_2000_10;
alter table tq84_partitioned_table drop partition p_before_2000_11;
alter table tq84_partitioned_table drop partition p2000_12;

select * from tq84_partitioned_table order by date_too_old;

Oracle server connection reset by peer socket write error

The windows error 54 is associated with a port busy or problems with the network communication, try editing the listener.ora file and change the port, then restart the listener service (or if you're able to restart the machine, do so).

It looks like that, for some reason, the port 1521 hungs and there's no longer a TNS Listener service listening there.

P.D. if you didn't configured a TNS_ADMIN folder, the listener.ora file must be in the %ORACLE_HOME%\network\admin folder, where the %ORACLE_HOME% is where the product was installed

The Windows error reference is in: http://msdn.microsoft.com/en-us/library/windows/desktop/ms681382(v=vs.85).aspx

Best Answer

Related Solutions

Oracle 11g – Best Way to Delete Very Large Recordsets

Oracle server connection reset by peer socket write error

Related Question