|
Оптимизация запросов
#32176123
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
Имею три медленных запроса.Кто-нибудь может дать советы по оптимизации?
1.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
select distinct '|' as CatID,MFNAME from DESCFULLTEXT
union
select distinct CONCAT(p.PID,'|') as CatID,MFNAME from DESCFULLTEXT d1,
(select TRIM(ID) As PID,NVL(NAME,Description) as PDesc, NAME as PDescA from cds_Cctde c, ALTCATNAME a where length(Trim(ID))= 1 AND a.CatID (+) = c.ID) p
where p.PID = SUBSTR(d1.CatID, 1 , 1 )
union
select distinct CONCAT('|',CatID) as CatID,MFNAME from DESCFULLTEXT WHERE CatID != 'XX'
union
select distinct CONCAT(SUBSTR(d2.CatID, 1 , 1 ),CONCAT('|',d2.CatID)) as CatID,d2.MFNAME from DESCFULLTEXT d2
where d2.CatID != 'XX'
ORDER BY CatID,MFNAME;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost= 332 Card= 19682 Bytes
= 237098 )
1 0 SORT (UNIQUE) (Cost= 267 Card= 19682 Bytes= 237098 )
2 1 UNION-ALL
3 2 INDEX (FULL SCAN) OF 'IDX_MFNAME_DESCFULLTEXT' (NON-UN
IQUE) (Cost= 49 Card= 15494 Bytes= 154940 )
4 2 NESTED LOOPS (Cost= 40 Card= 155 Bytes= 3100 )
5 4 NESTED LOOPS (OUTER) (Cost= 2 Card= 1 Bytes= 8 )
6 5 INDEX (FULL SCAN) OF 'PK_CDS_CCTDE' (UNIQUE) (Cost
= 26 Card= 1 Bytes= 4 )
7 5 TABLE ACCESS (FULL) OF 'ALTCATNAME' (Cost= 1 Card= 8
2 Bytes= 328 )
8 4 TABLE ACCESS (FULL) OF 'DESCFULLTEXT' (Cost= 38 Card=
15494 Bytes= 185928 )
9 2 TABLE ACCESS (FULL) OF 'DESCFULLTEXT' (Cost= 38 Card= 15
310 Bytes= 183720 )
10 2 TABLE ACCESS (FULL) OF 'DESCFULLTEXT' (Cost= 38 Card= 15
310 Bytes= 183720 )
2.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
select '|' as MfNAME,PID as CATID, PDesc AS CATNAME from
(select TRIM(ID) As PID,NVL(NAME,Description) as PDesc from cds_Cctde c, ALTCATNAME a where length(Trim(ID))= 1 AND a.CatID (+) = c.ID) p,
descfulltext d
where PID = SUBSTR(d.CatID, 1 , 1 )
union
select distinct CONCAT('|',d.MFNAME)as MfNAME,SUBSTR(d.CatID, 1 , 1 ) as CATID,PDesc AS CATNAME from
(select TRIM(ID) As PID,NVL(NAME,Description) as PDesc from cds_Cctde c, ALTCATNAME a where length(Trim(ID))= 1 AND a.CatID (+) = c.ID) p,
descfulltext d
where PID(+) = SUBSTR(d.CatID, 1 , 1 )
union
select distinct CONCAT(d1.CatID,CONCAT('|',d.MfNAME))as MfNAME, PID as CATID,PDesc AS CATNAME from
(select TRIM(ID) As PID,NVL(NAME,Description) as PDesc from cds_Cctde c, ALTCATNAME a where length(Trim(ID))= 1 AND a.CatID (+) = c.ID) p,
descfulltext d, descfulltext d1
where PID = SUBSTR(d.CatID, 1 , 1 ) and PID= SUBSTR(d1.CatID, 1 , 1 )
union
select distinct CONCAT(d.CatID,'|') as MfNAME, PID as CATID,PDesc AS CATNAME from
(select TRIM(ID) As PID,NVL(NAME,Description) as PDesc from cds_Cctde c, ALTCATNAME a where length(Trim(ID))= 1 AND a.CatID (+) = c.ID) p,
descfulltext d
where PID = SUBSTR(d.CatID, 1 , 1 )
ORDER by MfNAME,CatID;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost= 975 Card= 19603 Bytes
= 1600632 )
1 0 SORT (UNIQUE) (Cost= 672 Card= 19603 Bytes= 1600632 )
2 1 UNION-ALL
3 2 NESTED LOOPS (Cost= 3 Card= 155 Bytes= 14570 )
4 3 NESTED LOOPS (OUTER) (Cost= 2 Card= 1 Bytes= 92 )
5 4 TABLE ACCESS (FULL) OF 'CDS_CCTDE' (Cost= 1 Card= 1
Bytes= 46 )
6 4 TABLE ACCESS (FULL) OF 'ALTCATNAME' (Cost= 1 Card= 8
2 Bytes= 3772 )
7 3 INDEX (FULL SCAN) OF 'IDX_CATID_DESCFULLTEXT' (NON-U
NIQUE) (Cost= 4 Card= 15494 Bytes= 30988 )
8 2 HASH JOIN (OUTER) (Cost= 45 Card= 15494 Bytes= 883158 )
9 8 TABLE ACCESS (FULL) OF 'DESCFULLTEXT' (Cost= 38 Card=
15494 Bytes= 185928 )
10 8 VIEW (Cost= 2 Card= 1 Bytes= 45 )
11 10 NESTED LOOPS (OUTER) (Cost= 2 Card= 1 Bytes= 92 )
12 11 TABLE ACCESS (FULL) OF 'CDS_CCTDE' (Cost= 1 Card=
1 Bytes= 46 )
13 11 TABLE ACCESS (FULL) OF 'ALTCATNAME' (Cost= 1 Card
= 82 Bytes= 3772 )
14 2 HASH JOIN (Cost= 42 Card= 24016 Bytes= 2545696 )
15 14 NESTED LOOPS (Cost= 3 Card= 155 Bytes= 14570 )
16 15 NESTED LOOPS (OUTER) (Cost= 2 Card= 1 Bytes= 92 )
17 16 TABLE ACCESS (FULL) OF 'CDS_CCTDE' (Cost= 1 Card=
1 Bytes= 46 )
18 16 TABLE ACCESS (FULL) OF 'ALTCATNAME' (Cost= 1 Card
= 82 Bytes= 3772 )
19 15 INDEX (FULL SCAN) OF 'IDX_CATID_DESCFULLTEXT' (NON
-UNIQUE) (Cost= 4 Card= 15494 Bytes= 30988 )
20 14 TABLE ACCESS (FULL) OF 'DESCFULLTEXT' (Cost= 38 Card=
15494 Bytes= 185928 )
21 2 NESTED LOOPS (Cost= 3 Card= 155 Bytes= 14570 )
22 21 NESTED LOOPS (OUTER) (Cost= 2 Card= 1 Bytes= 92 )
23 22 TABLE ACCESS (FULL) OF 'CDS_CCTDE' (Cost= 1 Card= 1
Bytes= 46 )
24 22 TABLE ACCESS (FULL) OF 'ALTCATNAME' (Cost= 1 Card= 8
2 Bytes= 3772 )
25 21 INDEX (FULL SCAN) OF 'IDX_CATID_DESCFULLTEXT' (NON-U
NIQUE) (Cost= 4 Card= 15494 Bytes= 30988 )
3.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
select distinct '|' as MfNAME, CID as CATID,CDesc AS CATNAME from
(select TRIM(ID) AS CID,NVL(NAME,Description) as CDesc from cds_Cctde c1, ALTCATNAME a1 where exists (SELECT 1 from descfulltext where c1.ID=CatID) AND a1.CatID (+) = c1.ID) c
union
select distinct CONCAT('|',SUBSTR(CID, 1 , 1 )) as MfNAME,CID as CATID,CDesc AS CATNAME from
(select TRIM(ID) AS CID,NVL(NAME,Description) as CDesc from cds_Cctde c1, ALTCATNAME a1 where exists (SELECT 1 from descfulltext where c1.ID=CatID) AND a1.CatID (+) = c1.ID) c
union
select distinct CONCAT(d.MFNAME,CONCAT('|',SUBSTR(CID, 1 , 1 ))) as MFNAME,CID as CATID,CDesc AS CATNAME from
(select TRIM(ID) AS CID,NVL(NAME,Description) as CDesc from cds_Cctde c1, ALTCATNAME a1 where exists (SELECT 1 from descfulltext where c1.ID=CatID) AND a1.CatID (+) = c1.ID) c,
descfulltext d
where CID = d.CatID
union
select distinct CONCAT(d.MFNAME,'|') as MFNAME, d.CATID, CDesc as CATNAME from
(select TRIM(ID) AS CID,NVL(NAME,Description) as CDesc from cds_Cctde c1, ALTCATNAME a1 where exists (SELECT 1 from descfulltext where c1.ID=CatID) AND a1.CatID (+) = c1.ID) c,
descfulltext d
where CID (+) = d.CatID
ORDER BY MFNAME,CatID;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost= 83494 Card= 35255 Byt
es= 2957692 )
1 0 SORT (UNIQUE) (Cost= 83145 Card= 35255 Bytes= 2957692 )
2 1 UNION-ALL
3 2 NESTED LOOPS (Cost= 4 Card= 12705 Bytes= 1194270 )
4 3 HASH JOIN (OUTER) (Cost= 3 Card= 82 Bytes= 7544 )
5 4 TABLE ACCESS (FULL) OF 'CDS_CCTDE' (Cost= 1 Card= 82
Bytes= 3772 )
6 4 TABLE ACCESS (FULL) OF 'ALTCATNAME' (Cost= 1 Card= 8
2 Bytes= 3772 )
7 3 INDEX (RANGE SCAN) OF 'IDX_CATID_DESCFULLTEXT' (NON-
UNIQUE)
8 2 NESTED LOOPS (Cost= 4 Card= 12705 Bytes= 1194270 )
9 8 HASH JOIN (OUTER) (Cost= 3 Card= 82 Bytes= 7544 )
10 9 TABLE ACCESS (FULL) OF 'CDS_CCTDE' (Cost= 1 Card= 82
Bytes= 3772 )
11 9 TABLE ACCESS (FULL) OF 'ALTCATNAME' (Cost= 1 Card= 8
2 Bytes= 3772 )
12 8 INDEX (RANGE SCAN) OF 'IDX_CATID_DESCFULLTEXT' (NON-
UNIQUE)
13 2 HASH JOIN (Cost= 48 Card= 1968513 Bytes= 208662378 )
14 13 TABLE ACCESS (FULL) OF 'DESCFULLTEXT' (Cost= 38 Card=
15494 Bytes= 185928 )
15 13 NESTED LOOPS (Cost= 4 Card= 12705 Bytes= 1194270 )
16 15 HASH JOIN (OUTER) (Cost= 3 Card= 82 Bytes= 7544 )
17 16 TABLE ACCESS (FULL) OF 'CDS_CCTDE' (Cost= 1 Card=
82 Bytes= 3772 )
18 16 TABLE ACCESS (FULL) OF 'ALTCATNAME' (Cost= 1 Card
= 82 Bytes= 3772 )
19 15 INDEX (RANGE SCAN) OF 'IDX_CATID_DESCFULLTEXT' (NO
N-UNIQUE)
20 2 HASH JOIN (OUTER) (Cost= 58 Card= 1968513 Bytes= 11220524
1 )
21 20 TABLE ACCESS (FULL) OF 'DESCFULLTEXT' (Cost= 47 Card=
15494 Bytes= 185928 )
22 20 VIEW (Cost= 4 Card= 12705 Bytes= 571725 )
23 22 NESTED LOOPS (Cost= 4 Card= 12705 Bytes= 1194270 )
24 23 HASH JOIN (OUTER) (Cost= 3 Card= 82 Bytes= 7544 )
25 24 TABLE ACCESS (FULL) OF 'CDS_CCTDE' (Cost= 1 Car
d= 82 Bytes= 3772 )
26 24 TABLE ACCESS (FULL) OF 'ALTCATNAME' (Cost= 1 Ca
rd= 82 Bytes= 3772 )
27 23 INDEX (RANGE SCAN) OF 'IDX_CATID_DESCFULLTEXT' (
NON-UNIQUE)
|
|
|
