Thanks for the question, Wang.
Asked: June 02, 2016 - 3:41 am UTC
Last updated: June 02, 2016 - 3:56 am UTC
Version: 11.2.0.3
Viewed 1000+ times
You Asked
Hi Tom,
I have a sql as below:
INSERT INTO tt_date_2
with src as (select id,MIN(yr_min_date) yr_min_date,MIN(prev_min_date) prev_min_date
from tt_date
group by id)
select DISTINCT d1.id , d1.yr_min_date , d1.yr_n , d1.yr_g , 0 , d2.prev_min_date , d2.prev_n , d2.prev_g , 0
from tt_date d1 , tt_date d2, src
where src.id = d1.id
and src.yr_min_date = d1.yr_min_date
and src.id = d2.id
and src.prev_min_date = d2.prev_min_date;
Actually there only about 1000 rows to insert, however this sql is hang seems has some wait. Then I move the with clause to subquery, just as below:
INSERT INTO tt_date_2
select DISTINCT d1.id , d1.yr_min_date , d1.yr_n , d1.yr_g , 0 , d2.prev_min_date , d2.prev_n , d2.prev_g , 0
from tt_date d1 , tt_date d2, (select id,MIN(yr_min_date) yr_min_date,MIN(prev_min_date) prev_min_date from tt_date group by id)src
where src.id = d1.id
and src.yr_min_date = d1.yr_min_date
and src.id = d2.id
and src.prev_min_date = d2.prev_min_date;
And it quickly finished. I had run the explain plan, both two sqls have same execution plan. I don't know why the first sql has with clause will hang. Could you please help look into this? Thanks very much.
Tiger
and Connor said...
I'm looking into my psychic crystal ball....and....nope, I cant see your executions plans :-)
You might want to paste them in here (with the code tags please) via Review
And try this for each insert:
INSERT /*+ gather_plan_statistics */ INTO tt_date_2
select ....
and after each one, run:
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'))
You can paste that here in code tags as well if you like via Review.
Is this answer out of date? If it is, please let us know via a Comment