我有一个 SQL 请求,定义如下:
private static final String fetchOfferQuery = "SELECT DISTINCT "
+ "sim_id, sim_code, sim_label, sim_state, sim_type, sim_customerid, sim_storeid, sim_projectnumber, sim_version, sim_type_user, sim_type_vente, sim_statut, "
+ "(SELECT MAX(set_date) FROM offer_storage.t_simulationeventtrack_set WHERE set_sim_id = sim_id) AS sim_dateevtmax, "
+ "sim_creation_user, sim_modif_user, sim_rayon, sim_hours_lifetime, sim_eligible_reduced_vat, sim_store_linked, sim_canal, "
+ "ofr_id, CAST(ofr_creationdate AS timestamp) AS ofr_creationdate, ofr_label, ofr_state, ofr_transaction, ofr_modif_date, ofr_del_valid, "
+ "ofr_numcdecli, "
+ "ofi_id, ofi_productid, ofi_quantity,ofi_productprice, ofi_top, ofi_c1promo, ofi_codeactivite, ofi_codrem, "
+ "ofi_datejour, ofi_datepose, ofi_dateprevpose, ofi_datfinc1, ofi_datfinprxvtepromo, ofi_delai, ofi_libligdtl, ofi_montantpresta, "
+ "ofi_montrt, ofi_aro_id, ofi_numartisan, ofi_prxvte, ofi_prxvtepromo, ofi_typinitialoff, ofi_typoff, ofi_c1, ofi_numlig, "
+ "deo_id, deo_numligdtl, deo_codligdtl, deo_libligdtl, "
+ "aro_id, aro_type_offer, aro_type_inioff, aro_top_caisse, aro_num_arty, aro_date_prev, aro_mntrt, aro_date_jour, "
+ "aro_delai, aro_mnt_presta, aro_codact, aro_date_pose "
+ "FROM offer_storage.t_simulation_sim "
//with fixed date
+ "INNER JOIN offer_storage.t_simulationeventtrack_set ON set_sim_id = sim_id AND set_date >= TO_DATE('20180726000000','yyyymmddhh24miss') "
//with bind parameter
//+ "INNER JOIN offer_storage.t_simulationeventtrack_set ON set_sim_id = sim_id AND set_date >= ? "
+ "LEFT JOIN offer_storage.t_offer_ofr ON ofr_sim_id = sim_id "
+ "LEFT JOIN offer_storage.t_offeritem_ofi ON ofi_ofr_id = ofr_id "
+ "LEFT JOIN offer_storage.t_details_item_offer_deo ON deo_ofi_id = ofi_id "
+ "LEFT JOIN offer_storage.t_artisan_offer_aro ON aro_id = ofi_aro_id "
+ "ORDER BY sim_id, ofr_id, ofi_id, deo_id, aro_id";
如果我在请求中设置了一个固定的日期:
...
+ "INNER JOIN offer_storage.t_simulationeventtrack_set ON set_sim_id = sim_id AND set_date >= TO_DATE('20180726000000','yyyymmddhh24miss') "
...
使用以下代码执行请求: simulationsSt = connection.prepareStatement(fetchOfferQuery); ResultSet simulationsRs = simulationsSt.executeQuery();
该请求需要1分30秒才能完成。
如果我使用绑定参数:
...
+ "INNER JOIN offer_storage.t_simulationeventtrack_set ON set_sim_id = sim_id AND set_date >= ? "
...
在执行executeQuery方法之前使用set方法(dateLastExtract = 20180726000000的相同日期):
simulationsSt.setTimestamp(1, new Timestamp(this.dateLastExtract.getTime()));
这个请求需要10分钟的时间!
我也遇到了一个问题,所有请求都需要设置任何参数(整数、日期、字符串等),所以它不仅限于日期,而是在使用绑定时和不使用时都会出现。
另外,数据行的数量非常大:
t_simulationeventtrack_set : 66.097.939 rows
t_details_item_offer_deo : 46.259.704 rows
t_offeritem_ofi : 14.232.150 rows
t_artisan_offer_aro : 2.317.658 rows
t_offer_ofr : 1.801.969 rows
t_simulation_sim : 1.756.235 rows
创建表 t_simulationeventtrack_set 的脚本为:
CREATE TABLE "OFFER_STORAGE"."T_SIMULATIONEVENTTRACK_SET"
( "SET_ID" NUMBER(*,0) NOT NULL ENABLE,
"SET_DATE" DATE,
"SET_CHANGETYPE" VARCHAR2(254 BYTE),
"SET_CHANGE" VARCHAR2(254 BYTE),
"SET_USR_ID" NUMBER(*,0),
"SET_SIM_ID" NUMBER(*,0),
CONSTRAINT "PK_SIMULATIONEVENTTRACK" PRIMARY KEY ("SET_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "OAA_INDX" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "OAA_DATA" ;
CREATE INDEX "OFFER_STORAGE"."IDX_SET_SIM_ID" ON "OFFER_STORAGE"."T_SIMULATIONEVENTTRACK_SET" ("SET_SIM_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "OAA_INDX" ;
CREATE INDEX "OFFER_STORAGE"."IDX_SIMULATIONEVENTTRACK_DATE" ON "OFFER_STORAGE"."T_SIMULATIONEVENTTRACK_SET" ("SET_DATE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "OAA_INDX" ;
CREATE INDEX "OFFER_STORAGE"."IDX_SIMULATIONEVENTTRACK_FDATE" ON "OFFER_STORAGE"."T_SIMULATIONEVENTTRACK_SET" (TO_CHAR("SET_DATE",'YYYY-MM-DD'))
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "OAA_INDX" ;
CREATE INDEX "OFFER_STORAGE"."IDX_SIM_ID_USER_ID" ON "OFFER_STORAGE"."T_SIMULATIONEVENTTRACK_SET" ("SET_SIM_ID", "SET_USR_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "OAA_INDX" ;
出了什么问题?
我试图在"T_SIMULATIONEVENTTRACK_SET"表上添加索引("SET_SIM_ID", "SET_DATE") 但是没有改变任何东西。
编辑:
我在这里找到了日期参数的解决方案: https://blog.jooq.org/2014/12/22/are-you-binding-your-oracle-dates-correctly-i-bet-you-arent/
如果我使用"CAST(? AS DATE)"替换"?",它就很快!
但是现在我有一个整数参数的相同问题。 我有一个子句">= CAST(? AS DATE) OR 1 = ?" 第二个参数是1或0,如果我放0,它会取每一行,包括旧的行。
当我输入这个简单的整数参数时,它变得很慢...
编辑2:
这是绑定执行计划:
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 191M(100)| |
| 1 | SORT AGGREGATE | | 1 | 14 | | | |
| 2 | FIRST ROW | | 1 | 14 | | 4 (0)| 00:00:01 |
| 3 | INDEX RANGE SCAN (MIN/MAX)| IDX_SIMULATIONEVENTTRACK_SD | 1 | 14 | | 4 (0)| 00:00:01 |
| 4 | SORT ORDER BY | | 26 | 9698 | 17G| 191M (1)|637:07:31 |
| 5 | FILTER | | | | | | |
| 6 | HASH JOIN RIGHT OUTER | | 46M| 16G| 3351M| 915K (1)| 03:03:10 |
| 7 | TABLE ACCESS FULL | T_DETAILS_ITEM_OFFER_DEO | 46M| 2815M| | 145K (1)| 00:29:01 |
| 8 | HASH JOIN RIGHT OUTER | | 14M| 4263M| 134M| 384K (1)| 01:16:57 |
| 9 | TABLE ACCESS FULL | T_ARTISAN_OFFER_ARO | 2317K| 108M| | 4543 (1)| 00:00:55 |
| 10 | HASH JOIN OUTER | | 14M| 3589M| 325M| 187K (1)| 00:37:28 |
| 11 | HASH JOIN RIGHT OUTER | | 1823K| 304M| 125M| 35194 (1)| 00:07:03 |
| 12 | TABLE ACCESS FULL | T_OFFER_OFR | 1824K| 104M| | 5995 (1)| 00:01:12 |
| 13 | TABLE ACCESS FULL | T_SIMULATION_SIM | 1778K| 195M| | 12293 (1)| 00:02:28 |
| 14 | TABLE ACCESS FULL | T_OFFERITEM_OFI | 14M| 1183M| | 69005 (1)| 00:13:49 |
| 15 | INDEX RANGE SCAN | IDX_SIMULATIONEVENTTRACK_SD | 1 | 14 | | 4 (0)| 00:00:01 |
| 16 | INDEX RANGE SCAN | IDX_SET_SIM_ID | 2 | 12 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
没有绑定变量的执行计划(更快):
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 163K(100)| |
| 1 | SORT AGGREGATE | | 1 | 14 | | | |
| 2 | FIRST ROW | | 1 | 14 | | 4 (0)| 00:00:01 |
| 3 | INDEX RANGE SCAN (MIN/MAX) | IDX_SIMULATIONEVENTTRACK_SD | 1 | 14 | | 4 (0)| 00:00:01 |
| 4 | SORT ORDER BY | | 156K| 57M| 60M| 163K (1)| 00:32:41 |
| 5 | NESTED LOOPS OUTER | | 156K| 57M| | 150K (1)| 00:30:07 |
| 6 | NESTED LOOPS OUTER | | 48049 | 14M| | 40080 (1)| 00:08:01 |
| 7 | NESTED LOOPS OUTER | | 48049 | 12M| | 35935 (1)| 00:07:12 |
| 8 | HASH JOIN OUTER | | 6085 | 1123K| | 12654 (1)| 00:02:32 |
| 9 | NESTED LOOPS | | | | | | |
| 10 | NESTED LOOPS | | 5930 | 747K| | 6654 (1)| 00:01:20 |
| 11 | SORT UNIQUE | | 6008 | 84112 | | 643 (0)| 00:00:08 |
| 12 | TABLE ACCESS BY INDEX ROWID| T_SIMULATIONEVENTTRACK_SET | 6008 | 84112 | | 643 (0)| 00:00:08 |
| 13 | INDEX RANGE SCAN | IDX_SIMULATIONEVENTTRACK_DATE | 6008 | | | 20 (0)| 00:00:01 |
| 14 | INDEX UNIQUE SCAN | PK_SIMULATION | 1 | | | 1 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID | T_SIMULATION_SIM | 1 | 115 | | 2 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | T_OFFER_OFR | 1779K| 101M| | 5994 (1)| 00:01:12 |
| 17 | TABLE ACCESS BY INDEX ROWID | T_OFFERITEM_OFI | 8 | 688 | | 4 (0)| 00:00:01 |
| 18 | INDEX RANGE SCAN | IDX_OFI_OFR_ID | 9 | | | 2 (0)| 00:00:01 |
| 19 | TABLE ACCESS BY INDEX ROWID | T_ARTISAN_OFFER_ARO | 1 | 49 | | 2 (0)| 00:00:01 |
| 20 | INDEX UNIQUE SCAN | PK_ARTISANOFFER | 1 | | | 1 (0)| 00:00:01 |
| 21 | TABLE ACCESS BY INDEX ROWID | T_DETAILS_ITEM_OFFER_DEO | 3 | 189 | | 4 (0)| 00:00:01 |
| 22 | INDEX RANGE SCAN | IDX_DEO_OFI_ID | 22 | | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------------
谢谢