|
Insert into person.find_results (CL_ID,
PER_NUM,
DOC_NUM,
REG_DATE,
REG_STAT,
REG_PRICH,
ST_DATE,
RES,
PAR,
FAMCH_ID,
IMCH_ID,
OTCH_ID,
DRCH_ID,
DOCCH_ID,
RAJON_ID,
UNKNOWN_ID,
YAROSL_ID,
WORK_ID,
SMO)
Select distinct R.ID,
S.PER_NUM,
S.DOC_NUM,
S.REG_DATE,
S.REG_STAT,
S.REG_PRICH,
S.ST_DATE,
1,
1,
'N',
'N',
'N',
'N',
'N',
S.RAJON_ID,
DECODE(MIN(H.ARCHIVE_ID),'0','N','Y'),
DECODE(Y.YAR,0,'Y',1,'N','0'),--NVL(R.OBLS,'Y'),--DECODE(NVL(O.YAR,0),0,'Y','N'),
DECODE(NVL(R.NO_FOND,'0'),'32-01321','N','03-00288','N','06-00194','N','09-00123','N','12-00508','N','15-00588','N','18-00138','N','21-00265','N','23-00571','N',
'26-00375','N','29-00144','N','32-01238','N','34-00224','N','37-00276','N','40-07987','N','43-01476','N','46-01002','N','50-01434','N','68-03991','N','Y'),
R.SMO
from person.smo_report1 R, person.history H,person.status_new S, person.smo_yarosl_obl Y
where R.birthday=H.birthday and
R.lastname=H.lastname and
R.midname=H.midname and
R.firstname=H.firstname and
replace(R.paper_sernum,' ')=replace(H.paper_sernum,' ') and
H.person_id=S.per_num and
NVL(R.obls,'пустая область')=Y.obls
group by
R.ID,R.NO_FOND,R.SMO,S.PER_NUM,S.DOC_NUM,S.REG_DATE,S.REG_STAT,S.REG_PRICH,S.ST_DATE,S.RAJON_ID,Y.YAR;
COMMIT;
dbms_output.put_line('After PAR=1 :' || to_char(sysdate(), 'DD-MON-YY HH24:MI:SS'));
end;
Процедура для запроса №2
(d_update_uvd date)
as
id_first number;
id_last number;
id_avg number;
minn number;
maxx number;
/*d_update_uvd date - Дата обновления УВД, имеет формат типа "01-NOV-02"*/
begin
dbms_output.put_line('Begin :' || to_char(sysdate(), 'DD-MON-YY HH24:MI:SS'));
/*PAR=1 (пустое отчество) and PAR=4 (без отчества)*/
Insert into person.find_results (CL_ID,
PER_NUM,
DOC_NUM,
REG_DATE,
REG_STAT,
REG_PRICH,
ST_DATE,
RES,
PAR,
FAMCH_ID,
IMCH_ID,
OTCH_ID,
DRCH_ID,
DOCCH_ID,
RAJON_ID,
UNKNOWN_ID,
YAROSL_ID,
WORK_ID,
SMO)
Select distinct R.ID,----
S.PER_NUM,
S.DOC_NUM,
S.REG_DATE,
S.REG_STAT,
S.REG_PRICH,
S.ST_DATE,
1,
DECODE(min(DECODE(NVL(R.MIDNAME,'1'),'1',0,1)+DECODE(NVL(H.MIDNAME,'1'),'1',0,1)),0,1,4),
'N',
'N',
'N',
'N',
'N',
S.RAJON_ID,
DECODE(MIN(H.ARCHIVE_ID),'0','N','Y'),
DECODE(Y.YAR,0,'Y',1,'N','0'),
DECODE(NVL(R.NO_FOND,'0'),'32-01321','N','03-00288','N','06-00194','N','09-00123','N','12-00508','N','15-00588','N','18-00138','N','21-00265','N','23-00571','N',
'26-00375','N','29-00144','N','32-01238','N','34-00224','N','37-00276','N','40-07987','N','43-01476','N','46-01002','N','50-01434','N','68-03991','N','Y'),
R.SMO
from person.smo_report1 R, person.history H,person.status_new S,person.for_find_new_2 FF,
person.smo_yarosl_obl Y
where R.birthday=H.birthday and
R.lastname=H.lastname and
--R.midname=H.midname and
R.firstname=H.firstname and
replace(R.paper_sernum,' ')=replace(H.paper_sernum,' ') and
R.id=FF.c and
H.person_id=S.per_num and
NVL(R.obls,'пустая область')=Y.obls
group by
R.ID,R.NO_FOND,R.SMO,S.PER_NUM,S.DOC_NUM,S.REG_DATE,S.REG_STAT,S.REG_PRICH,S.ST_DATE,S.RAJON_ID,Y.YAR;
COMMIT;
dbms_output.put_line('Update PAR=1,4 '|| to_char(sysdate(), 'DD-MON-YY HH24:MI:SS'));
Update person.find_results
set otch_id=null
where par=4;
COMMIT;
dbms_output.put_line('Update PAR=1,4 '|| to_char(sysdate(), 'DD-MON-YY HH24:MI:SS'));
end;
Результаты explain plan:
Для первого запроса:
ID PARENT_ID Query Plan
0 INSERT STATEMENT Cost=853786
1 0 SORT UNIQUE Cost=853786
2 1 SORT GROUP BY Cost=853786
3 2 HASH JOIN Cost=810262
4 3 INDEX FAST FULL SCAN OBLL Cost=1
5 3 NESTED LOOPS Cost=809943
6 5 MERGE JOIN Cost=809939
7 6 SORT JOIN Cost=282222
8 7 TABLE ACCESS FULL SMO_REPORT1 Cost=6561
9 6 SORT JOIN Cost=527717
10 9 TABLE ACCESS FULL HISTORY Cost=28354
11 5 TABLE ACCESS BY INDEX ROWID STATUS_NEW Cost=4
12 11 INDEX RANGE SCAN PER_NUM Cost=3
13 rows selected.
Для второго:
ID PARENT_ID Query Plan
0 INSERT STATEMENT Cost=984402
1 0 SORT UNIQUE Cost=984402
2 1 SORT GROUP BY Cost=984402
3 2 NESTED LOOPS Cost=892392
4 3 NESTED LOOPS Cost=892364
5 4 NESTED LOOPS Cost=892362
6 5 MERGE JOIN Cost=826016
7 6 SORT JOIN Cost=298299
8 7 TABLE ACCESS FULL SMO_REPORT1 Cost=6561
9 6 SORT JOIN Cost=527717
10 9 TABLE ACCESS FULL HISTORY Cost=28354
11 5 VIEW FOR_FIND_NEW_2 Cost=66347
12 11 MINUS Cost=
13 12 SORT UNIQUE Cost=38968
14 13 TABLE ACCESS FULL SMO_REPORT1 Cost=6561
15 12 SORT UNIQUE Cost=27379
16 15 TABLE ACCESS FULL FIND_RESULTS Cost=2967
17 4 INDEX FAST FULL SCAN OBLL Cost=1
18 3 TABLE ACCESS BY INDEX ROWID STATUS_NEW Cost=4
19 18 INDEX RANGE SCAN PER_NUM Cost=3
20 rows selected.
крупные табл:
SMO_REPORT1 - 1 300 000 строк
HISTORY - 3 000 000
STATUS_NEW - 1 300 000
person.for_find_new_2 - представление smo_report1 minus find_results
|
