sorry, you lacked enough information
rati todua, September 30, 2016 - 8:39 am UTC
i haven't wrote all i knew about this so i will now.
you are right but i think there is something more there than rollback.
this insert of mine used select, which took data from another database with db_link, and inserted it on current db. the thing is, i tried the same sql statement(with same sequence and everything on that another db and it returned error quickly.
then i tried to execute them both with and without link, but this time without sequence and it returned error in 1 sec.
so the thing is, when i execute
(insert into t select seq.NEXTVAL as id, * from t2@dblink)
if it returns error, it returns it in like 20-30 min, while query itself is executed in 1 sec, and if it is succefull it inserts in 1 sec (i do not think rollback would slow down it that much cause it is like 8600 rows and i can see from sequences that error is on 120 row and it is standing there all time).
and when i do statement like:
(insert into t select seq.NEXTVAL as id, * from t2(without link)) it executes in 1 sec weather it returns error or ends succefully.
so something bad is happening when i use both sequence and db_link at the same time or what?
my db_s use different gateway which one uses 50.1 and other 10.1 can this be a problem for db_link to act like this?
but again when there is no sequence db_link works fine.
i will write my insert statement perhaps it will help:
insert into house_address_test
select TEST_HOUSE_ADDRES_SEQ.NEXTVAL as address_id, node_id, STREETS_ID, DOM, decode(REGEXP_COUNT(dom, ';'), 0, 0, 1) as building, null as cadastral_code, null as note
from ( select node_id, STREETS_ID, DOM, count(*) as raod from
( select m1.kross_id, k.node_id, m1.STREETS_ID, m1.streets_name, m1.DOM from (select kross_id, STREETS_ID, streets_name,
DOM from VI_MEXI@PRIM_LINK where inforce_id=0 and dom_box_id is not null and dom_box_id>0 and streets_name not like 'ქალაქი%') m1
left join KROSS@PRIM_LINK k on m1.kross_id=k.kross_id union all select m2.kross_id, null as node_id, m2.STREETS_ID, m2.streets_name, m2.DOM
from VI_MEXI@PRIM_LINK m2 where inforce_id=0 and m2.dom_box_id is not null and m2.dom_box_id>0 and streets_name like 'ქალაქი%' ) group by node_id, STREETS_ID, DOM )
thank you very much for your help
September 30, 2016 - 8:58 am UTC
That final SQL looks to be a lot more than just "insert into select from one_table".
Anyway, lets try explore workarounds in the interim. If you get errors returned quickly when you do not use a sequence, perhaps break the task down into 2 steps:
1) get the remote data
insert into a_global_temporary_table
select ... from table@remote, ...
2) assign the sequence locally
insert into my_real_target_table
select seq.nextval, g.*
from a_global_temporary_table g
nice workaround
rati todua, September 30, 2016 - 12:35 pm UTC
well, that is very simple and works nice. perfect solution i'd say.
thank you, i will use and remember it.
than agian i am still wondering why would such insert work slow.
it doesn't even react when i press "stop current task" in sql navigator, i am forced to kill it or wait for it to return error in 20 minutes...
well, thanks for your responce. i will post if i will find a reason behind this...
problem is in network?
rati todua, September 30, 2016 - 12:49 pm UTC
i just tested exactly the same insert, but now both remote and current db were from one net within one gateway 10.1 and it returned error in 1 sec, while previous two dbs had 10.1 and 50.1 gateway... never thought network would result in insert statement like this...