[jira] [Commented] (TRAFODION-2004) Statistics on volatile tables is not supported

["David Wayne Birdsall (JIRA)" <ji...@apache.org>]

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

David Wayne Birdsall commented on TRAFODION-2004:
-------------------------------------------------

The manual is incorrect. In fact, UPDATE STATISTICS is not supported on volatile tables at this time. The manual should be updated.

The error messages given by UPDATE STATISTICS in this case are not helpful. Instead, the code should be changed to simply say that statistics on volatile tables are not supported.

There is a simple workaround for this lack of support. Instead of creating a volatile table, create a non-volatile table.

If in the future we would like to add this support, the following changes are needed:
1. Insure that the appropriate histogram tables are created in the volatile schema.
2. Change UPDATE STATISTICS to use the volatile schema.
3. Change sqlci and mxosrvr so that if an UPDATE STATISTICS is done, any statistics cached for the object are invalidated. That is, statistics need to be re-read the next time the object is referenced in a SQL statement. (This would be a helpful change for non-volatile tables as well.)

> Statistics on volatile tables is not supported
> ----------------------------------------------
>
>                 Key: TRAFODION-2004
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-2004
>             Project: Apache Trafodion
>          Issue Type: Bug
>          Components: sql-cmp
>    Affects Versions: 2.0-incubating, 1.3-incubating
>         Environment: All
>            Reporter: David Wayne Birdsall
>            Assignee: David Wayne Birdsall
>
> The Trafodion SQL Reference Manual on wiki: http://trafodion.apache.org/docs/sql_reference/Trafodion_SQL_Reference_Manual.pdf, Page 119, 'Considerations for CREATE VOLATILE TABLE' explicitly says the following:
> "Statistics are not automatically updated for volatile tables. If you need statistics, you must explicitly run UPDATE STATISTICS."
> However, as shown in the following example, update statistics does not work for volatile tables.
> This was seen on the AdvEnv2.0 v1024 build installed on a 6-node cluster:
> >>create schema mytest;
> --- SQL operation complete.
> >>set schema mytest;
> --- SQL operation complete.
> >>
> >>create volatile table mytable (a int);
> --- SQL operation complete.
> >>insert into mytable values (1);
> --- 1 row(s) inserted.
> >>select * from mytable;
> A
> -----------
>           1
> --- 1 row(s) selected.
> >>update statistics for table mytable on every column;
> *** ERROR[4082] Object TRAFODION.MYTEST.MYTABLE does not exist or is inaccessible.
> *** ERROR[4082] Object TRAFODION.MYTEST.MYTABLE does not exist or is inaccessible.
> *** ERROR[4082] Object TRAFODION.MYTEST.MYTABLE does not exist or is inaccessible.
> --- SQL operation failed with errors.
> >>
> >>drop volatile table mytable cascade;
> --- SQL operation complete.
> >>drop schema mytest cascade;
> --- SQL operation complete.



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