[jira] [Commented] (TRAFODION-25) Insert, delete and update operators should use specialized cost method

["ASF GitHub Bot (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/TRAFODION-25?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14943689#comment-14943689 ] 

ASF GitHub Bot commented on TRAFODION-25:
-----------------------------------------

GitHub user DaveBirdsall opened a pull request:

    https://github.com/apache/incubator-trafodion/pull/102

    [TRAFODION-25] First draft of costing code for DELETE.

    This is the first draft of costing code for the Delete operator
    in Trafodion. Formerly a cost of 1 was returned for the Delete
    operator. With this change, we can expect better plans for DELETE
    statements.
    
    Part of the code for the Update operator is in this check-in as
    well, but the main part of the Update code remains to be written.
    
    Two CQDs have been added to control execution of this code.
    
    CQD HBASE_DELETE_COSTING 'ON' causes the new Delete costing code
    to be executed. A value of 'OFF' causes the old stub logic (a
    cost of 1) to be executed instead. The default for this CQD is
    'ON', so this code can be exercised.
    
    CQD HBASE_UPDATE_COSTING 'ON' causes partial Update costing code
    to be executed. This results in a stub cost object of 1e32 to
    be returned. (Actually, this activates a stub that was already
    there in the code; this turned out to be useful in discovering
    certain bugs, fixed below.) A value of 'OFF' causes earlier 
    stub logic (a cost of 1) to be executed instead. The default
    for this CQD is 'OFF' for now.
    
    Once this code has been sufficiently validated, these CQDs will be
    removed.
    
    Several bugs are also fixed in this check-in. All are latent,
    existing bugs that became more visible when regression-testing the
    new costing code due to plan changes.
    
    1. MERGE with a NOT MATCHED action was being (incorrectly)
    transformed into a nested join in some plans. This caused the
    NOT MATCHED action to not be executed. (optimizer/TransRule.cpp,
    optimizer/RelUpdate.h)
    
    2. MERGE error messages generated by the compiler were sometimes
    incorrect. (optimizer/BindRelExpr.cpp, generator/GenPreCode.cpp)
    
    3. A check constraint error message was not being reported when
    it should have on a table with SERIALIZED columns. This was caused
    by an HBase filter expression being generated with operators
    having the wrong sense (e.g., < instead of >).
    (generator/GenPreCode.cpp)
    
    4. CLEANUP would fail if the record in the OBJECTS_UNIQ_IDX index
    was missing. This happened because it was using a vanilla DELETE
    and the plan for the DELETE changed with the costing changes to 
    use index access. The fix was to use CQDs to avoid accessing the
    index when deleting from the OBJECTS table.
    (sqlcomp/CmpSeabaseDDLcleanup.cpp)
    
    5. INITIALIZE AUTHORIZATION would fail if there was a row in
    the OBJECTS table with an object_owner that does not exist in
    the AUTHS table. (The latter can happen in rare circumstances
    when toggling authorization off and on repeatedly. That is a
    bug in its own right that remains outstanding.) This has been
    worked around by writing 'DB__ROOT' to the OBJECT_PRIVILEGES
    table when the object_owner is invalid.
    
    Some other issues were noticed during unit testing that will
    be addressed in later check-ins:
    
    1. Scan costs for DELETEs using TSJ plans are grossly over-
    estimated. The cause is that the generated join predicates between
    the Scan and Delete operators are being assigned a default
    selectivity of 1/3. This is an existing, latent bug.
    
    2. The HBaseDeleteRule in the optimizer considers a Delete-only
    plan (that is, without a TSJ of a Scan to a Delete) when there
    are equality predicates on all key columns, and when there are
    no predicates on key columns at all. The former is correct, the
    latter is almost never a good plan. Costing should prevent the
    latter from being chosen but still this is unnecessary extra
    computation. (The rule does not consider Delete-only plans when
    a proper subset of the key columns have equality predicates.)
    This is an existing, latent bug.
    
     

You can merge this pull request into a Git repository by running:

    $ git pull https://github.com/DaveBirdsall/incubator-trafodion DeleteCostingDraft1

Alternatively you can review and apply these changes as the patch at:

    https://github.com/apache/incubator-trafodion/pull/102.patch

To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:

    This closes #102
    
----
commit 8e0b3d44efb149d033dfadcbed4f99639a0b53fe
Author: Dave Birdsall <db...@apache.org>
Date:   2015-10-01T22:12:46Z

    [TRAFODION-25] First draft of DELETE costing changes

commit a543b588938cfd2b750ea1957ec982173fb6b3e6
Author: Dave Birdsall <db...@apache.org>
Date:   2015-10-02T22:48:10Z

    Make INITIALIZE AUTHORIZATION more robust

commit aa0994e15698341727a1ec5414303a7cd5924727
Author: Dave Birdsall <db...@apache.org>
Date:   2015-10-02T23:42:27Z

    Updating expected files with DELETE plan changes

----


> Insert, delete and update operators should use specialized cost method 
> -----------------------------------------------------------------------
>
>                 Key: TRAFODION-25
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-25
>             Project: Apache Trafodion
>          Issue Type: Bug
>            Reporter: Qifan Chen
>            Assignee: David Wayne Birdsall
>              Labels: performance
>
> In Trafodion, insert, delete and update operators do not use a specialized cost method that model the true operation involved. 
> As a result, these operators can be assigned a zero cost and the query plan may not be optimal. In one example, a delete query against a partitioned table may get a serial plan. 



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)