| Performance on DB Link [message #129261] |
Fri, 22 July 2005 16:39  |
scottwmackey
Messages: 515
Registered: March 2005
|
Senior Member |
|
|
I am getting some strange behavior trying to insert some records from a remote database and I was wondering if anybody had any insights.
This query runs in about 7 seconds, which is perfectly fine for my requirements.
SQL> SELECT /*+DRIVING_SITE(rt)*/
2 abs_relation_cd,
3 entity_1_id,
4 entity_2_id,
5 is_active,
6 relation_id
7 FROM fe_relation@stagingc.corp.finaplex.com rt,
8 (SELECT DISTINCT local_key
9 FROM (SELECT definer_id local_key
10 FROM fi_instr_master
11 UNION
12 SELECT ins_id local_key
13 FROM fi_instr_master
14 UNION
15 SELECT upd_id local_key
16 FROM fi_instr_master
17 UNION
18 SELECT ins_id local_key
19 FROM fi_market_data
20 UNION
21 SELECT upd_id local_key
22 FROM fi_market_data
23 MINUS
24 SELECT relation_id FROM fe_relation)
25 WHERE local_key IS NOT NULL) lt
26 WHERE rt.relation_id = lt.local_key;
ABS_RELATION_CD ENTITY_1_ID ENTITY_2_ID IS_ACTIVE RELATION_ID
---------------- ------------ ------------- --------- -------------
FPX_APP_ROOT 1 2 1 1
FPX_APP_BATCH 1 3 1 2
CLIENT 4 841375 1 13048692
CLIENT 4 910670 1 39083577
CLIENT 4 915431 1 39099762
CLIENT 4 1029713 1 39569712
CLIENT 4 1030820 1 39573929
7 rows selected
This block runs in about 7 seconds
SQL> BEGIN
2 FOR r IN (SELECT /*+DRIVING_SITE(rt)*/
3 abs_relation_cd,
4 entity_1_id,
5 entity_2_id,
6 is_active,
7 relation_id
8 FROM fe_relation@stagingc.corp.finaplex.com rt,
9 (SELECT DISTINCT local_key
10 FROM (SELECT definer_id local_key
11 FROM fi_instr_master
12 UNION
13 SELECT ins_id local_key
14 FROM fi_instr_master
15 UNION
16 SELECT upd_id local_key
17 FROM fi_instr_master
18 UNION
19 SELECT ins_id local_key
20 FROM fi_market_data
21 UNION
22 SELECT upd_id local_key
23 FROM fi_market_data
24 MINUS
25 SELECT relation_id FROM fe_relation)
26 WHERE local_key IS NOT NULL) lt
27 WHERE rt.relation_id = lt.local_key)
28 LOOP
29 INSERT INTO fe_relation (abs_relation_cd, entity_1_id, entity_2_id, is_active, relation_id) VALUES (r.abs_relation_cd, r.entity_1_id, r.entity_2_id, r.is_active, r.relation_id);
30 END LOOP;
31 END;
32 /
PL/SQL procedure successfully completed
But this doesn't ever seem to complete.
SQL> INSERT INTO fe_relation
2 (abs_relation_cd,
3 entity_1_id,
4 entity_2_id,
5 is_active,
6 relation_id)
7 SELECT /*+DRIVING_SITE(rt)*/
8 abs_relation_cd,
9 entity_1_id,
10 entity_2_id,
11 is_active,
12 relation_id
13 FROM fe_relation@stagingc.corp.finaplex.com rt,
14 (SELECT DISTINCT local_key
15 FROM (SELECT definer_id local_key
16 FROM fi_instr_master
17 UNION
18 SELECT ins_id local_key
19 FROM fi_instr_master
20 UNION
21 SELECT upd_id local_key
22 FROM fi_instr_master
23 UNION
24 SELECT ins_id local_key
25 FROM fi_market_data
26 UNION
27 SELECT upd_id local_key
28 FROM fi_market_data
29 MINUS
30 SELECT relation_id FROM fe_relation)
31 WHERE local_key IS NOT NULL) lt
32 WHERE rt.relation_id = lt.local_key;
Before anybody gets too far into asking about table statistics and explain plans, please note that the select statement is exactly the same in each of the cases. As a cursor loop, it works just fine. Why does anything change when I use it as a subquery in an insert statement and how can I prevent whatever change is happening?
Thanks,
Scott
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
