I have been working with an application that is integrated with spring and Hibernate 4.X.X and its transaction is managed by JTA in Weblogic application server. After 3 years, there are about 40 million records only into one table from 100 tables that exist in my DB. The DB is Oracle 11g. The response time of a query is about 5 minutes because of increasing the count of records of this tables. I customized the query and put it into SQL Developer and run the query advisor plan for suggestion some Index. Totally after doing such this, its response time is reduced to 2 minutes. But even so, this response time does not satisfy the Customer. To further clarify I put the query, It is as following:
select * from (select (count(storehouse0_.ID) over()) as col_0_0_, storehouse3_.storeHouse_ID as col_1_0_, (DBPK_PUB_STOREHOUSE.get_Storehouse_Title(storehouse5_.id, 1)) as col_2_0_, storehouse5_.Organization_Code as col_3_0_, publicgood1_.Goods_Item_Id as col_4_0_, storehouse0_.storeHouse_Inventory_Id as col_5_0_, storehouse0_.Id as col_6_0_, storehouse3_.samapel_Item_Id as col_7_0_, samapelite10_.MAINNAME as col_8_0_, publicgood1_.serial_Number as col_9_0_, publicgood1_1_.production_Year as col_10_0_, samapelpar2_.ID_SourceInfo as col_11_0_, samapelpar2_.Pn as col_12_0_, storehouse3_.expire_Date as col_13_0_, publicgood1_1_.Status_Id as col_14_0_, baseinform12_.Topic as col_15_0_, publicgood1_.public_Num as col_16_0_, cast(publicgood1_1_.goods_Status as number(10, 0)) as col_17_0_, publicgood1_1_.goods_Status as col_18_0_, publicgood1_1_.deleted as col_19_0_ from amd.Core_StoreHouse_Inventory_Item storehouse0_, amd.Core_STOREHOUSE_INVENTORY storehouse3_, amd.Core_STOREHOUSE storehouse5_, amd.SMP_SAMAPEL_CODE samapelite10_ cross join amd.Core_Goods_Item_Public publicgood1_ inner join amd.Core_Goods_Item publicgood1_1_ on publicgood1_.Goods_Item_Id = publicgood1_1_.Id left outer join amd.SMP_SOURCEINFO samapelpar2_ on publicgood1_1_.Samapel_Part_Number_Id = samapelpar2_.ID_SourceInfo, amd.App_BaseInformation baseinform12_ where not exists (select ssec.samapelITem_id from core_security_samapelitem ssec inner join core_goods_item g on ssec.samapelitem_id = g.samapel_item_id where not exists (SELECT aa.groupid FROM app_actiongroup aa where aa.groupid in (select au.groupid from app_usergroup au where au.userid = 1) and aa.actionid = 9054) and ssec.isenable = 1 and storehouse0_.goods_Item_ID = g.id) and not exists (select * from CORE_POWER_SECURITY cps where not exists (SELECT aa.groupid FROM app_actiongroup aa where aa.groupid in (select au.groupid from app_usergroup au where au.userid = 1) and aa.actionid = 9055) and cps.inventory_id = storehouse0_.storeHouse_Inventory_Id and cps.goodsitemtype = 6) and storehouse0_.storeHouse_Inventory_Id = storehouse3_.Id and storehouse3_.storeHouse_ID = storehouse5_.Id and storehouse3_.samapel_Item_Id = samapelite10_.MAINCODE and publicgood1_1_.Status_Id = baseinform12_.ID and 1 <> 2 and storehouse0_.goods_Item_ID = publicgood1_.Goods_Item_Id and publicgood1_1_.edited = 0 and publicgood1_1_.deleted = 0 and (exists (select storehouse13_.Id from amd.Core_STOREHOUSE storehouse13_ cross join amd.core_power power16_ cross join amd.core_power power17_ where storehouse5_.powerID = power16_.Id and storehouse13_.powerID = power17_.Id and (storehouse13_.Id in (741684217)) and storehouse13_.storeHouseType = 2 and (power16_.hierarchiCode like power17_.hierarchiCode || '%')) or (storehouse3_.storeHouse_ID in (741684217)) and storehouse5_.storeHouseType = 1) and (storehouse5_.storeHouse_Status not in (2, 3)) order by storehouse3_.samapel_Item_Id) where rownum <= 10
[Note: This query is generated by Hibernate].
It is clear that order by 40 million holds so much time. I find the main issue of this query. I omitted the “order by” and run the query, its response time was reduced to about 5 seconds. I was wondering why the “order by” affects so much the response time. (Somebody may think that if this table is partitioned or use another facility of the oracle, it may get better response time. Ok, it may be right but my emphasis is the “order by” performance. If there is a way that does the “order by” responsibility, why not to do it). Anyway, I am not able to omit the “order by” because the Customer needs to order and it is necessary for paging. I find a solution that is explained by an example. This solution I order only some records that are needed. How I will explain later. It is clear when Oracle wants to sort 40 million records, it naturally takes so much time. I replace “order by” with “where clause”. With doing this replacement the response time was reduces from 2 minutes to about 5 seconds and this is very exciting for me. I explain my solution via an example, anybody that read this Post tells me whether this solution is good or there is another solution that I do not know exists. Another hand I have a solution that is explained later if it is ok or not. Whether I use it or not. I explain my solution: Let’s assumed that there is two table as below:
Post table Id Others fields 1 2 3 4 5 … … Post_comment table Id post_id 1 5 2 5 3 5 4 5 6 5 7 2 8 2 9 2 10 3 11 1 12 1 13 1 14 1 15 1 16 1 17 1 18 1 19 1 20 1 21 1 22 1 23 1 24 1 25 1 26 4 27 4
There is a form that shows the result of join between POST table and POST_COMMENT table.
I explain both queries with “order by” all records of that table and “order by” only specific records that are needed. The result of two query is exactly the same but the response time of the second approach is the better than that one.
You assume that the page size is 10 and you are on page 3.
The first query with the “order by” all records of that table:
select * from (Select res.*, rownum as rownum_ from (Select * from POST_COMMENT Order by post_id asc) res Where rownum <= 30) where rownum_ > 20
The second solution:
Before execution the query, I query as below:
select * from (select post_id, count(id) from POST_COMMENT group by post_id) order by post_id asc
So the result of it is the below:
Post_id Count(id) Sum(count(id)) 1 15 15 2 3 18 3 1 19 4 2 21 5 5 26
It needs to say that the third column that is "Sum(count(id))" is calculated after that query.Any entry of this column is sum all before records.
So there is a formula that specifics which post_id must be selected. The formula is the below:
pageSize = 10, pageNumber = 3 from : (pageNumber – 1) * pageCount 2 * 10 = 20 to : (pageNumber – 1) * pageCount + pageCount 20 + 10 = 30
So I need the posts that are between (20, 30] of Sum(count(id)). According to this, I need only two post_id that have value 4,5. According to this the main query of the second approach is:
select * from (select rownum as rownum_, res.* from (select * from (select * from POST_COMMENT where post_id in (4, 5)) order by post_id asc) res where rownum <= 30) where rownum_ > 20
If you look at both query, you will see the biggest difference. The second query only selects the records of POST_COMENT that have post_id that are 4 and 5. After that, orders this records not all records of that table.