下面我的个视图的SQL文：
create or replace view fx_v_wholesalerdetail as
select '批销' as reportname,
b.id as detailid,
b.noreg,
c.bookid,
c.bookname,
c.isbn,
c.netprice,
c.author,
c.duty_editor,
c.editiontimes,
c.printtimes,
c.first_receive_date,
b.scale,
b.netamount,
b.discount,
b.amount,
0 as ysscale,
0.0 as ysmoney,
0 as xsscale,
0.0 as xsmoney,
(c.endprintnum-c.startprintnum+1) as printscalar,
(c.endprintnum-c.startprintnum+1)*c.netprice as printnetamount,
h.editor_house_name,
d.sort_name,
e.clientnum,
e.clientname,
f.province_name,
g.area_name,
a.ywy,
a.modifytime as makedate,
a.sendtime,
r.roomid,
r.roomcode,
r.roomname,
0 as state
from fx_wholesalercheck a
inner join fx_wholesalerchecklist b on a.noreg = b.noreg
inner join fx_bookinfo c on b.bookid = c.bookid
inner join fx_code_book_sort d on c.booksortid = d.sort_id
inner join fx_clientinfo e on a.clientid = e.clientid
left join fx_code_province f on e.regionid = f.id
left join fx_code_area g on e.areaid = g.area_id
left join fx_code_editor_house h on c.editorhouseid = h.editor_house_id
left join fx_storekwinfo w on b.kwid=w.kwid
left join fx_storeroominfo r on w.roomid=r.roomid
where a.deltf1
and b.deltf1
union all
select '发货' as reportname,
c.id as detailid,
a.noreg,
d.bookid,
d.bookname,
d.isbn,
d.netprice,
d.author,
d.duty_editor,
d.editiontimes,
d.printtimes,
d.first_receive_date,
c.scale,
c.netamount,
c.discount,
c.amount,
c.ysscale,
c.ysmoney,
c.xsscale,
c.xsmoney,
(d.endprintnum-d.startprintnum+1) as printscalar,
(d.endprintnum-d.startprintnum+1)*d.netprice as printnetamount,
i.editor_house_name,
e.sort_name,
f.clientnum,
f.clientname,
g.province_name,
h.area_name,
a.ywy,
a.modifytime as makedate,
b.sendtime,
r.roomid,
r.roomcode,
r.roomname,
1 as state
from fx_wholesalercheck a
inner join fx_deliverycheck b on a.noreg = b.noreg
inner join fx_deliverychecklist c on b.deliveryid = c.deliveryid
inner join fx_bookinfo d on c.bookid = d.bookid
inner join fx_code_book_sort e on d.booksortid = e.sort_id
inner join fx_clientinfo f on a.clientid = f.clientid
left join fx_code_province g on f.regionid = g.id
left join fx_code_area h on f.areaid = h.area_id
left join fx_code_editor_house i on d.editorhouseid = i.editor_house_id
left join fx_storekwinfo w on c.kwid=w.kwid
left join fx_storeroominfo r on w.roomid=r.roomid
union all
select '退货' as reportname,
b.id as detailid,
b.lsid as noreg,
c.bookid,
c.bookname,
c.isbn,
c.netprice,
c.author,
c.duty_editor,
c.editiontimes,
c.printtimes,
c.first_receive_date,
b.scale,
b.netamount,
b.discount,
b.amount,
b.ysscale,
b.ysmoney,
b.xsscale,
b.xsmoney,
(c.endprintnum-c.startprintnum+1) as printscalar,
(c.endprintnum-c.startprintnum+1)*c.netprice as printnetamount,
h.editor_house_name,
d.sort_name,
e.clientnum,
e.clientname,
f.province_name,
g.area_name,
a.ywy,
a.modifytime as makedate,
a.sendtime as sendtime,
r.roomid,
r.roomcode,
r.roomname,
2 as state
from fx_returnbookcheck a
inner join fx_returnbookchecklist b on a.lsid = b.lsid
inner join fx_bookinfo c on b.bookid = c.bookid
inner join fx_code_book_sort d on c.booksortid = d.sort_id
inner join fx_clientinfo e on a.clientid = e.clientid
left join fx_code_province f on e.regionid = f.id
left join fx_code_area g on e.areaid = g.area_id
left join fx_code_editor_house h on c.editorhouseid = h.editor_house_id
left join fx_storekwinfo w on b.kwid=w.kwid
left join fx_storeroominfo r on w.roomid=r.roomid
where a.deltf1
and b.deltf1
相应的执行计划如下：
1
Plan hash value: 3976186787
2
3
---------------------------------------------------------------------------------------------------------------
4
| Id| Operation
| Name
| Rows| Bytes |TempSpc| Cost (%CPU)| Time |
5
---------------------------------------------------------------------------------------------------------------
6
| 0 | SELECT STATEMENT
|
|5747K|2737M| | 98419(67)| 00:19:42 |
7
| 1 |UNION-ALL
|
| | | |
|
|
8
|*2 | HASH JOIN RIGHT OUTER|
|1986K| 918M| | 33387 (3)| 00:06:41 |
9
| 3 |TABLE ACCESS FULL | FX_CODE_PROVINCE |31 | 279 | | 3 (0)| 00:00:01 |
10
|*4 |HASH JOIN RIGHT OUTER |
|1986K| 901M| | 33349 (2)| 00:06:41 |
11
| 5 | TABLE ACCESS FULL| FX_CODE_EDITOR_HOUSE |19 | 285 | | 3 (0)| 00:00:01 |
12
|*6 | HASH JOIN RIGHT OUTER|
|1986K| 873M| | 33312 (2)| 00:06:40 |
13
| 7 |TABLE ACCESS FULL | FX_STOREROOMINFO |15 | 855 | |15 (0)| 00:00:01 |
14
|*8 |HASH JOIN RIGHT OUTER |
|1986K| 765M| | 33262 (2)| 00:06:40 |
15
| 9 | TABLE ACCESS FULL| FX_STOREKWINFO |1245 | 82170 | |15 (0)| 00:00:01 |
16
|* 10 | HASH JOIN RIGHT OUTER|
|1986K| 640M| | 33212 (2)| 00:06:39 |
17
|11 |TABLE ACCESS FULL | FX_CODE_AREA | 7 |84 | | 3 (0)| 00:00:01 |
18
|* 12 |HASH JOIN
|
|1986K| 617M| | 33175 (2)| 00:06:39 |
19
|13 | TABLE ACCESS FULL| FX_CLIENTINFO
|2933 | 249K| |57 (2)| 00:00:01 |
20
|* 14 | HASH JOIN
|
|1987K| 452M| | 33083 (2)| 00:06:37 |
21
|15 |
TABLE ACCESS FULL | FX_CODE_BOOK_SORT|31 | 341 | | 3 (0)| 00:00:01 |
22
|* 16 |
HASH JOIN |
|1987K| 432M|1496K| 33046 (2)| 00:06:37 |
23
|17 | TABLE ACCESS FULL| FX_BOOKINFO
| 15275 |1312K| | 143 (3)| 00:00:02 |
24
|* 18 | HASH JOIN
|
|1987K| 265M|11M| 18460 (2)| 00:03:42 |
25
|* 19 |
TABLE ACCESS FULL | FX_WHOLESALERCHECK | 160K|10M| |1272 (3)| 00:00:16 |
26
|* 20 |
TABLE ACCESS FULL | FX_WHOLESALERCHECKLIST |2074K| 146M| |8072 (3)| 00:01:37 |
27
|* 21 | HASH JOIN RIGHT OUTER|
|2119K|1032M| | 37318 (2)| 00:07:28 |
28
|22 |TABLE ACCESS FULL | FX_STOREROOMINFO |15 | 855 | |15 (0)| 00:00:01 |
29
|* 23 |HASH JOIN RIGHT OUTER |
|2119K| 917M| | 37266 (2)| 00:07:28 |
30
|24 | TABLE ACCESS FULL| FX_STOREKWINFO |1245 | 82170 | |15 (0)| 00:00:01 |
31
|* 25 | HASH JOIN RIGHT OUTER|
|2119K| 784M| | 37214 (2)| 00:07:27 |
32
|26 |TABLE ACCESS FULL | FX_CODE_PROVINCE |31 | 279 | | 3 (0)| 00:00:01 |
33
|* 27 |HASH JOIN RIGHT OUTER |
|2119K| 766M| | 37174 (2)| 00:07:27 |
34
|28 | TABLE ACCESS FULL| FX_CODE_AREA | 7 |84 | | 3 (0)| 00:00:01 |
35
|* 29 | HASH JOIN
|
|2119K| 741M| | 37134 (2)| 00:07:26 |
36
|30 |TABLE ACCESS FULL | FX_CLIENTINFO
|2933 | 249K| |57 (2)| 00:00:01 |
37
|* 31 |HASH JOIN
|
|2120K| 566M| | 37041 (2)| 00:07:25 |
38
|32 | TABLE ACCESS FULL| FX_CODE_BOOK_SORT|31 | 341 | | 3 (0)| 00:00:01 |
39
|* 33 | HASH JOIN RIGHT OUTER|
|2120K| 544M| | 37001 (2)| 00:07:25 |
40
|34 |
TABLE ACCESS FULL | FX_CODE_EDITOR_HOUSE |19 | 285 | | 3 (0)| 00:00:01 |
41
|* 35 |
HASH JOIN |
|2120K| 513M|1496K| 36961 (2)| 00:07:24 |
42
|36 | TABLE ACCESS FULL| FX_BOOKINFO
| 15275 |1312K| | 143 (3)| 00:00:02 |
43
|* 37 | HASH JOIN
|
|2120K| 335M|14M| 18801 (2)| 00:03:46 |
44
|* 38 |
HASH JOIN |
| 160K|12M|6280K|2547 (3)| 00:00:31 |
45
|39 |
TABLE ACCESS FULL| FX_DELIVERYCHECK | 160K|4392K| | 431 (4)| 00:00:06 |
46
|40 |
TABLE ACCESS FULL| FX_WHOLESALERCHECK | 168K|9033K| |1267 (3)| 00:00:16 |
47
|41 |
TABLE ACCESS FULL | FX_DELIVERYCHECKLIST |2065K| 163M| |6165 (4)| 00:01:14 |
48
|* 42 | HASH JOIN RIGHT OUTER|
|1641K| 786M| | 27714 (3)| 00:05:33 |
49
|43 |TABLE ACCESS FULL | FX_CODE_PROVINCE |31 | 279 | | 3 (0)| 00:00:01 |
50
|* 44 |HASH JOIN RIGHT OUTER |
|1641K| 771M| | 27682 (3)| 00:05:33 |
51
|45 | TABLE ACCESS FULL| FX_STOREROOMINFO |15 | 855 | |15 (0)| 00:00:01 |
52
|* 46 | HASH JOIN RIGHT OUTER|
|1641K| 682M| | 27638 (2)| 00:05:32 |
53
|47 |TABLE ACCESS FULL | FX_STOREKWINFO |1245 | 82170 | |15 (0)| 00:00:01 |
54
|* 48 |HASH JOIN RIGHT OUTER |
|1641K| 579M| | 27594 (2)| 00:05:32 |
55
|49 | TABLE ACCESS FULL| FX_CODE_AREA | 7 |84 | | 3 (0)| 00:00:01 |
56
|* 50 | HASH JOIN
|
|1641K| 560M| | 27563 (2)| 00:05:31 |
57
|51 |TABLE ACCESS FULL | FX_CLIENTINFO
|2933 | 249K| |57 (2)| 00:00:01 |
58
|* 52 |HASH JOIN RIGHT OUTER |
|1641K| 424M| | 27477 (2)| 00:05:30 |
59
|53 | TABLE ACCESS FULL| FX_CODE_EDITOR_HOUSE |19 | 285 | | 3 (0)| 00:00:01 |
60
|* 54 | HASH JOIN
|
|1641K| 400M| | 27445 (2)| 00:05:30 |
61
|55 |
TABLE ACCESS FULL | FX_CODE_BOOK_SORT|31 | 341 | | 3 (0)| 00:00:01 |
62
|* 56 |
HASH JOIN |
|1641K| 383M|1496K| 27414 (2)| 00:05:29 |
63
|57 | TABLE ACCESS FULL| FX_BOOKINFO
| 15275 |1312K| | 143 (3)| 00:00:02 |
64
|* 58 | HASH JOIN
|
|1641K| 245M|2400K| 14005 (3)| 00:02:49 |
65
|* 59 |
TABLE ACCESS FULL | FX_RETURNBOOKCHECK | 30285 |2040K| | 157 (3)| 00:00:02 |
66
|* 60 |
TABLE ACCESS FULL | FX_RETURNBOOKCHECKLIST |1649K| 138M| |5864 (4)| 00:01:11 |
67
---------------------------------------------------------------------------------------------------------------
68
69
Predicate Information (identified by operation id):
70
---------------------------------------------------
71
72
2 - access("F"."ID"(+)=SYS_OP_C2C("E"."REGIONID"))
73
4 - access("H"."EDITOR_HOUSE_ID"(+)=SYS_OP_C2C("C"."EDITORHOUSEID"))
74
6 - access("W"."ROOMID"="R"."ROOMID"(+))
75
8 - access("B"."KWID"="W"."KWID"(+))
76
10 - access("G"."AREA_ID"(+)=SYS_OP_C2C("E"."AREAID"))
77
12 - access("A"."CLIENTID"="E"."CLIENTID")
78
14 - access("C"."BOOKSORTID"="D"."SORT_ID")
79
16 - access("B"."BOOKID"="C"."BOOKID")
80
18 - access("A"."NOREG"="B"."NOREG")
81
19 - filter("A"."DELTF"1)
82
20 - filter("B"."DELTF"1)
83
21 - access("W"."ROOMID"="R"."ROOMID"(+))
84
23 - access("C"."KWID"="W"."KWID"(+))
85
25 - access("G"."ID"(+)=SYS_OP_C2C("F"."REGIONID"))
86
27 - access("H"."AREA_ID"(+)=SYS_OP_C2C("F"."AREAID"))
87
29 - access("A"."CLIENTID"="F"."CLIENTID")
88
31 - access("D"."BOOKSORTID"="E"."SORT_ID")
89
33 - access("I"."EDITOR_HOUSE_ID"(+)=SYS_OP_C2C("D"."EDITORHOUSEID"))
90
35 - access("C"."BOOKID"="D"."BOOKID")
91
37 - access("B"."DELIVERYID"="C"."DELIVERYID")
92
38 - access("A"."NOREG"="B"."NOREG")
93
42 - access("F"."ID"(+)=SYS_OP_C2C("E"."REGIONID"))
94
44 - access("W"."ROOMID"="R"."ROOMID"(+))
95
46 - access("B"."KWID"="W"."KWID"(+))
96
48 - access("G"."AREA_ID"(+)=SYS_OP_C2C("E"."AREAID"))
97
50 - access("A"."CLIENTID"="E"."CLIENTID")
98
52 - access("H"."EDITOR_HOUSE_ID"(+)=SYS_OP_C2C("C"."EDITORHOUSEID"))
99
54 - access("C"."BOOKSORTID"="D"."SORT_ID")
100
56 - access("B"."BOOKID"="C"."BOOKID")
101
58 - access("A"."LSID"="B"."LSID")
102
59 - filter("A"."DELTF"1)
103
60 - filter("B"."DELTF"1)
直接select * from fx_v_wholesalerdetail t执行时间0.4秒左右，
select count(1) from fx_v_wholesalerdetail t执行时间50秒左右，这个SQL文如何来优化，头大了，谢谢
|
