Speed up query with analytic
From: bob123 <bob123_at_gmail.com>
Date: Mon, 2 Aug 2010 21:09:24 +0200
Message-ID: <4c5717e4$0$23925$426a74cc_at_news.free.fr>
Hi,
37 - filter("T82157"."LIBCAT"<>'Non spécifié') 40 - access("T82233"."MOIS"='2010 / 06')
s)(object id 270086)
3040800 TABLE ACCESS BY INDEX ROWID FAI_OLTP_DIS_CATCLE (cr=912001 pr=0 pw=0 time=8614476
us)
3040800 BITMAP CONVERSION TO ROWIDS (cr=813120 pr=0 pw=0 time=5571660 us)
us)
970 us)(object id 270086)
s)(object id 274808)
us)(object id 274809)
1266 us)(object id 274812)
d 39121) Received on Mon Aug 02 2010 - 14:09:24 CDT
Date: Mon, 2 Aug 2010 21:09:24 +0200
Message-ID: <4c5717e4$0$23925$426a74cc_at_news.free.fr>
Hi,
how to speed up this query ?
below:
- query
- explain plan
- tkprof
Thanks in advance
Oracle 10.2.0.4
SELECT SUM (T83717.IND) AS c1,
T82128.LIBCAT AS c2,
T82171.LIBPDT AS c3,
CONCAT (T82128.LIBCAT, T82171.LIBPDT) AS c4,
T82128.NUMCAT AS c5
FROM DMS_OLTP_DIS_CAT T82128 /* Dim_DMS_OLTP_DIS_CAT
*/
, DMS_OLTP_DIS_PDT T82171 /*
Dim_DMS_OLTP_DIS_PDT */
, ASS_DIS_CAT_PDT
T83717 /* Ass_ASS_DIS_CAT_PDT */
WHERE ( T82128.NUMCAT = T83717.NUMCAT AND T82171.NUMPDT = T83717.NUMPDT AND T82171.FLGPDTPRES = 'Y' AND T82128.LIBCAT <> 'Non spécifié'
AND CONCAT (T82128.LIBCAT, T82171.LIBPDT) NOT IN
(SELECT DISTINCT D1.c3 AS c1
FROM (SELECT D1.c1 AS c1,
D1.c2 AS c2,
D1.c3 AS c3,
D1.c6 AS c6,
D1.c7 AS c7,
D1.c8 AS c8
FROM (SELECT T82157.LIBCAT AS c1,
T82157.LIBCLE AS c2,
CONCAT (T82157.LIBCAT,
T82171.LIBPDT)
AS c3,
T82171.LIBPDT AS c6,
T82157.NUMCAT AS c7,
T82157.NUMCATCLE AS c8,
ROW_NUMBER ()
OVER (
PARTITION BY
T82157.NUMCATCLE,
T82157.NUMCAT,
T82157.LIBCAT,
T82157.LIBCLE,
T82171.LIBPDT
ORDER BY
T82157.NUMCATCLE
ASC,
T82157.NUMCAT
ASC,
T82157.LIBCAT
ASC,
T82157.LIBCLE
ASC,
T82171.LIBPDT
ASC
)
AS c9
FROM DMS_OLTP_DIS_HIE_CAT_CLE
T82157 /* Dim_DMS_OLTP_DIS_HIE_CAT_CLE */
,
DMS_OLTP_DIS_PDT T82171
/* Dim_DMS_OLTP_DIS_PDT */
,
DMS_OLTP_DIS_HIE_PAYS
T82163 /* Dim_DMS_OLTP_DIS_HIE_PAYS */
,
DMS_OBIEE_JOUR T82233 /*
Dim_DMS_OBIEE_JOUR (Date arrivee DIS) */
,
FAI_OLTP_DIS_CATCLE
T82212 /* Faits_FAI_OLTP_DIS_CATCLE */
WHERE (T82157.NUMCATCLE =
T82212.NUMCATCLE
AND T82163.NUMPAYS =
T82212.NUMPAYS
AND T82171.NUMPDT =
T82212.NUMPDT
AND T82163.LIBGRP =
'France,
métropole'
AND T82171.FLGPDTPRES =
'Y'
AND T82212.DTARRDIS =
T82233.NUMJOUR
AND T82157.LIBCAT <>
'Non spécifié'
AND T82233.MOIS <=
'2010 / 06'
AND (T82233.MOIS >=
CONCAT (
CONCAT (
CAST (
T82233.EXR
- 1 AS
VARCHAR (4)
),
' / '
),
'10'
)
OR T82233.MOIS >=
'2010 / 06')
AND T82233.EXR IN
(SELECT
DISTINCT
T82233.EXR
AS
c1
FROM
DMS_OBIEE_JOUR T82233 /* Dim_DMS_OBIEE_JOUR (Date arrivee DIS) */
WHERE
(T82233.MOIS =
'2010
/ 06'))))
D1
WHERE (D1.c9 = 1)) D1)
)
GROUP BY T82128.NUMCAT, T82128.LIBCAT, T82171.LIBPDT, CONCAT (T82128.LIBCAT, T82171.LIBPDT)
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 120 | 7080 | 810 (7)| 00:00:10 | | 1 | HASH GROUP BY | | 120 | 7080 | 810 (7)| 00:00:10 | |* 2 | FILTER | | | | | | |* 3 | HASH JOIN | | 230 | 13570 | 8 (13)| 00:00:01 | |* 4 | TABLE ACCESS FULL | DMS_OLTP_DIS_CAT | 20 | 460 | 2 (0)| 00:00:01 | |* 5 | HASH JOIN | | 230 | 8280 | 6 (17)| 00:00:01 | |* 6 | TABLE ACCESS FULL | DMS_OLTP_DIS_PDT | 12 | 204 | 2 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | ASS_DIS_CAT_PDT | 580 | 11020 | 3 (0)| 00:00:01 | |* 8 | VIEW | | 1 | 1025 | 67 (6)| 00:00:01 | |* 9 | WINDOW SORT PUSHED RANK | | 1 | 193 | 67 (6)| 00:00:01 | |* 10 | HASH JOIN SEMI | | 1 | 193 | 66 (5)| 00:00:01 | |* 11 | HASH JOIN | | 1 | 179 | 57 (6)| 00:00:01 | |* 12 | HASH JOIN | | 1 | 103 | 47 (5)| 00:00:01 | |* 13 | HASH JOIN | | 1 | 83 | 32 (4)| 00:00:01 | |* 14 | HASH JOIN | | 1 | 66 | 30 (4)| 00:00:01 | | 15 | TABLE ACCESS BY INDEX ROWID | DMS_OLTP_DIS_HIE_PAYS | 1 | 16 | 1 (0)| 00 | 16 | BITMAP CONVERSION TO ROWIDS | | | | | | |* 17 | BITMAP INDEX SINGLE VALUE | DMS_OLTP_DIS_HIE_PAYS_B3 | | | | | | 18 | TABLE ACCESS BY INDEX ROWID | FAI_OLTP_DIS_CATCLE | 28 | 1400 | 28 (0)| 00 | 19 | BITMAP CONVERSION TO ROWIDS | | | | | | | 20 | BITMAP AND | | | | | | | 21 | BITMAP MERGE | | | | | | | 22 | BITMAP KEY ITERATION | | | | | | | 23 | TABLE ACCESS BY INDEX ROWID | DMS_OLTP_DIS_HIE_PAYS | 1 | 16 | 1 (0)| | 24 | BITMAP CONVERSION TO ROWIDS| | | | | | |* 25 | BITMAP INDEX SINGLE VALUE | DMS_OLTP_DIS_HIE_PAYS_B3 | | | | | |* 26 | BITMAP INDEX RANGE SCAN | FAI_OLTP_DIS_CATCLE_B10 | | | | | 27 | BITMAP MERGE | | | | | | | 28 | BITMAP KEY ITERATION | | | | | | |* 29 | TABLE ACCESS FULL | DMS_OLTP_DIS_PDT | 12 | 204 | 2 (0)| 00:00:01 |* 30 | BITMAP INDEX RANGE SCAN | FAI_OLTP_DIS_CATCLE_B11 | | | | | 31 | BITMAP MERGE | | | | | | | 32 | BITMAP KEY ITERATION | | | | | | |* 33 | TABLE ACCESS FULL | DMS_OBIEE_JOUR | 781 | 34364 | 14 (0)| 00:00:01 | |* 34 | BITMAP INDEX RANGE SCAN | FAI_OLTP_DIS_CATCLE_B14 | | | | |* 35 | TABLE ACCESS FULL | DMS_OLTP_DIS_PDT | 12 | 204 | 2 (0)| 00:00:01 | |* 36 | TABLE ACCESS FULL | DMS_OBIEE_JOUR | 781 | 15620 | 14 (0)| 00:00:01 | |* 37 | TABLE ACCESS FULL | DMS_OLTP_DIS_HIE_CAT_CLE | 2542 | 188K| 9 (0)| 00:00:0 | 38 | TABLE ACCESS BY INDEX ROWID | DMS_OBIEE_JOUR | 30 | 420 | 9 (0)| 00: | 39 | BITMAP CONVERSION TO ROWIDS | | | | | | |* 40 | BITMAP INDEX SINGLE VALUE | DMS_OBIEE_JOUR_B4 | | | | | ----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM (SELECT /*+ NO_EXPAND ???)
3 - access("T82128"."NUMCAT"="T83717"."NUMCAT")
4 - filter("T82128"."LIBCAT"<>'Non spécifié')
5 - access("T82171"."NUMPDT"="T83717"."NUMPDT")
6 - filter("T82171"."FLGPDTPRES"='Y')
8 - filter("D1"."C9"=1 AND LNNVL("D1"."C3"<>:B1||:B2))
9 - filter(ROW_NUMBER() OVER ( PARTITION BY
"T82157"."NUMCATCLE","T82157"."NUMCAT","T82157"."LIBC
"."LIBCLE","T82171"."LIBPDT" ORDER BY
"T82157"."NUMCATCLE","T82157"."NUMCAT","T82157"."LIBCAT
","T82171"."LIBPDT")<=1)
10 - access("T82233"."EXR"="T82233"."EXR")
11 - access("T82157"."NUMCATCLE"="T82212"."NUMCATCLE")
12 - access("T82212"."DTARRDIS"="T82233"."NUMJOUR")
13 - access("T82171"."NUMPDT"="T82212"."NUMPDT")
14 - access("T82163"."NUMPAYS"="T82212"."NUMPAYS")
17 - access("T82163"."LIBGRP"='France, métropole')
25 - access("T82163"."LIBGRP"='France, métropole')
26 - access("T82212"."NUMPAYS"="T82163"."NUMPAYS")
29 - filter("T82171"."FLGPDTPRES"='Y')
30 - access("T82212"."NUMPDT"="T82171"."NUMPDT")
33 - filter("T82233"."MOIS"<='2010 / 06' AND ("T82233"."MOIS">='2010 / 06'
OR
"T82233"."MOIS">=CAST("T82233"."EXR"-1 AS VARCHAR (4)
)||' / '||'10'))
34 - access("T82212"."DTARRDIS"="T82233"."NUMJOUR")
35 - filter("T82171"."FLGPDTPRES"='Y')
36 - filter("T82233"."MOIS"<='2010 / 06' AND ("T82233"."MOIS">='2010 / 06'
OR
"T82233"."MOIS">=CAST("T82233"."EXR"-1 AS VARCHAR (4)
)||' / '||'10'))
37 - filter("T82157"."LIBCAT"<>'Non spécifié') 40 - access("T82233"."MOIS"='2010 / 06')
Note
- star transformation used for this statement
84 rows selected.
Rows Row Source Operation
------- ---------------------------------------------------
46 HASH GROUP BY (cr=937453 pr=89625 pw=89625 time=37407099 us)
46 FILTER (cr=937453 pr=89625 pw=89625 time=68635377 us)
240 HASH JOIN (cr=11 pr=0 pw=0 time=7661 us)
20 TABLE ACCESS FULL DMS_OLTP_DIS_CAT (cr=3 pr=0 pw=0 time=98 us)
240 HASH JOIN (cr=8 pr=0 pw=0 time=3183 us)
12 TABLE ACCESS FULL DMS_OLTP_DIS_PDT (cr=3 pr=0 pw=0 time=45 us)
580 TABLE ACCESS FULL ASS_DIS_CAT_PDT (cr=5 pr=0 pw=0 time=1395 us)
194 VIEW (cr=937442 pr=89625 pw=89625 time=72125019 us)
1200032 WINDOW SORT PUSHED RANK (cr=937442 pr=89625 pw=89625
time=70024357 us)
1981920 HASH JOIN SEMI (cr=937442 pr=89625 pw=89625 time=54332544 us)
3040800 HASH JOIN (cr=936722 pr=50190 pw=50190 time=43477774 us)
3040800 HASH JOIN (cr=928081 pr=25095 pw=25095 time=33614607 us)
3040800 HASH JOIN (cr=913441 pr=7170 pw=7170 time=21112131 us)
3040800 HASH JOIN (cr=912721 pr=0 pw=0 time=14736411 us)
240 TABLE ACCESS BY INDEX ROWID DMS_OLTP_DIS_HIE_PAYS (cr=720
pr=0 pw=0 time=10350 us)
240 BITMAP CONVERSION TO ROWIDS (cr=480 pr=0 pw=0 time=7945
us)
240 BITMAP INDEX SINGLE VALUE DMS_OLTP_DIS_HIE_PAYS_B3
(cr=480 pr=0 pw=0 time=6601 u
s)(object id 270086)
3040800 TABLE ACCESS BY INDEX ROWID FAI_OLTP_DIS_CATCLE (cr=912001 pr=0 pw=0 time=8614476
us)
3040800 BITMAP CONVERSION TO ROWIDS (cr=813120 pr=0 pw=0 time=5571660 us)
240 BITMAP AND (cr=813120 pr=0 pw=0 time=5570445 us)
240 BITMAP MERGE (cr=1200 pr=0 pw=0 time=70501 us)
240 BITMAP KEY ITERATION (cr=1200 pr=0 pw=0 time=9757 us)
240 TABLE ACCESS BY INDEX ROWID DMS_OLTP_DIS_HIE_PAYS
(cr=720 pr=0 pw=0 time=5203
us)
240 BITMAP CONVERSION TO ROWIDS (cr=480 pr=0 pw=0
time=3756 us)
240 BITMAP INDEX SINGLE VALUE DMS_OLTP_DIS_HIE_PAYS_B3
(cr=480 pr=0 pw=0 time=2
970 us)(object id 270086)
240 BITMAP INDEX RANGE SCAN FAI_OLTP_DIS_CATCLE_B10
(cr=480 pr=0 pw=0 time=3671 u
s)(object id 274808)
240 BITMAP MERGE (cr=7200 pr=0 pw=0 time=152343 us)
2880 BITMAP KEY ITERATION (cr=7200 pr=0 pw=0 time=48007
us)
2880 TABLE ACCESS FULL DMS_OLTP_DIS_PDT (cr=720 pr=0 pw=0
time=12462 us)
2880 BITMAP INDEX RANGE SCAN FAI_OLTP_DIS_CATCLE_B11
(cr=6480 pr=0 pw=0 time=31566
us)(object id 274809)
240 BITMAP MERGE (cr=804720 pr=0 pw=0 time=5300759 us)
93120 BITMAP KEY ITERATION (cr=804720 pr=0 pw=0
time=5089904 us)
394080 TABLE ACCESS FULL DMS_OBIEE_JOUR (cr=14640 pr=0 pw=0
time=1254964 us)
93120 BITMAP INDEX RANGE SCAN FAI_OLTP_DIS_CATCLE_B14
(cr=790080 pr=0 pw=0 time=330
1266 us)(object id 274812)
2880 TABLE ACCESS FULL DMS_OLTP_DIS_PDT (cr=720 pr=0 pw=0
time=11751 us)
394080 TABLE ACCESS FULL DMS_OBIEE_JOUR (cr=14640 pr=0 pw=0
time=870582 us)
610080 TABLE ACCESS FULL DMS_OLTP_DIS_HIE_CAT_CLE (cr=8641 pr=0 pw=0
time=10806 us)
7200 TABLE ACCESS BY INDEX ROWID DMS_OBIEE_JOUR (cr=720 pr=0 pw=0 time=24260 us)
7200 BITMAP CONVERSION TO ROWIDS (cr=240 pr=0 pw=0 time=7564 us)
240 BITMAP INDEX SINGLE VALUE DMS_OBIEE_JOUR_B4 (cr=240 pr=0
pw=0 time=5612 us)(object i
d 39121) Received on Mon Aug 02 2010 - 14:09:24 CDT