This query make much recursive call [message #287704] |
Thu, 13 December 2007 03:55 |
trantuananh24hg
Messages: 744 Registered: January 2007 Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Hi all!
Take a look to my query, it get much more time for desiring
SELECT a.ma_kh,
a.ten_tt,
a.ma_nv,
a.diachi_tt,
a.ms_thue,
a.so_gt,
b.ma_nv ||' - '|| b.ten_nv ten_nv,
a.sodaidien,
'' tt_chuyentuyen
FROM CCS_BNH.khachhangs_112007 a, CCS_BNH.nhanvien_tcs b
WHERE a.donviql_id = 1
AND a.ma_nv = '01'
AND a.ma_nv = b.ma_nv
AND b.ma_bc = a.ma_bc
And a.ma_kh not in (
Select ma_kh
From CCS_BNH.lichsu_chuyentuyens)
union
SELECT a.ma_kh,
a.ten_tt,
a.ma_nv,
a.diachi_tt,
a.ms_thue,
a.so_gt,
b.ma_nv ||' - '|| b.ten_nv ten_nv,
a.sodaidien,
'Da chuyen tu '||ls_tuyen.ma_nv_cu||' toi '||ls_tuyen.ma_nv_moi tt_chuyentuyen
FROM CCS_BNH.khachhangs_112007 a, CCS_BNH.nhanvien_tcs b,CCS_BNH.lichsu_chuyentuyens ls_tuyen
WHERE a.donviql_id = 1
AND ls_tuyen.ma_nv_moi = '01'
AND a.ma_nv = b.ma_nv
AND b.ma_bc = a.ma_bc
And ls_tuyen.ma_kh=a.ma_kh
/
Traced it with statistic
Quote: |
SQL> /
236 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1576584514
------------------------------------------------------------------------------
----------------------------------------------------------
| Id | Operation | Name | Rows | By
tes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 |
601 | 152 (7)| 00:00:02 | | | |
| 1 | SORT UNIQUE | | 5 |
601 | 152 (7)| 00:00:02 | | | |
| 2 | UNION-ALL | | |
| | | | | |
| 3 | NESTED LOOPS ANTI | | 4 |
476 | 145 (3)| 00:00:02 | | | |
|* 4 | HASH JOIN | | 4 |
436 | 144 (3)| 00:00:02 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID | NHANVIEN_TCS | 8 |
176 | 3 (0)| 00:00:01 | | | |
|* 6 | INDEX RANGE SCAN | PK_NHANVIEN_TC | 8 |
| 1 (0)| 00:00:01 | | | |
|* 7 | TABLE ACCESS FULL | KHACHHANGS_112007 | 244 | 21
228 | 140 (2)| 00:00:02 | | | |
|* 8 | INDEX RANGE SCAN | IDX_LAN | 1 |
10 | 1 (0)| 00:00:01 | | | |
| 9 | PX COORDINATOR | | |
| | | | | |
| 10 | PX SEND QC (RANDOM) | :TQ10002 | 1 |
125 | 6 (17)| 00:00:01 | Q1,02 | P->S | QC (RAND) |
|* 11 | HASH JOIN | | 1 |
125 | 6 (17)| 00:00:01 | Q1,02 | PCWP | |
| 12 | PX RECEIVE | | 2 |
206 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 13 | PX SEND HASH | :TQ10001 | 2 |
206 | 2 (0)| 00:00:01 | Q1,01 | P->P | HASH |
| 14 | NESTED LOOPS | | 2 |
206 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 15 | PX BLOCK ITERATOR | | |
| | | Q1,01 | PCWC | |
|* 16 | TABLE ACCESS FULL | LICHSU_CHUYENTUYENS | 2 |
32 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 17 | TABLE ACCESS BY INDEX ROWID| KHACHHANGS_112007 | 1 |
87 | 1 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 18 | INDEX UNIQUE SCAN | PK_KH_112007 | 1 |
| 0 (0)| 00:00:01 | Q1,01 | PCWP | |
| 19 | BUFFER SORT | | |
| | | Q1,02 | PCWC | |
| 20 | PX RECEIVE | | 251 | 5
522 | 3 (0)| 00:00:01 | Q1,02 | PCWP | |
| 21 | PX SEND HASH | :TQ10000 | 251 | 5
522 | 3 (0)| 00:00:01 | | S->P | HASH |
| 22 | TABLE ACCESS FULL | NHANVIEN_TCS | 251 | 5
522 | 3 (0)| 00:00:01 | | | |
------------------------------------------------------------------------------
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."MA_NV"="B"."MA_NV" AND "B"."MA_BC"="A"."MA_BC")
6 - access("B"."MA_NV"='01')
7 - filter("A"."MA_NV"='01' AND "A"."DONVIQL_ID"=1)
8 - access("A"."MA_KH"="MA_KH")
11 - access("A"."MA_NV"="B"."MA_NV" AND "B"."MA_BC"="A"."MA_BC")
16 - filter("LS_TUYEN"."MA_NV_MOI"='01')
17 - filter("A"."DONVIQL_ID"=1)
18 - access("LS_TUYEN"."MA_KH"="A"."MA_KH")
Statistics
----------------------------------------------------------
655 recursive calls
3 db block gets
1560 consistent gets
0 physical reads
664 redo size
21984 bytes sent via SQL*Net to client
549 bytes received via SQL*Net from client
17 SQL*Net roundtrips to/from client
22 sorts (memory)
0 sorts (disk)
236 rows processed
|
655 recursive calls, it's too much.
|
|
|
|
|
|