-----Too big - 13GB - consider using smaller set

CREATE TABLE t1

AS

WITH

  t AS (

    SELECT /*+ materialize */ dbms_random.string('p',50) AS pad

    FROM dual

    CONNECT BY level <= 10000

    ORDER BY dbms_random.normal

  )

SELECT /*+ ordered */ rownum AS id,

       (1+ABS(MOD(dbms_random.random,100000))) num1,

       (1+ABS(MOD(dbms_random.random,100000))) num2,

       t1.pad AS pad01,

       t1.pad AS pad02

FROM t t1, t t2;

CREATE TABLE t2

AS

WITH

  t AS (

    SELECT /*+ materialize */ dbms_random.string('p',50) AS pad

    FROM dual

    CONNECT BY level <= 1000

    ORDER BY dbms_random.normal

  )

SELECT /*+ ordered */ rownum AS id,

       (1+ABS(MOD(dbms_random.random,100000))) num1,

       (1+ABS(MOD(dbms_random.random,100000))) num2,

       t1.pad AS pad01,

       t1.pad AS pad02

FROM t t1, t t2;

CREATE TABLE T3 AS SELECT * FROM T2 WHERE ID<50000;

----not necessary

create index  t1_idx_num1 on t1(num1);

create index  t2_idx_num1 on t2(num1);

---Necessaary for T1

create unique index t1_idx_id on  t1(id);

create unique index t2_idx_id on  t2(id);

create unique index t3_idx_id on  t3(id);

exec  dbms_stats.gather_table_stats(ownname => user,tabname => 'T1');

exec  dbms_stats.gather_table_stats(ownname => user,tabname => 'T2');

exec  dbms_stats.gather_table_stats(ownname => user,tabname => 'T3');

---small query

select /*+ use_nl(t3) full(t3) gather_plan_statistics*//*test_999550*/ * from t3,t1

where t1.id=t3.id;

---big query

select /*+ use_nl(t2) full(t2) gather_plan_statistics*//*test_650*/ * from t2,t1

where t1.id=t2.id

and t2.id>650000;