-- (c) Copyright 2002 BMC Software Ltd. All rights reserved.
-- This QA scripts creates a base set of objects so the Query Rewrite statements (40) can be tested.
-- Four tables are created.
-- Creates six indexes.
-- To include columns with non-distinct keys.
-- Columns with NULL.
-- Join with non-distinct matches on both sides of the join.
-- INserts data into the table and indexes.
-- This scripts will be run on all Oracle releases.
-- Created by Paul Mowat

CREATE TABLE QAT0001IUN
(
  c01 NUMBER CONSTRAINT QAT0001iunc01 NOT NULL,
  -- unique single column index
  c02 VARCHAR2(30) CONSTRAINT QAT0001iunc02 NOT NULL, 
  -- unindexed description
  c03 NUMBER CONSTRAINT QAT0001iunc03 NOT NULL,             
  -- non-unique single column index  MOD (key,10)
  c04 NUMBER CONSTRAINT QAT0001iunc04 NOT NULL,             
  -- non-unique single column index  MOD (key,100)
  c05 NUMBER CONSTRAINT QAT0001iunc05 NOT NULL,             
  -- non-unique single column index  MOD (key,1000)
  c06 NUMBER CONSTRAINT QAT0001iunc06 NOT NULL,             
  -- non-unique single column index  TRUNC (random * 100)
  c07 NUMBER 
  -- non-unique single column index  TRUNC (key / 2)    
  -- for use in CONNECT BY
  -- no NOT NULL constraint
)
TABLESPACE TS01
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

--Table 2 created without the NULL constraints of table 1.

CREATE TABLE QAT0002IUN
(
   c01 NUMBER,
  -- unique single column index
  c02 VARCHAR2(30), 
  -- unindexed description
  c03 NUMBER,             
  -- non-unique single column index  MOD (key,10)
  c04 NUMBER,             
  -- non-unique single column index  MOD (key,100)
  c05 NUMBER,             
  -- non-unique single column index  MOD (key,1000)
  c06 NUMBER,             
  -- non-unique single column index  TRUNC (random * 100)
  c07 NUMBER 
  -- non-unique single column index  TRUNC (key / 2)    
  -- for use in CONNECT BY
  -- no NOT NULL constraint
)
TABLESPACE TS01
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

--Table 3 created with no indexes.

CREATE TABLE QAT0003IUN
(
  c01 NUMBER CONSTRAINT QAT0003iunc01 NOT NULL,
  -- unique single column index
  c02 VARCHAR2(30) CONSTRAINT QAT0003iunc02 NOT NULL, 
  -- unindexed description
  c03 NUMBER CONSTRAINT QAT0003iunc03 NOT NULL,             
  -- non-unique single column index  MOD (key,10)
  c04 NUMBER CONSTRAINT QAT0003iunc04 NOT NULL,             
  -- non-unique single column index  MOD (key,100)
  c05 NUMBER CONSTRAINT QAT0003iunc05 NOT NULL,             
  -- non-unique single column index  MOD (key,1000)
  c06 NUMBER CONSTRAINT QAT0003iunc06 NOT NULL,             
  -- non-unique single column index  TRUNC (random * 100)
  c07 NUMBER 
  -- non-unique single column index  TRUNC (key / 2)    
  -- for use in CONNECT BY
  -- no NOT NULL constraint
)
TABLESPACE TS01
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

--Table 4 created to test all other types of data types.

CREATE TABLE QAT0004IUN
(
  c01 CHAR(30),
  c02 VARCHAR(30),
  C03 NUMBER(38),
  c04 VARCHAR2(30),
  C05 VARCHAR2(30),
  C06 VARCHAR(30),
  C07 CHAR (10) CONSTRAINT QAT004IUN NULL
)
  TABLESPACE TS01
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);             


--Inserts into QAT0001IUN, Table 1.

DECLARE
  c_max_rows           CONSTANT NUMBER := 1000;
  v_key                NUMBER;
  v_c01                QAT0001IUN.c01%TYPE;
  v_c02                QAT0001IUN.c02%TYPE;
  v_c03                QAT0001IUN.c03%TYPE;
  v_c04                QAT0001IUN.c04%TYPE;
  v_c05                QAT0001IUN.c05%TYPE;
  v_c06                QAT0001IUN.c06%TYPE;
  v_c07                QAT0001IUN.c07%TYPE; 

BEGIN
  sim_lcg.initialise (1);

  FOR v_key IN 0..c_max_rows - 1 LOOP
    v_c01 := v_key;
    v_c02 := LPAD (TO_CHAR (v_key),20,'0');
    v_c03 := MOD (v_key,20);
    v_c04 := MOD (v_key,50);
    v_c05 := MOD (v_key,1000);
    v_c06 := TRUNC (sim_lcg.random * 100);
    IF v_key = 0 THEN
      v_c07 := NULL;
    ELSE
      v_c07 := TRUNC (v_key / 2);   
    END IF;

    INSERT INTO QAT0001iun 
    (
      c01,
      c02,
      c03,
      c04,
      c05,
      c06,
      c07
    )
    VALUES 
    (
      v_c01,
      v_c02,
      v_c03,
      v_c04,
      v_c05,
      v_c06,
      v_c07
    );
    IF MOD (v_key,20000) = 19999 THEN
      COMMIT;
    END IF;
  END LOOP;  

  COMMIT;
END;
/

--Inserts into QAT0002IUN,Table 2.

DECLARE
  c_max_rows           CONSTANT NUMBER := 1000;
  v_key                NUMBER;
  v_c01                QAT0002IUN.c01%TYPE;
  v_c02                QAT0002IUN.c02%TYPE;
  v_c03                QAT0002IUN.c03%TYPE;
  v_c04                QAT0002IUN.c04%TYPE;
  v_c05                QAT0002IUN.c05%TYPE;
  v_c06                QAT0002IUN.c06%TYPE;
  v_c07                QAT0002IUN.c07%TYPE; 

BEGIN
  sim_lcg.initialise (1);

  FOR v_key IN 0..c_max_rows - 1 LOOP
    v_c01 := v_key;
    v_c02 := LPAD (TO_CHAR (v_key),20,'0');
    v_c03 := MOD (v_key,20);
    v_c04 := MOD (v_key,100);
    v_c05 := MOD (v_key,1000);
    v_c06 := TRUNC (sim_lcg.random * 100);
    IF v_key = 0 THEN
      v_c07 := NULL;
    ELSE
      v_c07 := TRUNC (v_key / 2);   
    END IF;

    INSERT INTO QAT0002iun 
    (
      c01,
      c02,
      c03,
      c04,
      c05,
      c06,
      c07
    )
    VALUES 
    (
      v_c01,
      v_c02,
      v_c03,
      v_c04,
      v_c05,
      v_c06,
      v_c07
    );
    IF MOD (v_key,20000) = 19999 THEN
      COMMIT;
    END IF;
  END LOOP;  

  COMMIT;
END;
/


--Inserts into QAT0003IUN,Table 3.

DECLARE
  c_max_rows           CONSTANT NUMBER := 1000;
  v_key                NUMBER;
  v_c01                QAT0003IUN.c01%TYPE;
  v_c02                QAT0003IUN.c02%TYPE;
  v_c03                QAT0003IUN.c03%TYPE;
  v_c04                QAT0003IUN.c04%TYPE;
  v_c05                QAT0003IUN.c05%TYPE;
  v_c06                QAT0003IUN.c06%TYPE;
  v_c07                QAT0003IUN.c07%TYPE; 

BEGIN
  sim_lcg.initialise (1);

  FOR v_key IN 0..c_max_rows - 1 LOOP
    v_c01 := v_key;
    v_c02 := LPAD (TO_CHAR (v_key),20,'0');
    v_c03 := MOD (v_key,20);
    v_c04 := MOD (v_key,100);
    v_c05 := MOD (v_key,1000);
    v_c06 := TRUNC (sim_lcg.random * 100);
    IF v_key = 0 THEN
      v_c07 := NULL;
    ELSE
      v_c07 := TRUNC (v_key / 2);   
    END IF;

    INSERT INTO QAT0003IUN 
    (
      c01,
      c02,
      c03,
      c04,
      c05,
      c06,
      c07
    )
    VALUES 
    (
      v_c01,
      v_c02,
      v_c03,
      v_c04,
      v_c05,
      v_c06,
      v_c07
    );
    IF MOD (v_key,20000) = 19999 THEN
      COMMIT;
    END IF;
  END LOOP;  

  COMMIT;
END;
/

--Inserts into QAT0004IUN,Table 4.

DECLARE 
  c_max_rows           CONSTANT NUMBER := 1000;
  v_key                NUMBER;
  v_c01                QAT0004IUN.c01%TYPE;
  v_c02                QAT0004IUN.c02%TYPE;
  v_c03                QAT0004IUN.c03%TYPE;
  v_c04                QAT0004IUN.c04%TYPE;
  v_c05                QAT0004IUN.c05%TYPE;
  v_c06                QAT0004IUN.c06%TYPE;
  v_c07                QAT0004IUN.c07%TYPE; 

