Устанавливать любой параметр в PreparedStatement не работает
У меня есть 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); Моделирование ResultSetRs = simulationsSt.executeQuery();
Запрос займет 1 мин.30.
Если я использую параметр bind:
...
+ "INNER JOIN offer_storage.t_simulationeventtrack_set ON set_sim_id = sim_id AND set_date >= ? "
...
С установленным методом перед executeQuery (dateLastExtract = то же самое Дата 20180726000000):
simulationsSt.setTimestamp(1, new Timestamp(this.dateLastExtract.getTime()));
Запрос займет 10 минут!
У меня также есть проблема со всеми запросами, которые мне нужны, чтобы установить любой параметр (int, date, string... и т.д.). Так что это не специфично на сегодняшний день, это просто когда я использую привязку, а когда нет.
Для информации количество строк данных довольно велико:
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
Сценарий CREATE TABLE 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) ИЛИ 1 =?" Второй параметр равен 1 или 0, и если я поставлю 0, он возьмет каждую строку, даже старую.
Когда я помещаю этот простой int param, он медленнее снова...
EDIT 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 |
-----------------------------------------------------------------------------------------------------------------------------
Спасибо
Ответы
Ответ 1
Если две копии по существу одного и того же запроса имеют очень разные характеристики производительности, причиной обычно является некоторая причуда в том, как план выполнения вычисляется оптимизатором.
Для вышесказанного отметим, что ваш запрос имеет декартовое соединение. Согласно документации Oracle относительно декартовых объединений:
"В некоторых случаях оптимизатор может подобрать общее условие фильтрации между двумя таблицами как возможное условие соединения".
Одной из возможных причин очевидного выбора не оптимального плана выполнения может быть наличие переменных привязки - см. Документацию Oracle о том, почему изменения плана выполнения меняются:
"Некоторые факторы, влияющие на затраты, включают следующее:
... Привязать переменные типы и значения... "
Одна из интерпретаций заявлений из документации Oracle - "если вы укажете декартовое соединение, вам может потребоваться некоторая удача, чтобы он работал хорошо, а использование переменных привязки не улучшает ваши шансы".
Если вы обновите запрос, заменив "FROM t_user_usr, t_simulationeventtrack_set",
с "FROM t_user_usr INNER JOIN t_simulationeventtrack_set ON xxx = yyy"
(xxx = yyy, вероятно, должно быть одним из условий в WHERE, но невозможно определить, какой из них без описания таблицы) скорее всего заставит оптимизатора вести себя лучше.
Первый вопрос имел выражение "Запрос запущен, но он никогда не заканчивается, ошибка не отображается". Как долго "никогда" в этом случае? Я спрашиваю, потому что, когда что-то займет часть секунды, все, что приближается к отметке в 30 секунд, вполне может оказаться убитой на моей машине.
Последняя проблема с "... OR 1 =?" заставит план выполнения запроса не использовать индекс SIM_ID + SET_DATE (который, учитывая размер данных, приведет к возникновению проблемы с производительностью).
Учитывая, что делает условие, я бы просто имел 2 подготовленных оператора в своем Java-коде (один с одним и без условия даты), что должно позволить оптимизатору выбрать правильный индекс для использования для каждого из них.
Ответ 2
Кажется, что SQL-механизм устраняет постоянные выражения, такие как X OR 1=0 X. А для динамического подготовленного выражения не так, не используя какой-либо индекс на set_date.
Условие имеет целью отключить фильтрацию на set_date, для всех значений set_date.
Если это возможно, вы можете это сделать, заменив
+ "AND (set_date >= TO_DATE('2018-07-19', 'YYYY-MM-DD') "
+ "OR 1 = ?) "
с
+ "AND set_date >= ? "
а также
LocalDate d = seen == 1 ? LocalDate.of(1900, 1, 1) : LocalDate.of(2018, 7, 19);
java.sql.Date sd = new java.sql.Date(d.toEpochDay);
simulationsSt.setDate(1, sd);
Ответ 3
Запрос запускается, но он никогда не заканчивается, ошибка не отображается.
когда у вас длинный оператор (более нескольких секунд), вы можете увидеть его в v $ session_longops
select * from v$session_longops v order by v.start_time desc;
Найдите его, возьмите sql_id и найдите инструкцию в dba_hist_sqltext
select * from dba_hist_sqltext t where t.sql_id = 'b6usrg82hwsa3';
и его план выполнения в dba_hist_sql_plan:
select * from dba_hist_sql_plan p where p.sql_id = 'b6usrg82hwsa3' order by p.plan_hash_value, p.id;
вы можете получить более 1 здесь (разные plan_hash_value).
Как уже писал Joop Eggen, вы, вероятно, используете другой план выполнения, когда вы фактически устанавливаете параметры через переменные связывания.
Ответ 4
Как вы можете видеть в более быстром плане выполнения, используется индекс в столбце даты и где начинается оптимизатор. В более медленном плане выполнения оптимизатор не замечает, что это хороший индекс, поэтому он запускается где-то в другом месте.
Проблема в том, что тип данных java.sql.Timestamp не соответствует DATE оракула. "set_date" имеет тип DATE, это означает, что он хранится с точностью до нескольких секунд. Теперь вы делаете запрос с параметром, который имеет тип java.sql.Timestamp, который имеет наносекундную точность. Чтобы сравнить значения DATE в таблице с параметром запроса, oracle должен использовать функцию для преобразования DATE в более высокую точность (подумайте об этом как о трансляции в java). И, к сожалению, индексы не работают, если вам сначала нужно использовать функцию по значению столбца.
Что вам следует делать: при запросе через JDBC всегда используйте более низкие временные типы точности, чем тип столбца. В вашем случае: либо используйте java.sql.Date для запроса, либо измените тип данных столбца как минимум на наносекунду.
Я знаю, что это не решает вашу проблему с другими типами данных, но все, что я могу рассказать вам из планов информации и исполнения, которые вы опубликовали.
