Optimize insert select query Oracle

问题: Hi i need to optimize below sql query. insert into exa_table (column1, column2, column3, column4) select value1, value2, value3, value4 from (select tb2.ID, tb2.PARCELNO...

问题:

Hi i need to optimize below sql query.

insert into exa_table (column1, column2, column3, column4)
select value1, value2, value3, value4 from 
(select tb2.ID, tb2.PARCELNO, tb2.SHP_ID, tb4.CUST_ID
from exa_table2 tb2 
join table3 tb3 on tb2.ID = tb3.ID 
join table4 tb4 on tb3.ID = tb4.ID 
where tb2.STATUS='1' and tb2.ACTIVE='1' and tb2.DATE >= '20180924' AND tb2.SDATE < '20181024' and 
tb4.STATUS='1' and tb4.ACTIVE='1' and 
not exists (select 1 from exa_table Q where Q.ID = tb2.ID));

i already tried optimize query by adding APPEND NOLOGGING and PARALLEL, like this

insert /*+ APPEND NOLOGGING */ into exa_table (column1, column2, column3, column4)
select value1, value2, value3, value4 from 
(select /*+ PARALLEL(4) */ tb2.ID, tb2.PARCELNO, tb2.SHP_ID, tb4.CUST_ID
from exa_table2 tb2 
join table3 tb3 on tb2.ID = tb3.ID 
join table4 tb4 on tb3.ID = tb4.ID 
where tb2.STATUS='1' and tb2.ACTIVE='1' and tb2.DATE >= '20180924' AND tb2.SDATE < '20181024' and 
tb4.STATUS='1' and tb4.ACTIVE='1' and 
not exists (select 1 from exa_table Q where Q.ID = tb2.ID));

It's much better now but still not enought - took 13 minutes to insert ~100k rows

Explanation plan: enter image description here

Do you have any ideas how to improve query ?


回答1:

Based on the execution plan you are showing the bottleneck is in the SELECT statement. You didn't answer about the selectivity of the select, so I'll assume it's below 5% and, therefore, you should use an index.

I would start by creating the following index:

create index ix1 on exa_table2 (STATUS, ACTIVE, DATE, SDATE, ID);

If the selectivity is low this index will improve the performance. Try it first WITHOUT any parallelism to see how well it performs. Once you find out it's performing well, then you can throw more hardware at it.

Besides, it will be very important to find out about the individual (separate) selectivities for each of the following filtering conditions:

  • STATUS = '1'
  • ACTIVE = '1'
  • DATE >= '20180924'
  • SDATE < '20181024'

The ordering of the columns in the newly created index can be quite significant for the performance of the query, and it greatly depends on those selectivities.

  • 发表于 2019-01-13 08:52
  • 阅读 ( 186 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除