oracle - Oracle隐式转换是否依赖于连接表或 View

Question

我现在遇到了一个奇怪的问题。查询本身很大，所以我不打算在这里发布(我可以发布，以防有​​人需要查看)。现在我有一个表，TABLE1，有一个 CHAR(1) 列，COL1。此表列作为我的查询的一部分进行查询。当我过滤此列的记录集时，我说:

WHERE TAB1.COL1=1

这样查询就会运行并返回一个非常大的结果集。我最近更新了其中一个子查询以加快查询速度。但在此之后，当我写 WHERE TAB1.COL1=1 它不会返回任何东西，但如果我将它更改为 WHERE TAB1.COL1='1' 它会给我我需要的记录。注意带引号和不带引号的 WHERE 子句。因此，为了更清楚地说明，在更新其中一个子查询之前，我不必用引号来检查 COL1 值，但在更新之后我必须这样做。 Oracle 的哪些特性是我不知道的？

编辑:我发布了两个版本的查询以防有人发现它有用

版本 1:

SELECT p.ssn,
  pss.pin,
  pd.doc_number,
  p.surname,
  p.name,
  p.patronymic,
  to_number(p.sex, '9') as sex,
  citiz_c.short_name citizenship,
  p.birth_place,
  p.birth_day as birth_date,
  coun_c.short_name as country,
  di.name as leg_city,
  trim( pa.settlement
  || ' '
  || pa.street) AS leg_street,
  pd.issue_date,
  pd.issuing_body,
  irs.irn,
  irs.tpn,
  irs.reg_office,
  to_number(irs.insurer_type, '9') as insurer_type,
  TO_CHAR(sa.REG_CODE)
  ||CONVERT_INT_TO_DOUBLE_LETTER(TO_NUMBER(SUBSTR(TO_CHAR(sa.DOSSIER_NR, '0999999'), 2, 3)))
  ||SUBSTR(TO_CHAR(sa.DOSSIER_NR, '0999999'), 5, 4) CONVERTED_SSN_DOSSIER_NR,
  fa.snr
FROM
  (SELECT pss_t.pin,
    pss_t.ssn
  FROM EHDIS_INSURANCE.pin_ssn_status pss_t
  WHERE pss_t.difference_status < 5
  ) pss
INNER JOIN SSPF_CENTRE.file_archive fa
ON fa.ssn = pss.ssn
INNER JOIN SSPF_CENTRE.persons p
ON p.ssn = fa.ssn
INNER JOIN
  (SELECT pd_2.ssn,
    pd_2.type,
    pd_2.series,
    pd_2.doc_number,
    pd_2.issue_date,
    pd_2.issuing_body
  FROM

--The changed subquery starts here
    (SELECT ssn,
      MIN(type) AS type
    FROM SSPF_CENTRE.person_documents
    GROUP BY ssn
    ) pd_1
  INNER JOIN SSPF_CENTRE.person_documents pd_2
  ON pd_2.type       = pd_1.type
  AND pd_2.ssn       = pd_1.ssn
  ) pd
--The changed subquery ends here


ON pd.ssn = p.ssn
INNER JOIN SSPF_CENTRE.ssn_archive sa
ON p.ssn = sa.ssn
INNER JOIN SSPF_CENTRE.person_addresses pa
ON p.ssn = pa.ssn
INNER JOIN
  (SELECT i_t.irn,
    irs_t.ssn,
    i_t.tpn,
    i_t.reg_office,
    (
    CASE i_t.insurer_type
      WHEN '4'
      THEN '1'
      ELSE i_t.insurer_type
    END) AS insurer_type
  FROM sspf_centre.irn_registered_ssn irs_t
  INNER JOIN SSPF_CENTRE.insurers i_t
  ON i_t.irn                   = irs_t.new_irn
  OR i_t.old_irn               = irs_t.old_irn
  WHERE irs_t.is_registration IS NOT NULL
  AND i_t.is_real             IS NOT NULL
  ) irs ON irs.ssn             = p.ssn
LEFT OUTER JOIN SSPF_CENTRE.districts di
ON di.code = pa.city
LEFT OUTER JOIN SSPF_CENTRE.countries citiz_c
ON p.citizenship = citiz_c.numeric_code
LEFT OUTER JOIN SSPF_CENTRE.countries coun_c
ON pa.country_code  = coun_c.numeric_code
WHERE pa.address_flag = '1'--Here's the column value with quotes
AND fa.form_type    = 'Q3';

和版本 2:

