The Correct ISNUMERIC Function in Oracle

1. In the function, I use REPLACE(UPPER()) to replace the 'E' with 'A', which fixes the issue with strings like '1234e2', otherwise Oracle considers it as a number, i.e. 123400
2. I prefer to return NUMBER instead of BOOLEAN, which allows me to use the function in DML SQL statements directly.
-----------------------------------------------------------------------------
FUNCTION ISNUMERIC (This_str IN VARCHAR2) RETURN NUMBER
IS
  v_dummy NUMBER;
BEGIN
  v_dummy := TO_NUMBER(REPLACE(UPPER(This_str),'E','A'));
  RETURN 0;
  EXCEPTION WHEN OTHERS THEN
    RETURN 1;
END;
/

However are Numbers and Characters Stored in Oracle

1. For integers others than those in tens (i.e. 10, 100...), 1-99 is stored using 2 bytes, 101~999 using 3 bytes...
2. For integers in tens, their trailing 0s are not used any extra bytes. E.g. 100000000 still uses 2 bytes. Not sure how does Oracle achieve it?
3. For characters, each character is stored using 1 bytes.
4. So if you need to store single digit like '0' or '1' indicator, better to store it as VARCHAR2(1), not NUMBER(1).

You can use vsize to verify.

How does DataStage Project Level Auto-Purge Job Logs Work

1. The job has to be run. I.e. if the job isn't run, its old logs will not be purged.
2. The job has to run successfully. E.g. if the job is aborted, its old logs will not be purged.

Someone mentioned to me that the project level auto-purge job logs only affects those jobs imported after auto-purge change. This doens't appear to be the behaviour with the version 8.5 I am working with.

What is It Doing - Select Count(*) on a Table

If there are indexes on the table, it will do an index full scan (small index) or index fast full scan (large index).

But which index will be used by the optimizer? The primary key index, or the index on the column that in the COUNT()?

The answer is it will use the smallest index on the table. Take a look the execution plan and then verify the index segment is the smallest one.

Using NEW_VALUE to Record Start/End Time in Oracle SQL Script

In a SQL script, I need to record the start and end time of a block which performs some DMLs, then I will query the records being DML'ed during the peroid.

COLUMN CHAR_SYSDATE NEW_VALUE START_DATETIME
SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') CHAR_SYSDATE FROM DUAL;

......
...The block
......

COLUMN CHAR_SYSDATE NEW_VALUE END_DATETIME
SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') CHAR_SYSDATE FROM DUAL;
 
SELECT ... FROM ...
 WHERE audit_creation_date BETWEEN TO_DATE('&START_DATETIME','YYYYMMDDHH24MISS') AND TO_DATE('&END_DATETIME','YYYYMMDDHH24MISS');

Indexes on Foreign Keys

Indexes on foreign keys are not always required. But I recommend to create the indexes on foreign keys when: 1) There are deletions on the parent table and 2) the child table is large.

It has happened to me a few times when deleting from the parent table, it was kind of hanging even there were no associated records in the child table. And when looking at the execution plan, it wouldn't show anything about the foreign key validation to the child table. After adding the indexes the deletions finished instantly.

Oracle Tablespace Fragmentation and Resize

http://forums.oracle.com/forums/thread.jspa?threadID=695610