2011-02-26Oracle Example Scripts for "The Where Clause"

The Equals Operator

Surrogate Keys

The following script creates the EMPLOYEES table with 1000 entries.

CREATE TABLE employees (
   employee_id   NUMBER         NOT NULL,
   first_name    VARCHAR2(1000) NOT NULL,
   last_name     VARCHAR2(1000) NOT NULL,
   date_of_birth DATE                   ,
   phone_number  VARCHAR2(1000) NOT NULL,
   junk          CHAR(1000)             ,
   CONSTRAINT employees_pk PRIMARY KEY (employee_id)
);

INSERT INTO employees (employee_id,  first_name,
                       last_name,    date_of_birth, 
                       phone_number, junk)
SELECT level, 
       DBMS_RANDOM.STRING('u', 1) || 
            DBMS_RANDOM.STRING('l', DBMS_RANDOM.value(2,10)),
       DBMS_RANDOM.STRING('u', 1) || 
            DBMS_RANDOM.STRING('l', DBMS_RANDOM.value(2,10)),
       SYSDATE - (DBMS_RANDOM.normal() * 365 * 10) - 40 * 365,
       TRUNC(DBMS_RANDOM.VALUE(1000,10000)),
       'junk'
  FROM DUAL 
  CONNECT BY level <= 1000;

UPDATE employees 
   SET first_name='MARKUS', 
       last_name='WINAND'
 WHERE employee_id=123;

BEGIN
     DBMS_STATS.GATHER_TABLE_STATS(null, 'EMPLOYEES', 
     METHOD_OPT=>'for all indexed columns', CASCADE => true);
END;
/

Notes:

Concatenated Keys

This script changes the EMPLOYEES table so that it reflects the situation after the merger with Very Big Company:

-- add subsidiary_id and update existing records
ALTER TABLE employees ADD subsidiary_id NUMBER;
UPDATE      employees SET subsidiary_id = 30;
ALTER TABLE employees MODIFY subsidiary_id NOT NULL;

-- change the PK
ALTER TABLE employees DROP PRIMARY KEY;
ALTER TABLE employees ADD CONSTRAINT employees_pk 
      PRIMARY KEY (employee_id, subsidiary_id);

-- generate more records (Very Big Company)
INSERT INTO employees (employee_id,  first_name, 
                       last_name,    date_of_birth, 
                       phone_number, subsidiary_id, junk)
SELECT level, 
       DBMS_RANDOM.STRING('u', 1) || 
            DBMS_RANDOM.STRING('l', DBMS_RANDOM.value(2,10)),
       DBMS_RANDOM.STRING('u', 1) || 
            DBMS_RANDOM.STRING('l', DBMS_RANDOM.value(2,10)),
       SYSDATE - (DBMS_RANDOM.normal() * 365 * 10) - 40 * 365,
       TRUNC(DBMS_RANDOM.VALUE(1000,10000)), 
       TRUNC(DBMS_RANDOM.VALUE(1,level/9000*29)),
       'junk'
FROM DUAL CONNECT BY level <= 9000;


BEGIN
     DBMS_STATS.GATHER_TABLE_STATS(null, 'EMPLOYEES', 
     METHOD_OPT=>'for all indexed columns', CASCADE => true);
END;
/

Notes:

The next script introduces the index on SUBSIDIARY_ID to support the query for all employees of one particular subsidiary:

CREATE INDEX emp_sub_id ON employees (subsidiary_id);

BEGIN
     DBMS_STATS.GATHER_TABLE_STATS(null, 'EMPLOYEES', 
     METHOD_OPT=>'for all indexed columns', CASCADE => true);
END;
/

Notes:

Although that gives decent performance, it’s better to use the index that supports the primary key:

-- use tmp index to support the PK
CREATE UNIQUE INDEX employee_pk_tmp 
    ON employees (subsidiary_id, employee_id, 1);

ALTER TABLE employees 
      MODIFY CONSTRAINT employees_pk 
      USING INDEX employee_pk_tmp;

-- recreate the pk index as needed
DROP   INDEX employee_pk;
CREATE UNIQUE INDEX employee_pk 
    ON employees (subsidiary_id, employee_id);

-- change the constraint to use the new index
ALTER TABLE employees 
      MODIFY CONSTRAINT employees_pk 
      USING INDEX employee_pk;

-- drop old indexes
DROP INDEX employee_pk_tmp;
DROP INDEX emp_sub_id;

BEGIN
     DBMS_STATS.GATHER_TABLE_STATS(null, 'EMPLOYEES', 
     METHOD_OPT=>'for all indexed columns', CASCADE => true);
END;
/

Notes:

Slow Indexes, Part II

The following statement removes some statistics to make my example work.

BEGIN
      DBMS_STATS.DELETE_COLUMN_STATS
       (null, 'EMPLOYEES', 'SUBSIDIARY_ID');
END;
/

To re-create them, use the same procedure as before:

BEGIN
     DBMS_STATS.GATHER_TABLE_STATS(null, 'EMPLOYEES', 
     METHOD_OPT=>'for all indexed columns', CASCADE => true);
END;
/

The final statement to create, and analyze, the index on the LAST_NAME column:

CREATE INDEX emp_name ON employees (last_name);

BEGIN
     DBMS_STATS.GATHER_TABLE_STATS(null, 'EMPLOYEES', 
     METHOD_OPT=>'for all indexed columns', CASCADE => true);
END;
/

Functions

Case-Insensitive Search

The randomized names were already created in correct case, just update “my” record:

UPDATE employees 
   SET first_name = 'Markus'
     , last_name  = 'Winand'
 WHERE employee_id   = 123
   AND subsidiary_id = 30;

The statement to create the function-based index:

CREATE INDEX emp_up_name ON employees (UPPER(last_name));
DROP INDEX emp_name;

Notes:

The next statement will, as of release 11g, automatically collect the extended statistics for the function-based index.

BEGIN
     DBMS_STATS.GATHER_TABLE_STATS(null, 'EMPLOYEES', 
     METHOD_OPT=>'for all indexed columns', CASCADE => true);
END;
/

User-Defined Functions

Define a PL/SQL function that calculates the age and attempts to use it in an index:

CREATE FUNCTION get_age(date_of_birth DATE) 
RETURN NUMBER
AS
BEGIN
    RETURN TRUNC(MONTHS_BETWEEN(SYSDATE, DATE_OF_BIRTH)/12);
END;
/

CREATE INDEX invalid ON EMPLOYEES (get_age(date_of_birth));

You should get the error “ORA-30553: The function is not deterministic”.

Indexing NULL

The following re-creates the standard indexes after you have run the examples from the book:

DROP INDEX emp_dob_upname;

CREATE INDEX emp_dob ON employees (date_of_birth, '1');

Emulating Partial Indexes

CREATE TABLE messages AS
SELECT level id
     , CASE WHEN DBMS_RANDOM.NORMAL() < 0.09 THEN 'N' ELSE 'Y' END processed
     , DBMS_RANDOM.VALUE(0,100) receiver
     , RPAD('junk', 200) message
  FROM dual 
CONNECT BY level < 999999;
;

You have to update the statistics after creating the function-based index. No statistics, no CBO, no FIB.

EXEC DBMS_STATS.GATHER_TABLE_STATS( user
                                  ,'MESSAGES'
                                  , cascade=>true);