logo标签列表

在PreparedStatement中设置任何参数都不起作用

javasqloracleprepared-statementresultset
12

我有一个 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 |
-----------------------------------------------------------------------------------------------------------------------------

谢谢

1
如果您有两个参数，应该将值绑定到它们两个。 - Tim Biegeleisen
它在执行executeQuery调用时挂起，setInt没问题。 - user2178964
你是什么意思？我对属性字符串规则一无所知...你的意思是说我不能在请求末尾进行参数替换吗？顺便提一下，当我在服务器上运行代码时，这些参数替换是有效的，但在本地却不行... - user2178964
尝试在查询中设置另一个参数，而不是使用1 =？。 - Shadab Siddiqui
我完全更新了原帖以暴露实际情况，也添加了许多细节。希望你能帮忙。谢谢。 - user2178964
显示剩余10条评论
4个回答
4

当两个几乎相同的查询有非常不同的性能特征时，通常的原因是执行计划在优化器中计算时出现了某些问题。

对于上述内容，我注意到您的查询具有笛卡尔积。根据Oracle文档中关于笛卡尔积的说明:
“在某些情况下，优化器可能会将两个表之间的公共过滤条件作为可能的连接条件。”

显然选择不理想的执行计划的一个可能原因是存在绑定变量 - 请参阅Oracle关于为什么执行计划会改变的文档：

“影响成本的一些因素包括以下几点：
... 绑定变量类型和值 ..."

从Oracle文档的陈述中可以解释为“如果指定笛卡尔积，则可能需要一些运气才能使它的性能良好，而使用绑定变量并不能提高成功的机会”。

如果您将查询更新为用"FROM t_user_usr INNER JOIN t_simulationeventtrack_set ON xxx=yyy "替换"FROM t_user_usr, t_simulationeventtrack_set "
（xxx = yyy可能是WHERE中的条件之一，但是没有办法确定哪一个是，需要查看表的描述），优化器的性能可能会更好。

第一个问题中有这样的陈述：“请求被启动，但永远不会结束，也没有显示任何错误。”在这种情况下，“永远”指多长时间？ 我问这个问题是因为，当某些操作应该仅需几分之一秒时，任何接近30秒的操作都可能被终止。

最新的问题“... OR 1 =？”将强制查询执行计划不使用SIM_ID + SET_DATE索引（鉴于数据量的大小，这将导致性能问题）。根据条件的功能，我只需要在Java代码中准备两个语句（一个带日期条件，一个不带）- 这应该允许优化器为每个语句选择正确的索引。

事实上，我有另一个具有相同问题的请求，没有笛卡尔积，只有“左连接”，问题是一样的。我将在原始帖子中添加这个第二个请求。 - user2178964
你能分享一下上述所提出的更改与之前问题的结果吗？还有，如果你遇到了相同的问题，那么查询绑定和非绑定执行的时间也会有所不同。提供这些信息也是很好的。第三点，你能提供涉及表的SHOW CREATE TABLE详细信息（这应该在之前就被提出）吗？ - Koen
我为那个有问题的表格添加了创建表格的详细信息，因为它包含了数百万行。 - user2178964
您可能还希望添加 'CREATE INDEX MYCOMBO ON "OFFER_STORAGE"."T_SIMULATIONEVENTTRACK_SET" ("SET_SIM_ID", "SET_DATE")' ，因为大多数条件都涉及这两列。请注意，获取数字仍然很好。 - Koen
我完全更新了原始帖子，以揭露实际情况。我也添加了很多细节。希望你能帮忙。谢谢。 - user2178964
我发布了执行计划，如果你有线索，我会非常感兴趣...谢谢。 - user2178964
3
似乎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);
我完全更新了原始帖子以揭示实际情况。我还添加了很多细节。希望你能帮忙。谢谢。 - user2178964
好的，看起来你需要在SET_DATE上创建一个非唯一索引。如果SIM_ID是（唯一）主键，则（SIM_ID，SET_DATE）索引肯定是无用的。也许最快的方式是（SET_DATE，SIM_ID），以便重新排序ORDER_BY。尝试将其放在WHERE部分而不是INNER JOIN AND中。（我可能会错误地判断SET_DATE属于哪个表。） - Joop Eggen
谢谢。我不是很理解，你想尝试什么？我刚刚更新了原帖，我找到了一个解决方案，将CAST(? AS DATE)放入请求中，但这并不是真正的“干净”，现在我有了相同的整数参数问题。 - user2178964
当我使用：java.sql.Date tmstpExtract = new java.sql.Date(this.dateLastExtract.getTime()); 和 setDate 时，代替 Timestamp tmstpExtract = new Timestamp(this.dateLastExtract.getTime()); 和 setTimestamp，它也能快速工作。但我不明白为什么... Java 和 SQL 中的 Timestamp 格式不是相同的吗？ - user2178964
java.util.Date 表示日期时间。SQL DATE 仅表示日期，它是 java.sql.Date 的子类（继承自 java.util.Date 但将时间部分归零）。SQL TIMESTAMP/DATETIME 表示日期和时间，它是 java.sql.Timestamp 的子类（也继承自 java.util.Date，只是名字不同）。通常驱动程序会在一定程度上转换 setInt/setDouble/setDate/setTimestamp，但对于 TIMESTAMP 字段，setDate 将失去其时间部分。 - Joop Eggen
显示剩余2条评论
0
发出请求，但它永远不会结束，也没有显示任何错误信息。
当您有一个长时间运行的语句（超过几秒钟）时，您可以在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;

这里可能会得到多个值（不同的plan_hash_value）。

正如Joop Eggen已经发布的那样，当您通过绑定变量实际设置参数时，您可能会遇到不同的执行计划。

我完全更新了原始帖子以揭示实际情况。我还添加了许多细节。希望你能帮忙。我将尝试添加执行计划，但由于请求格式不良好，很难在此处发布。 - user2178964
如果您能自己发现差异，就不需要发布执行计划。如果您的数据绑定不正确，例如日期类型，那么您会注意到两个查询之间的search_column不同。例如：如果您使用时间戳参数查询日期列，则无法在日期列上使用索引，因为Oracle需要将每个日期转换为时间戳进行比较。 - EasterBunnyBugSmasher
我发布了执行计划，如果你有线索，我会非常感兴趣...谢谢 - user2178964
0

从执行计划可以看出，在快速执行计划中，日期列上的索引被使用，这也是优化器开始的地方。在较慢的执行计划中，优化器没有注意到这是一个好的索引，所以它会从其他地方开始。

问题在于java.sql.Timestamp数据类型与oracle DATE不对应。“set_date”是DATE类型，这意味着它存储的精度为秒。现在你查询一个参数类型为java.sql.Timestamp的查询，它具有纳秒精度。为了将表中的DATE值与查询参数进行比较，Oracle必须使用函数将DATE转换为更高的精度（类似于Java中的强制类型转换）。不幸的是，如果您需要首先在列的值上使用函数，则索引无法工作。

你应该做什么：当通过JDBC查询时，始终使用低精度时间类型而不是列的类型。在你的情况下：要么使用java.sql.Date进行查询，要么将列的数据类型改为至少有纳秒精度。

我知道这并不能解决你在其他数据类型方面的问题，但这是根据你提供的信息和执行计划我能告诉你的所有内容。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接