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

CREATE TABLE mother
(
   ID    NUMBER(10,0)
         NOT NULL
         CONSTRAINT coPKMother
         PRIMARY KEY

 , name  VARCHAR2(2000)
         NOT NULL

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

 , dateOfBirth  DATE
                NOT NULL

 , meaninglessData CHAR(100)
)
;
CREATE TABLE child
(
   ID    NUMBER(10,0)
         NOT NULL
         CONSTRAINT coPKChild
           PRIMARY KEY

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

 , name     VARCHAR(2000)
            NOT NULL

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

 , dateOfBirth DATE
               NOT NULL

 , meaninglessData CHAR(100)
                   NOT NULL
)
;
CREATE SEQUENCE sqMother
  START WITH 1
  INCREMENT BY 1
  noMaxValue
  cache 100
  ;
  CREATE SEQUENCE sqChild
  START WITH 1
  INCREMENT BY 1
  noMaxValue
  cache 100

/
DECLARE

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

BEGIN

  EXECUTE IMMEDIATE 'truncate table child';
  EXECUTE IMMEDIATE 'truncate table mother';
  FOR m IN 1..noOfMothers LOOP

     INSERT INTO mother (id,name,gender,dateOfBirth,meaninglessData)
     VALUES (    sqMother.NEXTVAL
              , 'Mother ' || TO_CHAR(sqMother.CURRVAL)
              , 'F'
              , SYSDATE
              , 'x'
              );

     FOR c IN 1..noChildrenPerMother LOOP
       INSERT INTO child (id, motherID, name, gender, dateOfBirth, meaninglessData)
        VALUES (
                   sqChild.NEXTVAL
                ,  sqMother.CURRVAL
                ,  'Child number ' || TO_CHAR(c) || ' of mother ' || TO_CHAR(sqMother.CURRVAL)
                ,  'F'
                ,  SYSDATE
                , 'x'
               );
     END LOOP;

  END LOOP;
  COMMIT;
END;

----------------------

SELECT TABLE_NAME, COLUMN_NAME
FROM DBA_TAB_COLS
WHERE OWNER = 'SYS'
AND TABLE_NAME LIKE 'DBA%'
AND COLUMN_NAME LIKE '%BLOCKS%';

-----------------------

-- 1.1
SELECT TABLESPACE_NAME FROM ALL_ALL_TABLES WHERE TABLE_NAME = 'MOTHER';

-- 1.2 65536, 1048576
SELECT INITIAL_EXTENT, NEXT_EXTENT FROM ALL_TABLES WHERE TABLE_NAME = 'MOTHER';

-- 1.3 - 10
SELECT PCT_FREE FROM ALL_TABLES WHERE TABLE_NAME = 'MOTHER';

-- 1.4 - USERS01.DBF
SELECT DBA_DATA_FILES.FILE_NAME FROM DBA_DATA_FILES
INNER JOIN ALL_TABLES ON (DBA_DATA_FILES.TABLESPACE_NAME = ALL_TABLES.TABLESPACE_NAME)
WHERE ALL_TABLES.TABLE_NAME = 'MOTHER';

-- 1.5 - 8192
SELECT DBA_TABLESPACES.BLOCK_SIZE FROM ALL_TABLES
INNER JOIN DBA_TABLESPACES ON (ALL_TABLES.TABLESPACE_NAME = DBA_TABLESPACES.TABLESPACE_NAME)
WHERE ALL_TABLES.TABLE_NAME = 'MOTHER';

-- 2 ---- 95 blocks
EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('mircea','MOTHER');
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT BLOCKS FROM ALL_TABLES WHERE TABLE_NAME = 'MOTHER';

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

-- 3.2 91
SELECT AVG(COUNT(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))) FROM MOTHER
GROUP BY DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID);

-- 3.3 empty blocks between 135 and 192
SELECT DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
FROM MOTHER
ORDER BY DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID);

-- 4
EXPLAIN PLAN FOR SELECT * FROM MOTHER;

SELECT * FROM TABLE(dbms_xplan.display(NULL,NULL,'basic'));

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';

-- 12877, 12898, 12919 => 21

-- 5
SELECT ID, NAME, GENDER, DATEOFBIRTH, MEANINGLESSDATA
FROM MOTHER WHERE ID = '1' OR ID = '1250' OR ID = '2500' OR ID = '3750' OR ID = '5000';

EXPLAIN PLAN FOR SELECT ID, NAME, GENDER, DATEOFBIRTH, MEANINGLESSDATA
FROM MOTHER WHERE ID = '1' OR ID = '1250' OR ID = '2500' OR ID = '3750' OR ID = '5000';

SELECT * FROM TABLE(dbms_xplan.display(NULL,NULL,'basic'));

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

CREATE TABLE mother
(
   ID    NUMBER(10,0)
         NOT NULL
         CONSTRAINT coPKMother
         PRIMARY KEY

 , name  VARCHAR2(2000)
         NOT NULL

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

 , dateOfBirth  DATE
                NOT NULL

 , meaninglessData CHAR(100)
)
PCTFREE 0
;

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

/
DECLARE

  noOfMothers  CONSTANT int := 5000;

BEGIN

  EXECUTE IMMEDIATE 'truncate table mother';
  FOR m IN 1..noOfMothers LOOP

     INSERT INTO mother (id,name,gender,dateOfBirth,meaninglessData)
     VALUES (    sqMother.NEXTVAL
              , 'Mother ' || TO_CHAR(sqMother.CURRVAL)
              , 'F'
              , SYSDATE
              , 'x'
              );

  END LOOP;
  COMMIT;
END;

-- 7.1 ---- 88 blocks, previous: 95

EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('mircea','MOTHER');
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT BLOCKS FROM ALL_TABLES WHERE TABLE_NAME = 'MOTHER';

-- 7.2 ---- 82 blocks, previous: 95
SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) FROM MOTHER;

-- 7.3 ---- 60.9756098 rows / block, previous: 91
SELECT AVG(COUNT(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))) FROM MOTHER
GROUP BY DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID);

-- 8
-- a) 95 - 82 = 13 blocks
-- b) 91 - 61 = ~30 rows / block

-- 9
EXPLAIN PLAN FOR SELECT * FROM MOTHER;

SELECT * FROM TABLE(dbms_xplan.display(NULL,NULL,'basic'));

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';

-- before: 12877, 12898, 12919 => 21
-- now: 136454, 136578, 136602 => 24

-- 10
SELECT ID, NAME, GENDER, DATEOFBIRTH, MEANINGLESSDATA
FROM MOTHER WHERE ID = '1' OR ID = '1250' OR ID = '2500' OR ID = '3750' OR ID = '5000';

EXPLAIN PLAN FOR SELECT ID, NAME, GENDER, DATEOFBIRTH, MEANINGLESSDATA
FROM MOTHER WHERE ID = '1' OR ID = '1250' OR ID = '2500' OR ID = '3750' OR ID = '5000';

SELECT * FROM TABLE(dbms_xplan.display(NULL,NULL,'basic'));