SELECT p.ssn,
  pss.pin,
  pd.doc_number,
  p.surname,
  p.name,
  p.patronymic,
  to_number(p.sex, '9') as sex,
  citiz_c.short_name citizenship,
  p.birth_place,
  p.birth_day as birth_date,
  coun_c.short_name as country,
  di.name as leg_city,
  trim( pa.settlement
  || ' '
  || pa.street) AS leg_street,
  pd.issue_date,
  pd.issuing_body,
  irs.irn,
  irs.tpn,
  irs.reg_office,
  to_number(irs.insurer_type, '9') as insurer_type,
  TO_CHAR(sa.REG_CODE)
  ||CONVERT_INT_TO_DOUBLE_LETTER(TO_NUMBER(SUBSTR(TO_CHAR(sa.DOSSIER_NR, '0999999'), 2, 3)))
  ||SUBSTR(TO_CHAR(sa.DOSSIER_NR, '0999999'), 5, 4) CONVERTED_SSN_DOSSIER_NR,
  fa.snr
FROM
  (SELECT pss_t.pin,
    pss_t.ssn
  FROM EHDIS_INSURANCE.pin_ssn_status pss_t
  WHERE pss_t.difference_status < 5
  ) pss
INNER JOIN SSPF_CENTRE.file_archive fa
ON fa.ssn = pss.ssn
INNER JOIN SSPF_CENTRE.persons p
ON p.ssn = fa.ssn
INNER JOIN

 --The changed subquery starts here
 (SELECT ssn,
    type,
    series,
    doc_number,
    issue_date,
    issuing_body
  FROM
    (SELECT ssn,
      type,
      series,
      doc_number,
      issue_date,
      issuing_body,
      ROW_NUMBER() OVER (partition BY ssn order by type) rn
    FROM SSPF_CENTRE.person_documents
    )
  WHERE rn = 1
  ) pd --
 --The changed subquery ends here

ON pd.ssn = p.ssn
INNER JOIN SSPF_CENTRE.ssn_archive sa
ON p.ssn = sa.ssn
INNER JOIN SSPF_CENTRE.person_addresses pa
ON p.ssn = pa.ssn
INNER JOIN
  (SELECT i_t.irn,
    irs_t.ssn,
    i_t.tpn,
    i_t.reg_office,
    (
    CASE i_t.insurer_type
      WHEN '4'
      THEN '1'
      ELSE i_t.insurer_type
    END) AS insurer_type
  FROM sspf_centre.irn_registered_ssn irs_t
  INNER JOIN SSPF_CENTRE.insurers i_t
  ON i_t.irn                   = irs_t.new_irn
  OR i_t.old_irn               = irs_t.old_irn
  WHERE irs_t.is_registration IS NOT NULL
  AND i_t.is_real             IS NOT NULL
  ) irs ON irs.ssn             = p.ssn
LEFT OUTER JOIN SSPF_CENTRE.districts di
ON di.code = pa.city
LEFT OUTER JOIN SSPF_CENTRE.countries citiz_c
ON p.citizenship = citiz_c.numeric_code
LEFT OUTER JOIN SSPF_CENTRE.countries coun_c
ON pa.country_code  = coun_c.numeric_code
WHERE pa.address_flag = 1--Here's the column value without quotes
AND fa.form_type    = 'Q3';

我在两个查询中为更改的子查询和 WHERE 子句放置了分隔注释。两个版本的子查询返回相同的结果，其中一个版本更慢，这就是我决定更新它的原因。

Answer 1

用最简单的例子，我无法在 11.2.0.3.0 或 11.2.0.1.0 上重现您的问题。

SQL> create table tmp_test ( a char(1) );

Table created.

SQL> insert into tmp_test values ('1');

1 row created.

SQL> select *
  2    from tmp_test
  3   where a = 1;

A
-
1

如果我随后在表中插入一个非数字值，我可以确认 Chris 的评论“Oracle 会将 tab1.col1 = 1 重写为 to_number(tab1.col1) = 1 ”，这意味着该列中只有数字字符。

SQL> insert into tmp_test values ('a');

1 row created.

SQL> select *
  2    from tmp_test
  3   where a = 1;
ERROR:
ORA-01722: invalid number



no rows selected

如果您有兴趣跟踪此问题，您应该逐渐降低查询的复杂性，直到找到一个最小的、可重现的示例。 Oracle 可以预先计算要在 JOIN 中使用的转换，因为您的查询很复杂，这似乎是对正在发生的事情的可能解释。

甲骨文 explicitly recommends against using implicit conversion所以最好不要使用它；正如你发现的那样。首先，无法保证您的索引会被正确使用。

Oracle recommends that you specify explicit conversions, rather than rely on implicit or automatic conversions, for these reasons:

• SQL statements are easier to understand when you use explicit data type conversion functions.

• Implicit data type conversion can have a negative impact on performance, especially if the data type of a column value is converted to that of a constant rather than the other way around.

• Implicit conversion depends on the context in which it occurs and may not work the same way in every case. For example, implicit conversion from a datetime value to a VARCHAR2 value may return an unexpected year depending on the value of the NLS_DATE_FORMAT parameter.

• Algorithms for implicit conversion are subject to change across software releases and among Oracle products. Behavior of explicit conversions is more predictable.

如果您在该列中只有数字字符，我强烈建议将其更改为 NUMBER(1) 列，并且我始终建议进行显式转换以避免长期运行带来的麻烦。