DROP TABLE T1;
CREATE TABLE T1 AS
SELECT LEVEL-1 AS N1, TRUNC((LEVEL-1)/10) AS N2, TRUNC((LEVEL-1)/100) AS N3, TRUNC((LEVEL-1)/1000) AS N4 FROM DUAL CONNECT BY LEVEL <= 10000;
ALTER TABLE T1 MODIFY N2 NUMBER NOT NULL;
ALTER TABLE T1 MODIFY N3 NUMBER NOT NULL;
ALTER TABLE T1 MODIFY N4 NUMBER NOT NULL;
ALTER TABLE T1 ADD CONSTRAINT T1PK PRIMARY KEY (N1);
CREATE INDEX T1N2 ON T1 (N2);
CREATE INDEX T1N3 ON T1 (N3);
CREATE INDEX T1N4 ON T1 (N4);
CREATE INDEX T1N4N3 ON T1 (N4, N3);
EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => 'UADMIN', TABNAME => 'T1' , DEGREE => 2);
EXEC DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => 'UADMIN', INDNAME => 'T1PK', DEGREE => 2);
EXEC DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => 'UADMIN', INDNAME => 'T1N2', DEGREE => 2);
EXEC DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => 'UADMIN', INDNAME => 'T1N3', DEGREE => 2);
EXEC DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => 'UADMIN', INDNAME => 'T1N4', DEGREE => 2);
EXEC DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => 'UADMIN', INDNAME => 'T1N4N3', DEGREE => 2);
-- T2 / LOCAL DB
DROP TABLE T2;
CREATE TABLE T2 AS SELECT * FROM T1;
ALTER TABLE T2 MODIFY N1 NUMBER NOT NULL;
ALTER TABLE T2 MODIFY N2 NUMBER NOT NULL;
ALTER TABLE T2 MODIFY N3 NUMBER NOT NULL;
ALTER TABLE T2 MODIFY N4 NUMBER NOT NULL;
ALTER TABLE T2 ADD CONSTRAINT T2PK PRIMARY KEY (N1);
CREATE INDEX T2N2 ON T2 (N2);
CREATE INDEX T2N3 ON T2 (N3);
CREATE INDEX T2N4 ON T2 (N4);
CREATE INDEX T2N4N3 ON T2 (N4, N3);
EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => 'UADMIN', TABNAME => 'T2' , DEGREE => 2);
EXEC DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => 'UADMIN', INDNAME => 'T2PK', DEGREE => 2);
EXEC DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => 'UADMIN', INDNAME => 'T2N2', DEGREE => 2);
EXEC DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => 'UADMIN', INDNAME => 'T2N3', DEGREE => 2);
EXEC DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => 'UADMIN', INDNAME => 'T2N4', DEGREE => 2);
EXEC DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => 'UADMIN', INDNAME => 'T2N4N3', DEGREE => 2);
-- T3 / REMOTE DB
DROP TABLE T3;
CREATE TABLE T3 AS SELECT * FROM T1@TESTDB;
-- T4 / LOCAL DB
DROP CLUSTER H1 INCLUDING TABLES;
CREATE CLUSTER H1 (N4 NUMBER) HASHKEYS 10;
CREATE TABLE T4 CLUSTER H1 (N4) AS SELECT * FROM T1;
-- T5 / LOCAL DB
CREATE CLUSTER H2 (N4 NUMBER);
CREATE INDEX H2N4 ON CLUSTER H2;
CREATE TABLE T5 CLUSTER H2 (N4) AS SELECT * FROM T1;
CREATE TABLE T6 CLUSTER H2 (N4) AS SELECT * FROM T1;
-- M1 / LOCAL DB
CREATE MATERIALIZED VIEW M1 ENABLE QUERY REWRITE AS
SELECT N4, COUNT(*) AS CNT FROM T1 GROUP BY N4;
-- PT1, PT2 / LOCAL DB
CREATE TABLE PT1 PARTITION BY LIST (N4)
(
PARTITION PT1_0 VALUES (0),
PARTITION PT1_1 VALUES (1),
PARTITION PT1_2 VALUES (2),
PARTITION PT1_3 VALUES (3),
PARTITION PT1_4 VALUES (4),
PARTITION PT1_5 VALUES (5),
PARTITION PT1_6 VALUES (6),
PARTITION PT1_7 VALUES (7),
PARTITION PT1_8 VALUES (8),
PARTITION PT1_9 VALUES (9)
)
AS SELECT * FROM T1;
CREATE TABLE PT2 PARTITION BY LIST (N4)
(
PARTITION PT2_0 VALUES (0),
PARTITION PT2_1 VALUES (1),
PARTITION PT2_2 VALUES (2),
PARTITION PT2_3 VALUES (3),
PARTITION PT2_4 VALUES (4),
PARTITION PT2_5 VALUES (5),
PARTITION PT2_6 VALUES (6),
PARTITION PT2_7 VALUES (7),
PARTITION PT2_8 VALUES (8),
PARTITION PT2_9 VALUES (9)
)
AS SELECT * FROM T1;