[Solved] Does Oracle implicit conversion depend on joined tables or views

EverSQL Database Performance Knowledge Base

Does Oracle implicit conversion depend on joined tables or views

Database type:

I've faced with a weird problem now. The query itself is huge so I'm not going to post it here (I could post however in case someone needs to see). Now I have a table ,TABLE1, with a CHAR(1) column, COL1. This table column is queried as part of my query. When I filter the recordset for this column I say:

WHERE TAB1.COL1=1

This way the query runs and returns a very big resultset. I've recently updated one of the subqueries to speed up the query. But after this when I write WHERE TAB1.COL1=1 it does not return anything, but if I change it to WHERE TAB1.COL1='1' it gives me the records I need. Notice the WHERE clause with quotes and w/o them. So to make it more clear, before updating one of the sub-queries I did not have to put quotes to check against COL1 value, but after updating I have to. What feature of Oracle is it that I'm not aware of?

EDIT: I'm posting the tw versions of the query in case someone might find it useful

Version 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';

And Version 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';

I've put separating comments for the changed subqueries and the WHERE clause in both queries. Both versions of the subqueries return the same result, one of them is just slower, which is why I decided to update it.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Correlated Subqueries (query line: 41): A correlated subquery is a subquery that contains a reference (column: type) to a table that also appears in the outer query. Usually correlated queries can be rewritten with a join clause, which is the best practice. The database optimizer handles joins much better than correlated subqueries. Therefore, rephrasing the query with a join will allow the optimizer to use the most efficient execution plan for the query.
  2. Avoid OR Conditions By Using UNION (modified query below): In mosts cases, filtering using the OR operator cannot be applied using indexes. A more optimized alternative will be to split the query to two parts combined with a UNION clause, while each query holds one part of the original OR condition.
  3. Avoid Subqueries (query line: 49): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
  4. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  5. Prefer Direct Join Over Joined Subquery (query line: 32): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, we recommend to replace subqueries with JOIN clauses.
  6. Use Numeric Column Types For Numeric Values (query line: 9): Referencing a numeric value (e.g. 9) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
  7. Use Numeric Column Types For Numeric Values (query line: 22): Referencing a numeric value (e.g. 9) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
  8. Use Numeric Column Types For Numeric Values (query line: 24): Referencing a numeric value (e.g. 0999999) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
  9. Use Numeric Column Types For Numeric Values (query line: 27): Referencing a numeric value (e.g. 0999999) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
  10. Use Numeric Column Types For Numeric Values (query line: 124): Referencing a numeric value (e.g. 1) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
  11. Use UNION ALL instead of UNION (query line: 94): Always use UNION ALL unless you need to eliminate duplicate records. By using UNION ALL, you'll avoid the expensive distinct operation the database applies when using a UNION clause.
Optimal indexes for this query:
CREATE INDEX countries_idx_numeric_code ON countries (numeric_code);
CREATE INDEX districts_idx_code ON districts (code);
CREATE INDEX file_archive_idx_form_type_ssn ON file_archive (form_type,ssn);
CREATE INDEX insurers_idx_old_irn_is_real ON insurers (old_irn,is_real);
CREATE INDEX insurers_idx_irn_is_real ON insurers (irn,is_real);
CREATE INDEX irn_ssn_idx_is_registration ON irn_registered_ssn (is_registration);
CREATE INDEX person_addresses_idx_address_flag_ssn ON person_addresses (address_flag,ssn);
CREATE INDEX person_documents_idx_ssn ON person_documents (ssn);
CREATE INDEX persons_idx_ssn ON persons (ssn);
CREATE INDEX pin_status_idx_difference_status ON pin_ssn_status (difference_status);
CREATE INDEX ssn_archive_idx_ssn ON ssn_archive (ssn);
The optimized query:
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
        EHDIS_INSURANCE.pin_ssn_status 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
                (SELECT
                    SSPF_CENTRE.person_documents.ssn,
                    MIN(type) AS type 
                FROM
                    SSPF_CENTRE.person_documents 
                GROUP BY
                    SSPF_CENTRE.person_documents.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 
                    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,
                        insurer_type 
                    FROM
                        ((SELECT
                            i_t.irn AS i_t_irn,
                            irs_t.ssn AS irs_t_ssn,
                            i_t.tpn AS i_t_tpn,
                            i_t.reg_office AS 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.old_irn = irs_t.old_irn 
WHERE
irs_t.is_registration IS NOT NULL 
AND i_t.is_real IS NOT NULL) 
UNION
DISTINCT (SELECT
i_t.irn AS i_t_irn,
irs_t.ssn AS irs_t_ssn,
i_t.tpn AS i_t_tpn,
i_t.reg_office AS 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 
WHERE
irs_t.is_registration IS NOT NULL 
AND i_t.is_real IS NOT NULL)
) AS union1
) 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' 
AND fa.form_type = 'Q3'
) 
AND (
pss.difference_status < 5
)

Related Articles



* original question posted on StackOverflow here.