/* ************************************************** */
/* This script creates Index-organized version of     */
/* mother and heap organized version of child for use
/* in the exercises on table                          */
/* organization. It also generates a realistic        */
/* distribution of data in the tables                 */
/*                                                    */
/* NOTE: you need to change the schema name in the    */
/* two calls to DBMS_STATS to reflect your own        */
/* schema instead of mine                             */
/*                                                    */
/* For the exercises, you need to change the          */
/* organization of the tables as specified in the     */
/* exercise text by modifying the DDL part of this    */
/* script as needed. The procedure to create the      */
/* data will run unchanged regardless of the table    */
/* organization                                       */
/*                                                    */
/*                      Bo Brunsgaard, september 2016 */
/* ************************************************** */

DROP TABLE child purge
;
DROP TABLE mother purge
;
DROP SEQUENCE sqMother
;
DROP SEQUENCE sqChild
;

-- 1 - USING Index organized
CREATE TABLE mother
(
    id       NUMBER(10,0)
             NOT NULL
             CONSTRAINT coPKMother
               PRIMARY KEY
               USING INDEX

  , firstName VARCHAR(50)
              NOT NULL

  , lastName  VARCHAR(50)
              NOT NULL

  , gender    CHAR(01)
              NOT NULL
              CONSTRAINT coCHMotherGender
                CHECK (gender = 'F')

  , dateBorn  DATE
              NOT NULL

 , hairColor   VARCHAR2(20)
               NOT NULL

 , meaninglessData CHAR(100)
                   NOT NULL
)
ORGANIZATION INDEX
;

CREATE TABLE child
 (
     id      NUMBER(10,0)
             NOT NULL
             CONSTRAINT coPKChild
               PRIMARY KEY
               USING INDEX

  , firstName VARCHAR(50)
              NOT NULL

  , lastName  VARCHAR(50)
              NOT NULL

  , gender    CHAR(01)
              NOT NULL
              CONSTRAINT coCHChildGender
                CHECK (gender IN ('F','M'))

  , yearBorn  NUMBER(4,0)

  , motherId  NUMBER(10,0)
              NOT NULL
              CONSTRAINT coFKChildMother
                references mother

  , meaninglessData CHAR(100)
                    NOT NULL
   )
;
CREATE INDEX ixChildMotherID
  ON  child(motherID)
  ;

CREATE SEQUENCE sqMother
  START WITH 1
  INCREMENT BY 1
  noMaxValue
  cache 20
;

CREATE SEQUENCE sqChild
  START WITH 1
  INCREMENT BY 1
  noMaxValue
  cache 20
;
COMMIT
;
/
DECLARE

  noOfMothers           CONSTANT int := 5000;
  noOfChildrenPerMother CONSTANT int := 5;

BEGIN

  FOR m IN 1..noOfMothers LOOP

     INSERT
       INTO mother (id, firstname, lastname, gender, dateBorn, hairColor, meaninglessData)
       VALUES      (  sqMother.NEXTVAL
                    , 'First name ' || TO_CHAR(sqMother.CURRVAL)
                    , 'Last Name ' || TO_CHAR(sqMother.CURRVAL)
                    , 'F'
                    , TO_DATE('1980-01-01 00:00:00','YYYY-MM-DD hh24:mi:ss')
                       + TO_YMINTERVAL(TO_CHAR(FLOOR(dbms_random.VALUE(1,30))) || '-' || TO_CHAR(FLOOR(dbms_random.VALUE(1,12))) )
                    ,  CASE MOD(m,50)
                          WHEN 0 THEN 'blond'
                          ELSE        'brown'
                       END
                    , 'x');

  END LOOP;

    FOR c IN 1..noOfChildrenPerMother LOOP
       FOR m IN (SELECT id FROM mother) LOOP

         INSERT
         INTO child (id, firstname, lastname, gender, yearBorn, motherID, meaninglessdata)
         VALUES     (sqChild.NEXTVAL, 'Child first ' || TO_CHAR(sqChild.CURRVAL) ,
                     'Child no ' || TO_CHAR(c) || 'of mother ' || TO_CHAR(m.id),
                     'M'
                     , EXTRACT(YEAR FROM (ADD_MONTHS(SYSDATE, -12 * c))), m.id, 'y' );

    END LOOP;

  END LOOP;

  COMMIT;

END;
/
-- -------------------------------------------------------
EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('MSKN','MOTHER');
/
EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('MSKN','CHILD');
/
--
-- Check that the distributions of values are
-- sort of reasonable
--
SELECT yearBorn
     , COUNT(*)
  FROM child
  GROUP BY yearBorn
  ORDER BY yearBorn
;
SELECT SUBSTR(haircolor,1,10) AS haircolor
     , COUNT(*)
  FROM mother
  GROUP BY haircolor
  ;

-- 2 Check data storage (using DBMS_ROWID)
--  2.1. How many blocks does the mother table use?
--  2.2. Ditto for the child table
--  2.3. Find the total number of blocks used to store the two tables.

-- 2.1
SELECT LEAF_BLOCKS FROM DBA_INDEXES WHERE TABLE_NAME = 'MOTHER';


SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
              FROM MOTHER;

