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

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 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 := 50000;
  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,100)
                          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))) - 1), m.id, 'y' );

    END LOOP;

  END LOOP;

  COMMIT;

END;
/
-- -------------------------------------------------------
EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('mircea','MOTHER');
/
EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('mircea','CHILD');
/