Just get a table data is copied to another table, an emp table has billions of data, now want to have 2017 data with a table storage, 2018 with a piece of table, only by SQL ran quite a waste of time, want to use stored procedures themselves to run in the background,
The create table emp_20190731 as select * from emp
Want to be sure the above statements use stored procedures to achieve in the background, but he hasn't learned, baidu is not achieve their want
Ask for help,

CodePudding user response:

That you have to look through stored procedures to create and use a good, normal data, if you don't update directly using SQL plus time limit conditions, execute soon, this year's data can be in a stored procedure, then use the job control increment by means of the log, so that you can add and update automatically, remember to add index, or we'll crash

CodePudding user response:

Completely don't need to use stored procedures,
1, can be directly run eg shell the background
The export is=former
Vi m_t. Sh
Up sqlplus -s/as sysdba & lt; ./logfile
The set serveroutput on.
The set timing on;
Alter session set nls_date_format='yyyymmdd HH24: MI: SS';
The select sysdate from dual;
The create table emp_20190731 nologging as select */* + 4 */parallel from emp where id=2017;
The select sysdate from dual;
The create table emp_20190831 nologging as select */* + 4 */parallel from emp where id=2018;
The select sysdate from dual;
Prompt the get the count from emp_20190731 emp_20190831;
Select count (1) CNT, 'emp_20190731 as tname the from emp_20190731;
Select count (1) CNT, 'emp_20190831 as tname the from emp_20190831;
The select sysdate from dual;
exit;
EOF

Sh m_t. Sh & gt;>/dev/null 2 & gt; & 1 & amp;

2,
Can also write a stored procedure
The create or replace procedure bak_t (bak_name in varchar2, t_name varchar2, in t_where in varchar2)
As
V_sql varvhar2 (2000);
The begin
V_sql:='create table' | | bak_name | | 'nologging as the select/* + 4 */* from the parallel' | | t_name | | 'where id=' | | t_where;
Dbms_output. Put_line (v_sql);
The execute immediate v_sql;
end;

Call PLSQL
Call bak_t (' bak_emp_2017 ', 'emp, 2017);
Or
The begin
Bak_t (' bak_emp_2017 ', 'emp, 2017);
end;

Up sqlplus can directly use the
The exec bak_t (' bak_emp_2017 ', 'emp, 2017);
You can also use the begin end call,

3, the proposal with shell proc

CodePudding user response:

First built table emp_2017, structure and emp to be completely consistent, if not consistent, will change under the asterisk in the code block to need the content of the
MOD (N, 10)=0, the N, here is to emp in a more uniform digital type fields respectively, are not free, 10 is needed into the process of number, 0 is the first process, you can also change to 1, it is the second process... Until 9, it is the tenth process,

DECLARE
I NUMBER;
Cr emp % rowtype;
CURSOR userrows IS
SELECT
*
The FROM
Emp
WHERE
Year='2017'
AND
The mod (n, 10)=0;

The BEGIN
I:=0;
FOR c IN cr LOOP
INSERT INTO emp_2017 (column_name1, column_name2 column_name3... ) VALUES (
Arthur c. olumn_name1,
Arthur c. olumn_name2,
Arthur c. olumn_name3...
);

IF
I & gt;=1000
THEN
I:=0;
COMMIT;
END the IF;
I:=I + 1;
END LOOP;

COMMIT;
END;