-- 2.2
SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
              FROM CHILD;

-- 2.3
SELECT DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
              FROM MOTHER
UNION
SELECT DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
              FROM CHILD;

SELECT COUNT(*) FROM
(
SELECT COUNT(LEAF_BLOCKS) FROM DBA_INDEXES WHERE TABLE_NAME = 'MOTHER'
UNION
SELECT DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
              FROM CHILD
              )x;

-- 2.4. Choose one specific mother (say, motherID = 2500)
--  2.4.1. In which block in the mother table is her data stored?
--  2.4.2. In which blocks in the child table are her children stored?

-- 2.4.1
SELECT ID, DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
            FROM mother WHERE id = 2500
            ORDER BY ID;

--  2.4.2
SELECT ID, DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
            FROM child WHERE motherid = 2500
            ORDER BY ID;

-- 3. Retrieving mother data by table scan
--  3.1. Write an SQL statement that retrieves the number of blond mothers
--  3.2. Find AND SAVE the execution plan. How does Oracle resolve the query?
--  3.3. Find the IO cost (in consistent gets) for executing that statement.

-- 3.1 - 3.2
SET autotrace ON EXPLAIN
/
SELECT ID FROM MOTHER WHERE HAIRCOLOR = 'blond';
/
SET autotrace off
/

-- 3.3
SELECT ID FROM MOTHER WHERE HAIRCOLOR = 'blond';

SELECT SUM(V$SESSTAT.VALUE) AS "Total value"
       FROM V$STATNAME INNER JOIN V$SESSTAT
       ON (V$STATNAME.STATISTIC# = V$SESSTAT.STATISTIC#)
       WHERE V$SESSTAT.SID = SYS_CONTEXT('userenv', 'sid')
       AND V$STATNAME.NAME = 'consistent gets';

-- 1114760, 1114871, 1114982 -> 111


-- 4. Retrieving child data by table scan
--  4.1. Write an SQL statement that retrieves the count of children born in the
--  year 2012
--  4.2. Find AND SAVE the execution plan. How does Oracle resolve the query?
--  4.3. Find the IO cost (in consistent gets) for executing that statement.

-- 4.1 - 4.2
SET autotrace ON EXPLAIN
/
SELECT ID FROM CHILD WHERE YEARBORN = 2012;
/
SET autotrace off
/

-- 4.3
SELECT ID FROM CHILD WHERE YEARBORN = 2012;

SELECT SUM(V$SESSTAT.VALUE) AS "Total value"
       FROM V$STATNAME INNER JOIN V$SESSTAT
       ON (V$STATNAME.STATISTIC# = V$SESSTAT.STATISTIC#)
       WHERE V$SESSTAT.SID = SYS_CONTEXT('userenv', 'sid')
       AND V$STATNAME.NAME = 'consistent gets';

-- 1115595, 1116208, 1116821 -> 613

-- 5. Retrieving rows from both tables based on join (indexed)
--  5.1. Write an SQL statement (join) that retrieves the name of a mother and
--  the years of birth for all of her children. Reuse the mother that you
--  decided on above in exercise 1.4
--  5.2. Find AND SAVE the execution plan. How does Oracle resolve the query?
--  5.3. Find the IO cost (in consistent gets) for executing that statement.

-- 5.1 - 5.2

SET autotrace ON EXPLAIN
/
SELECT MOTHER.FIRSTNAME, CHILD.YEARBORN
    FROM MOTHER INNER JOIN CHILD ON (MOTHER.ID = CHILD.MOTHERID)
    WHERE MOTHER.ID = 2500;
/
SET autotrace off
/

-- 5.3
SELECT MOTHER.FIRSTNAME, CHILD.YEARBORN
    FROM MOTHER INNER JOIN CHILD ON (MOTHER.ID = CHILD.MOTHERID)
    WHERE MOTHER.ID = 2500;

SELECT SUM(V$SESSTAT.VALUE) AS "Total value"
       FROM V$STATNAME INNER JOIN V$SESSTAT
       ON (V$STATNAME.STATISTIC# = V$SESSTAT.STATISTIC#)
       WHERE V$SESSTAT.SID = SYS_CONTEXT('userenv', 'sid')
       AND V$STATNAME.NAME = 'consistent gets';

-- 1116939, 1116948, 1116957 -> 9

-- 6.1 - 6.2

SET autotrace ON EXPLAIN
/
SELECT FIRSTNAME FROM MOTHER
       WHERE ID IN (1000, 1100, 1200, 1300, 1400, 1500, 1600, 1700, 1800, 1900);
/
SET autotrace off
/

-- 6.3
SELECT FIRSTNAME FROM MOTHER
       WHERE ID IN (1000, 1100, 1200, 1300, 1400, 1500, 1600, 1700, 1800, 1900);

SELECT SUM(V$SESSTAT.VALUE) AS "Total value"
       FROM V$STATNAME INNER JOIN V$SESSTAT
       ON (V$STATNAME.STATISTIC# = V$SESSTAT.STATISTIC#)
       WHERE V$SESSTAT.SID = SYS_CONTEXT('userenv', 'sid')
       AND V$STATNAME.NAME = 'consistent gets';

-- 1708946, 1708958, 1708970 -> 12