[jira] [Commented] (DERBY-6036) If you wrap a SELECT * view around a table, all of the columns are read from the base row even when you SELECT only a subset of the view columns.

["Rick Hillegas (JIRA)" <ji...@apache.org>]

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

Rick Hillegas commented on DERBY-6036:
--------------------------------------

I don't think I'm ready to propose a solution to this issue. My feeling is that this requires more thought and experiment than I have time for right now. Before putting this issue aside, I want to record a couple notes:

1) There is a workaround for this issue, viz., rewrite the query to eliminate the view.

2) What queries are really affected by this problem? Affected queries are those in which the cost of materializing the unneeded columns is significant. For instance,

a) SELECTs of base tables with large rows which span multiple pages.

b) SELECTs where the needed columns live in a covering index and a base table probe is needed to fetch the unneeded columns.

3) Here are some problem cases which a solution needs to address:

a) SELECT * views. This is the original test case discussed by this issue:

    create view vt as select * from t;
    select s_nr from vt where ns_r = 3000;

b) Views which explicitly SELECT columns not needed by the outer query:

    create view vt1 as select s_r, s_nr from t where ns_r = 3000;
    select s_nr from vt1;

                
> If you wrap a SELECT * view around a table, all of the columns are read from the base row even when you SELECT only a subset of the view columns.
> -------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-6036
>                 URL: https://issues.apache.org/jira/browse/DERBY-6036
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.10.0.0
>            Reporter: Rick Hillegas
>         Attachments: derby-6036.sql, derbyAST.xml
>
>
> This also affects SELECTs from views wrapping RestrictedVTIs. Restrictions are pushed into a restricted VTI if you wrap it in a view. However, projections are not. I will attach a script showing this problem.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira