EverSQL Database Performance Knowledge Base

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
)