Modifying a Derived View

You can change the following properties of a derived view, using the statement ALTER VIEW:

  • Its cache configuration, using the parameters CACHE and TIMETOLIVEINCACHE (see section Using the Cache).

  • Virtual DataPort swapping policy configuration through the SWAP, SWAPSIZE and MAXRESULTSIZE parameters (see section Configuring Swapping Policies).

  • DECLARE CACHE INDEX defines a cache index. See more about this in the section Cache Indexes of the Administration Guide.

  • With DATAMOVEMENTPLAN, you can change the data movement plans defined for the view. If one of the subviews has several data movement plans and you want to leave some of them unchanged, leave the parenthesis empty. For example,

    ALTER VIEW test
    DATAMOVEMENTPLAN = { internet_inc: () (JDBC ds1) }
  • Add or remove the primary key constraints of the base view (<add key> and <drop key> definition). See more information about primary keys in the section Primary Keys of Views of the Administration Guide.

  • DELEGATESTATSQUERY: if false, the queries executed to gather the statistics of this view are not pushed down to the source. Instead, Virtual DataPort retrieves all the data (executes a SELECT * FROM table) and executes the aggregation functions locally. If true or not present, the queries are pushed down to the source if possible. Setting this to false is equivalent to selecting the check box “Do not delegate the generation of the statistics” of the “Statistics” tab of the views’ “Advanced” wizard. You can find more information about this option in the section Gathering the Statistics of Views of the Administration Guide.

  • CHECK_INDIRECT_ACCESS: if this option is defined with ON value, the INDIRECT_ACCESS privilege will be checked for the view (Only when the privilege is enabled at server level).

  • LAYOUT: on the administration tool, when editing a derived view, the “Model” tab displays the subviews of the view. This parameter sets the position of these subviews in this tab. If this parameter is not set, the Tool positions each subview in a default location.

    For interface views, it sets the position of the implementation view on the “Implementation” tab.


    Tis parameter does not affect in any way the behavior of the view.

  • Rename the view:

    ALTER VIEW <current name:identifier> RENAME <new name:identifier>;
  • Add, change or remove the primary key of the view (<add key> and <drop key> definitions). See more information about primary keys in the section Primary Keys of Views of the Administration Guide.

  • Add or modify the description of a field, use

    ALTER COLUMN <field name:identifier> ADD ( DESCRIPTION = <description:literal> )

    For example:

    ALTER VIEW order (
        ALTER COLUMN order_id ADD ( DESCRIPTION = 'Unique identifier of the order' )
Syntax of the ALTER VIEW statement
ALTER VIEW <name:identifier>
    [ CACHE {
        | PARTIAL [ EXACT ] [ PRELOAD ]
        | FULL
            [ NOATOMIC [ INVALIDATEBLOCKSIZE <integer> ] ]
            [ WHERE <condition> ]
        | RECREATE
    [ BATCHSIZEINCACHE { <integer> | DEFAULT } ]
    [ TIMETOLIVEINCACHE { <seconds:integer> | DEFAULT | NOEXPIRE } ]
    [ SWAP { ON | OFF | DEFAULT } ]
    [ SWAPSIZE <megabytes:integer> ]
    [ MAXRESULTSIZE <megabytes:integer> ]
    [ <table index clause> ]*
    [ DATAMOVEMENTPLAN = { <data movement plans> } ]
    [ DELEGATESTATSQUERY = <boolean> ]
    [ DESCRIPTION = <literal> ]

| ALTER VIEW <name:identifier> RENAME <new_name:identifier>

| ALTER VIEW <name:identifier>
    ( <alter column clause>+ )

| ALTER VIEW <view:identifier>
    LAYOUT ( <subview1:identifier> = <bounds>
        [, <subview:identifier> = <bounds> ]* )

<alter column clause> ::=
    ALTER COLUMN <field name:identifier> ADD ( DESCRIPTION = <description:literal> )

<table index clause> ::=
      DECLARE CACHE INDEX <name:identifier>
          ON ( <table index field [ ,<table index field> ]* )
    | DELETE CACHE INDEX <name:identifier>

<table index field> :: = <field name:identifier> [ ASC | DESC ]

<data movement plan> ::=
  [ <view name:identifier> : <data movement view plans> ]+

<data movement view plans> ::=
      <data movement view plan>
    | [ ( [ <data movement view plan> ] ) ]+

<data movement view plan> ::=
  JDBC <data source name:identifier> | OFF

<bounds> ::=
  <x1:int>, <y1:int>, <x2:int>, <y2:int>

Customize the Cache Table Creation Command

Denodo uses built-in SQL statements to create the cache tables. Although this should be sufficient for most scenarios, Denodo allows to customize the commands used to create the cache tables. You have to edit the view configuration to set a custom cache table creation template for this view only (see section Cache Table Creation Templates).

ALTER VIEW <view_name>
    cache = '<my template definition>'

After executing the command, Denodo will use this template to create the cache table of the view <view_name>. If the cache table already exists, you have to recreate the cache table to get a new table using the new table creation template. See Cache Table Recreation.

Add feedback