BEGIN
  sim_lcg.initialise (1);

  FOR v_key IN 0..c_max_rows - 1 LOOP
    v_c01 := v_key;
    v_c02 := LPAD (TO_CHAR (v_key),20,'0');
    v_c03 := MOD (v_key,20);
    v_c04 := MOD (v_key,100);
    v_c05 := MOD (v_key,1000);
    v_c06 := TRUNC (sim_lcg.random * 100);
    IF v_key = 0 THEN
      v_c07 := NULL;
    ELSE
      v_c07 := TRUNC (v_key / 2);   
    END IF;

    INSERT INTO QAT0004IUN 
    (
      c01,
      c02,
      c03,
      c04,
      c05,
      c06,
      c07
    )
    VALUES 
    (
      v_c01,
      v_c02,
      v_c03,
      v_c04,
      v_c05,
      v_c06,
      v_c07
    );
    IF MOD (v_key,20000) = 19999 THEN
      COMMIT;
    END IF;
  END LOOP;  

  COMMIT;
END;
/


--CREATES A TABLE TO TEST THE Upper Function
CREATE TABLE TEST_UPPER
( C01 NUMBER, C02 VARCHAR2(30));

---INSERTS DATA INTO THE TEST_UPPER TABLE
INSERT INTO TEST_UPPER VALUES (1, 'ONE');
INSERT INTO TEST_UPPER VALUES (2, 'TWO');
INSERT INTO TEST_UPPER VALUES (3, 'THREE');
INSERT INTO TEST_UPPER VALUES (4, 'FOUR');
INSERT INTO TEST_UPPER VALUES (5, 'FIVE');
COMMIT;

--CREATES A TABLE TO TEST THE LOWER FUNCTION
CREATE TABLE TEST_LOWER
( C01 NUMBER, C02 VARCHAR2(30));

--INSERTS DATA INTO THE TEST_LOWER TABLE
INSERT INTO TEST_LOWER VALUES (1, 'ONE');
INSERT INTO TEST_LOWER VALUES (2, 'TWO');
INSERT INTO TEST_LOWER VALUES (3, 'THREE');
INSERT INTO TEST_LOWER VALUES (4, 'FOUR');
INSERT INTO TEST_LOWER VALUES (5, 'FIVE');
COMMIT;

--Creates six indexes for QAT0001iun.

CREATE INDEX QAT0001iuni01 ON QAT0001iun (c01)
TABLESPACE TS02
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

CREATE INDEX QAT0001iuni02 ON QAT0001iun (c03)
TABLESPACE TS02
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

CREATE INDEX QAT0001iuni03 ON QAT0001iun (c04)
TABLESPACE TS02
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

CREATE INDEX QAT0001iuni04 ON QAT0001iun (c05)
TABLESPACE TS02
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

CREATE INDEX QAT0001iuni05 ON QAT0001iun (c06)
TABLESPACE TS02
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

CREATE INDEX QAT0001iuni06 ON QAT0001iun (c07)
TABLESPACE TS02
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

--Creates four indexes for QAT0002iun.

CREATE INDEX QAT0002iuni01 ON QAT0002iun (c01, C02)
TABLESPACE TS02
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

CREATE INDEX QAT0002iuni02 ON QAT0002iun (c03)
TABLESPACE TS02
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

CREATE INDEX QAT0002iuni03 ON QAT0002iun (c04)
TABLESPACE TS02
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

CREATE INDEX QAT0002iuni04 ON QAT0002iun (c05)
TABLESPACE TS02
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);


--Creates four indexes for QAT0004IUN.

CREATE INDEX QAT0004IUNI01 ON QAT0004IUN (c01)
TABLESPACE TS01
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

CREATE INDEX QAT0004IUNI02 ON QAT0004IUN (c02)
TABLESPACE TS01
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

CREATE INDEX QAT0004IUNI03 ON QAT0004IUN (c03)
TABLESPACE TS01
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

CREATE INDEX QAT0004IUNI04 ON QAT0004IUN (c04)
TABLESPACE TS01
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

CREATE INDEX QAT0004IUNI05 ON QAT0004IUN (c05)
TABLESPACE TS01
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

CREATE INDEX QAT0004IUNI06 ON QAT0004IUN (c06)
TABLESPACE TS01
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

CREATE INDEX QAT0004IUNI07 ON QAT0004IUN (c07)
TABLESPACE TS01
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);


--Analyzes the stats for each table.

ANALYZE TABLE QAT0001iun COMPUTE STATISTICS;
ANALYZE TABLE QAT0002iun COMPUTE STATISTICS;
ANALYZE TABLE QAT0003iun COMPUTE STATISTICS;
ANALYZE TABLE QAT0004IUN COMPUTE STATISTICS;








