--记录测试结果 DROP TABLE T_RU_160929_LHR; CREATE TABLE T_RU_160929_LHR ( ID NUMBER PRIMARY KEY, SQL_TYPES VARCHAR2(255), SQL1 VARCHAR2(255), SQL2 VARCHAR2(255), SQL3 VARCHAR2(4000), IS_DIRECT VARCHAR2(20), IS_NOLOGGING VARCHAR2(20), IS_PARALLEL VARCHAR2(20), ARCH_REDO NUMBER, ARCH_UNDO NUMBER, NOARCH_REDO NUMBER, NOARCH_UNDO NUMBER, ARCH_USE_TIME NUMBER, NOARCH_USE_TIME NUMBER, SQL_EXPLAIN CLOB, COMMENTS VARCHAR2(255) ); --插入要执行的SQL语句 INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (1, 'CTAS', NULL, NULL, 'CREATE TABLE T_RU_CTAS_LHR AS SELECT * FROM T_B', 'Y', 'N', 'N'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (2, 'CTAS', NULL, NULL, 'CREATE TABLE T_RU_CTAS_LHR NOLOGGING AS SELECT * FROM T_B', 'Y', 'Y', 'N'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (3, 'CTAS', NULL, NULL, 'CREATE TABLE T_RU_CTAS_LHR NOLOGGING PARALLEL 4 AS SELECT * FROM T_B', 'Y', 'Y', 'Y'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (4, 'CI', NULL, NULL, 'CREATE INDEX IND_TA_LHR ON T_A(OBJECT_ID)', 'N', 'N', 'N'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (5, 'CI', NULL, NULL, 'CREATE INDEX IND_TA_LHR ON T_A(OBJECT_ID) NOLOGGING', 'N', 'Y', 'N'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (6, 'CI', NULL, NULL, 'CREATE INDEX IND_TA_LHR ON T_A(OBJECT_ID) NOLOGGING PARALLEL 4', 'N', 'Y', 'Y'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (7, 'MOVE', NULL, NULL, 'ALTER TABLE T_A MOVE', 'N', 'N', 'N'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (8, 'MOVE', NULL, NULL, 'ALTER TABLE T_A MOVE NOLOGGING', 'N', 'Y', 'N'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (9, 'MOVE', NULL, NULL, 'ALTER TABLE T_A MOVE NOLOGGING PARALLEL 4', 'N', 'Y', 'Y'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (10, 'INSERT', NULL, NULL, 'INSERT INTO T_A SELECT * FROM T_B', 'N', 'N', 'N'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (11, 'INSERT', 'ALTER TABLE T_A NOLOGGING', NULL, 'INSERT INTO T_A SELECT * FROM T_B', 'N', 'Y', 'N'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (12, 'INSERT', NULL, NULL, 'INSERT /*+ APPEND */ INTO T_A SELECT * FROM T_B', 'Y', 'N', 'N'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (13, 'INSERT', 'ALTER TABLE T_A NOLOGGING', NULL, 'INSERT /*+ APPEND */ INTO T_A SELECT * FROM T_B', 'Y', 'Y', 'N'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (14, 'INSERT', 'ALTER TABLE T_A NOLOGGING', NULL, 'INSERT /*+ PARALLEL(4) APPEND */ INTO T_A SELECT * FROM T_B', 'Y', 'Y', 'Y'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (15, 'INSERT', 'ALTER TABLE T_A NOLOGGING', 'ALTER SESSION ENABLE PARALLEL DML', 'INSERT /*+ PARALLEL(4) APPEND */ INTO T_A SELECT * FROM T_B', 'Y', 'Y', 'Y(PDML)'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (16, 'UPDATE', NULL, NULL, 'UPDATE T_A T SET T.DATA_OBJECT_ID =(SELECT TB.DATA_OBJECT_ID FROM T_B TB WHERE TB.OBJECT_ID = T.OBJECT_ID AND ROWNUM=1) WHERE T.OBJECT_ID <= 1000', 'N', 'N', 'N'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (17, 'UPDATE', NULL, NULL, 'UPDATE /*+ PARALLEL(4) */ T_A T SET T.DATA_OBJECT_ID =(SELECT TB.DATA_OBJECT_ID FROM T_B TB WHERE TB.OBJECT_ID = T.OBJECT_ID AND ROWNUM=1) WHERE T.OBJECT_ID <= 1000', 'N', 'N', 'Y(Queries)'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (18, 'UPDATE', 'ALTER TABLE T_A NOLOGGING', NULL, 'UPDATE T_A T SET T.DATA_OBJECT_ID =(SELECT TB.DATA_OBJECT_ID FROM T_B TB WHERE TB.OBJECT_ID = T.OBJECT_ID AND ROWNUM=1) WHERE T.OBJECT_ID <= 1000', 'N', 'Y', 'N'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (19, 'UPDATE', 'ALTER TABLE T_A NOLOGGING', NULL, 'UPDATE /*+ PARALLEL(4) */ T_A T SET T.DATA_OBJECT_ID =(SELECT TB.DATA_OBJECT_ID FROM T_B TB WHERE TB.OBJECT_ID = T.OBJECT_ID AND ROWNUM=1) WHERE T.OBJECT_ID <= 1000', 'N', 'Y', 'Y(Queries)'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (20, 'UPDATE', 'ALTER SESSION ENABLE PARALLEL DML', NULL, 'UPDATE /*+ PARALLEL(4) */ T_A T SET T.DATA_OBJECT_ID =(SELECT TB.DATA_OBJECT_ID FROM T_B TB WHERE TB.OBJECT_ID = T.OBJECT_ID AND ROWNUM=1) WHERE T.OBJECT_ID <= 1000', 'N', 'N', 'Y(PDML)'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (21, 'UPDATE', 'ALTER TABLE T_A NOLOGGING', 'ALTER SESSION ENABLE PARALLEL DML', 'UPDATE /*+ PARALLEL(4) */ T_A T SET T.DATA_OBJECT_ID =(SELECT TB.DATA_OBJECT_ID FROM T_B TB WHERE TB.OBJECT_ID = T.OBJECT_ID AND ROWNUM=1) WHERE T.OBJECT_ID <= 1000', 'N', 'Y', 'Y(PDML)'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (22, 'MERGE', 'ALTER TABLE T_A NOLOGGING', NULL, 'MERGE INTO T_A T USING (SELECT TA.ROWID ROWIDS, MAX(TB.DATA_OBJECT_ID) DATA_OBJECT_ID FROM T_B TB, T_A TA WHERE TB.OBJECT_ID = TA.OBJECT_ID AND TA.OBJECT_ID <= 1000 GROUP BY TA.ROWID) T1 ON (T.ROWID = T1.ROWIDS)WHEN MATCHED THEN UPDATE SET T.DATA_OBJECT_ID = T1.DATA_OBJECT_ID', 'N', 'Y', 'N'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (23, 'MERGE', 'ALTER TABLE T_A NOLOGGING', NULL, 'MERGE /*+ PARALLEL(4) */ INTO T_A T USING (SELECT TA.ROWID ROWIDS, MAX(TB.DATA_OBJECT_ID) DATA_OBJECT_ID FROM T_B TB, T_A TA WHERE TB.OBJECT_ID = TA.OBJECT_ID AND TA.OBJECT_ID <= 1000 GROUP BY TA.ROWID) T1 ON (T.ROWID = T1.ROWIDS)WHEN MATCHED THEN UPDATE SET T.DATA_OBJECT_ID = T1.DATA_OBJECT_ID', 'N', 'Y', 'Y(Queries)'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (24, 'MERGE', 'ALTER TABLE T_A NOLOGGING', 'ALTER SESSION ENABLE PARALLEL DML', 'MERGE /*+ PARALLEL(4) */ INTO T_A T USING (SELECT TA.ROWID ROWIDS, MAX(TB.DATA_OBJECT_ID) DATA_OBJECT_ID FROM T_B TB, T_A TA WHERE TB.OBJECT_ID = TA.OBJECT_ID AND TA.OBJECT_ID <= 1000 GROUP BY TA.ROWID) T1 ON (T.ROWID = T1.ROWIDS)WHEN MATCHED THEN UPDATE SET T.DATA_OBJECT_ID = T1.DATA_OBJECT_ID', 'N', 'Y', 'Y(PDML)'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (25, 'DELETE', NULL, NULL, 'DELETE FROM T_A T WHERE T.OBJECT_ID IN ( SELECT TB.OBJECT_ID FROM T_B TB) AND T.OBJECT_ID <= 1000', 'N', 'N', 'N'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (26, 'DELETE', NULL, NULL, 'DELETE /*+ PARALLEL(4) */ FROM T_A T WHERE T.OBJECT_ID IN ( SELECT TB.OBJECT_ID FROM T_B TB) AND T.OBJECT_ID <= 1000', 'N', 'N', 'Y(Queries)'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (27, 'DELETE', 'ALTER TABLE T_A NOLOGGING', NULL, 'DELETE FROM T_A T WHERE T.OBJECT_ID IN ( SELECT TB.OBJECT_ID FROM T_B TB) AND T.OBJECT_ID <= 1000', 'N', 'Y', 'N'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (28, 'DELETE', 'ALTER TABLE T_A NOLOGGING', NULL, 'DELETE /*+ PARALLEL(4) */ FROM T_A T WHERE T.OBJECT_ID IN ( SELECT TB.OBJECT_ID FROM T_B TB) AND T.OBJECT_ID <= 1000', 'N', 'Y', 'Y(Queries)'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (29, 'DELETE', 'ALTER SESSION ENABLE PARALLEL DML', NULL, 'DELETE /*+ PARALLEL(4) */ FROM T_A T WHERE T.OBJECT_ID IN ( SELECT TB.OBJECT_ID FROM T_B TB) AND T.OBJECT_ID <= 1000', 'N', 'N', 'Y(PDML)'); INSERT INTO T_RU_160929_LHR (ID, SQL_TYPES, SQL1, SQL2, SQL3, IS_DIRECT, IS_NOLOGGING, IS_PARALLEL) VALUES (30, 'DELETE', 'ALTER TABLE T_A NOLOGGING', 'ALTER SESSION ENABLE PARALLEL DML', 'DELETE /*+ PARALLEL(4) */ FROM T_A T WHERE T.OBJECT_ID IN ( SELECT TB.OBJECT_ID FROM T_B TB) AND T.OBJECT_ID <= 1000', 'N', 'Y', 'Y(PDML)'); COMMIT; |