Hello Experts
I need a script, which fills my table with test data up.
What are your opinion, could this script be tuned?
CREATE TABLE TEST_TAB
( id_test NUMBER(10)
, x_domain VARCHAR2(1)
, id_object NUMBER(10)
)
TABLESPACE ALL_DATA
/
id_test : This is the primary key. It should goes from 1 to 10 000 000.
x_domain : It has 3 values and the distribution should be so:
M : 94 % (M = Main)
S : 4 % (S = Sub)
O : 2 % (O = Other)
id_object : Random number between 0 and 10 000 000
SET TERMOUT ON SERVEROUTPUT ON
DECLARE
v_start TIMESTAMP(6);
v_end TIMESTAMP(6);
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEST_TAB';
v_start := CURRENT_TIMESTAMP;
INSERT /*+ APPEND PARALLEL (16) */
INTO TEST_TAB (id_test, x_domain, id_object)
SELECT LEVEL AS ID_TEST
, CASE WHEN level <= 10000000/100*94 THEN 'M'
WHEN level > 10000000/100*94 AND level <= 10000000/100*98 THEN 'S'
ELSE 'O'
END AS X_DOMAIN
, TRUNC(dbms_random.value(0, 10000000)) AS ID_OBJECT
FROM DUAL
CONNECT BY LEVEL <= 10000000
;
COMMIT;
v_end := CURRENT_TIMESTAMP;
DBMS_OUTPUT.PUT_LINE('Insert took : ' || TO_CHAR(v_end-v_start));
END;
/
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 16 11:21:43 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Insert took : +000000000 00:02:12.020934000
PL/SQL procedure successfully completed.
SQL>
I have read from rp0428 that firstly we have to use SQL and not PL/SQL. But it seems for me that this sql took too long time.. Can it be tuned?