• 2743711


    2743711 - Possible Unexpected Results When Using Query With an ORDER BY Clause on a Rowstore Table With a Parallelized Search on a Cpbtree-Type Index
    Version 14 from May 28, 2019 in English

    Show Changes

    Symptom
    A query on a rowstore table containing an ORDER BY clause can potentially lead to unexpected results in situations where the query is internally executed in a way that a search on a cpbtree-typed index is performed in a parallelized manner.

    Other Terms
    SAPHANA, HANA, wrong result, NO_INDEX_SEARCH, SELECT FROM

    Reason and Prerequisites
    Reason:

    Due to a programming error in SAP HANA, the parallel execution might be handled incorrectly, which can potentially lead to unexpected results.

    Affected Releases:

    SAP HANA 1:
    Revision = 122.21 (SPS12)

    SAP HANA 2:
    Revision = 024.07 (SPS02)
    Revisions 034.00 and 035.00 (SPS03)
    Prerequisites:

    All of the following conditions need to be met:
    A query is executed on a rowstore table with a cpbtree-typed index
    The query is parallelized
    An ORDER BY clause is used

    To check how many cpbtree-typed indexes exist for a specific table, you can execute the following command:

    SELECT count(*) FROM INDEXES WHERE SCHEMA_NAME = <schema_name> AND TABLE_NAME = <table_name> and INDEX_TYPE LIKE '%CPBTREE%'

    To check if the query execution involved a parallelized search on a cpbtree-typed index, you can:
    Create a PlanViz for the query, e.g. as described in SAP Note 2073964
    In the PlanViz, search for nodes containing Cpbtree in their name
    If you find any, check if their parent node owns multiple nodes with cpbtree in their name
    This issue is not restricted to either SAP applications or custom code - both can be affected, as soon as the above conditions are met.

    Solution
    Apply one of the following SAP HANA revisions:

    SAP HANA 1:
    Revisions >= 122.22 (SPS12)

    SAP HANA 2:
    Revisions >= 024.08 (SPS02)
    Revisions >= 036.00 (SPS03)
    or higher

    or higher
    Workaround:

    As a workaround you can use one of the following:

    Add hint NO_INDEX_SEARCH to the query:
    To pin the SQL Plan of the statement with the hint, see SAP Note 2222321 for details.
    To configure a statement hint for the query, see SAP Note 2400006 for details.
    You can disable the parallel execution of row engine globally using the following SQL statement:
    ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'SYSTEM' ) SET ('row_engine', 'parallel_query_execution') = 'false' WITH RECONFIGURE;

    Please note that this can have severe performance impact on the whole system. You can revert the setting using the following SQL statement:

    ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'SYSTEM' ) UNSET ('row_engine', 'parallel_query_execution') WITH RECONFIGURE;

    Disclaimer

    The SAP HANA configuration parameter [row_engine] parallel_query_execution is an internal parameter and may only be used in the context of this SAP Note. Using such a parameter requires that the SAP HANA system has the revision specified in the SAP Note, the described error situation has occurred. Using internal parameters outside of this given scope is not supported.

    Software Components
    Software Component From To And Subsequent
    HDB 1.00 1.00
    HDB 2.00 2.00
    Support Package Patches
    Software Component Support Package Patch Level Download
    SAP HANA DATABASE 1.00 SP122 000022
    SAP HANA DATABASE 2.0 SP024 000008
    SAP HANA DATABASE 2.0 SP036 000000
    References
    This document refers to
    Number Title
    2400006 FAQ: SAP HANA Statement Hints
    2222321 How-To: Pinning SAP HANA SQL Plans
    2073964 Create & Export PlanViz in HANA Studio
    2628684 Known issues detected in SAP HANA 2 SPS03
    2525424 Known issues detected in SAP HANA 2 SPS02
    2380229 SAP HANA Platform 2.0 - Central Note
    2325090 Known issues detected in SAP HANA 1 SPS12
    1514967 SAP HANA: Central Note
    This document is referenced by
    Number Title
    2142945 FAQ: SAP HANA Hints
    2222121 SAP HANA Wrong Result Sets
    2655761 SAP S/4HANA - restrictions and recommendations regarding specific revisions of SAP HANA database for use in SAP S/4HANA
    2735060 SAP HANA 2.0 SPS 03 Database Revision 036
    2746759 SAP HANA 2.0 SPS 03 Database Revision 037
    2728470 SAP HANA 2.0 SPS 02 Database Maintenance Revision 024.08
    Languages
    日本語 (Machine Translation)
    Português (Machine Translation)

  • 相关阅读:
    遂宁2017届零诊16题(仅想说明网传答案的不正确)
    当参变分离遇见洛必达
    高考数学九大超纲内容(1)wffc
    给王志红老师构造的函数,想说明搜题软件的解答过程的不严谨!
    记住路径名
    php返回文件路径
    两个字符串合并为一个字符串的各种方法
    global作用域
    二进制字符串的比较
    var_dump — 打印变量的相关信息
  • 原文地址:https://www.cnblogs.com/weikui/p/11130987.html
Copyright © 2020-2023  润新知