Modifying a Base View¶
You can change the following properties of a base view, using the statement ALTER TABLE
:
Its internationalization configuration.
Its cache configuration (
CACHE
). IfPARTIAL
orFULL
, the tuples extracted from the source as a result of executing the queries are stored in the local cache. The section Using the Cache explains how each cache mode works.BATCHSIZEINCACHE
. The rows obtained from the source are inserted in cache in batches. This parameter determines the number of rows per batch. The valueDEFAULT
is the Batch size defined for the database in the “Database Management” dialog (menu “Administration”). If the database does not have a specific Batch size, the cache module uses the value defined in the “Cache” dialog (“Administration > Server configuration” menu).Its memory usage configuration (
SWAP
,SWAPSIZE
andMAXRESULTSIZE
). That is, the “swapping to disk” policy for queries that use the base view and involve a large number of tuples. See section Configuring Swapping Policies for more details.The commands
CREATE CACHE INDEX
andDECLARE CACHE INDEX
do exactly the same. Both create a cache index. See more about this in the section Cache Indexes of the Administration Guide.DELETE CACHE INDEX
deletes a cache index from the underlying data source. See more about this in the section Cache Indexes of the Administration Guide.DECLARE VIEW INDEX
defines a view index in VDP that already exists in the data source.CREATE VIEW INDEX
creates a view index in the underlying data source. See more about this in the section Indexes of Views of the Administration Guide.DELETE VIEW INDEX
deletes a view index defined in VDP.DELETE REMOTE VIEW INDEX
deletes a view index from the underlying data source and from VDP. See more about this in the section Indexes of Views of the Administration Guide.Add, delete or modify a search method. Search methods are composed of rules that represent the restrictions with which a specific query should comply in order to be executed using this search method. Furthermore, each search method has an associated wrapper which contains the data necessary to translate the user query for the source and interpret its response. The section Assigning Wrappers to Search Methods provides more details on this matter.
DELEGATESTATSQUERY
: iffalse
, the queries executed to gather the statistics of this base view are not pushed down to the source. Instead, Virtual DataPort retrieves all the data (executes aSELECT * FROM table
) and executes the aggregation functions locally. Iftrue
or not present, the queries are pushed down to the source if possible.Find more information about this option in the section Gathering the Statistics of Views of the Administration Guide.
The clauses
SMART_ONLY
,SMART_THEN_ATSOURCE_THROUGH_VDP
andATSOURCE_THROUGH_VDP_ONLY
control what the Server will do to obtain the statistics of this view:SMART_ONLY
: the Server will obtain the statistics of the view from the system tables of the database. The information that can be obtained from these tables depends on the database to which the data source connects, so not all the statistics may be obtained.Adding this clause is equivalent to clearing the check box “Complete missing statistics…” of the “Statistics” tab.
SMART_THEN_ATSOURCE_THROUGH_VDP
: the Server will obtain the statistics from the system tables of the database. If a statistic cannot be obtained from the catalog, a regularSELECT
query will be executed to obtain it.Adding this clause is equivalent to selecting the check box “Complete missing statistics…” of the “Statistics” tab.
ATSOURCE_THROUGH_VDP_ONLY
: the Server will not try to obtain any statistic from the system tables of the database. Instead, the Server will execute a regularSELECT
query to obtain these statistics.This option is not available from the Administration Tool. It can only be set via VQL.
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 withON
value, theINDIRECT_ACCESS
privilege will be checked for the base view (Only when the privilege is enabled at server level).Rename the view:
ALTER TABLE <name> RENAME...
Add, change or remove the primary key of the base view (
<add key>
and<drop key>
definitions). See more information about primary keys in the section Primary Keys of Views of the Administration Guide.Modify the columns of the base view:
To rename a column, use
ALTER COLUMN <current field name:identifier> RENAME <new field name:identifier>
For example:
ALTER TABLE internet_inc ( ALTER COLUMN iinc_id RENAME incidence_id );
To modify the type of a column, use
ALTER COLUMN <name:identifier> MODIFY <new type:identifier> <is field nullable>
.When you change the type of a field, the current source type properties of the field get lost. If you need them, you have to add them again.
For example:
ALTER TABLE internet_inc ( ALTER COLUMN incidence_id MODIFY decimal FALSE );
To add or modify a source type property of a field, use
ALTER COLUMN <field name:identifier> ADD ( <source type property:identifier> = <source type value:literal> )
If the property “source type property” already exists, the value provided replaces the existing one.
For example:
ALTER TABLE internet_inc ( ALTER COLUMN summary ADD ( sourcetypesize = '1000') );
To add or modify the description of a field, use
ALTER COLUMN <field name:identifier> ADD ( DESCRIPTION = <description:literal> )
For example:
ALTER TABLE internet_inc ( ALTER COLUMN iinc_id ADD ( DESCRIPTION = 'Identifier of the incident' ) );
ALTER TABLE <name:identifier>
[ I18N <name:identifier> ]
[ CACHE {
OFF
| PARTIAL [ EXACT ] [ PRELOAD ]
| FULL [ INCREMENTAL <incremental_condition:literal> ]
| INVALIDATE [ ON CASCADE ]
[ NOATOMIC [ INVALIDATEBLOCKSIZE <integer> ] ]
[ WHERE <condition> ]
| RECREATE
}
]
[ BATCHSIZEINCACHE { <integer> | DEFAULT } ]
[ TIMETOLIVEINCACHE { <seconds:integer> | DEFAULT | NOEXPIRE } ]
[ CREATE_TABLE_TEMPLATES (
[ CACHE = <create table template> , ]
[ REMOTE_TABLE = <create table template> ]
) ]
[ SWAP { ON | OFF | DEFAULT} ]
[ SWAPSIZE <megabytes:integer> ]
[ MAXRESULTSIZE <megabytes:integer> ]
[ <table index clause> ]*
[ <table search method clause> ]*
[ { <add key> | <drop key> }]
[ DELEGATESTATSQUERY = <boolean> ]
[ {
SMART_ONLY
| SMART_THEN_ATSOURCE_THROUGH_VDP
| ATSOURCE_THROUGH_VDP_ONLY
} ]
[ DESCRIPTION = <literal> ]
[ CHECK_INDIRECT_ACCESS { ON | OFF} ]
| ALTER TABLE <name:identifier> ( <alter column clause>+ )
| ALTER TABLE <name:identifier> RENAME <new_name:identifier>
<table index clause> ::=
DECLARE { CACHE | VIEW } [ CLUSTER | HASH | OTHER ]
INDEX <index_name:identifier> [ PRIMARY ] [ UNIQUE ]
ON ( <table index field [ ,<table index field> ]* )
| CREATE [ OR REPLACE ] { CACHE | VIEW }
INDEX <index_name:identifier> [ UNIQUE ]
ON ( <table index field> [ ,<table index field> ]* )
| DELETE CACHE INDEX <index_name:identifier>
| DELETE [ REMOTE ] VIEW INDEX <index_name:identifier>
<table index field> :: = <index_field_name:identifier> [ ASC | DESC ]
<table search method clause> ::=
ADD SEARCHMETHOD <name:identifier> (
[ I18N <name:identifier> ]
[ CONSTRAINTS ( [ <constraint clause> ]+ ) ]
[ OUTPUTLIST ( <output clause> ) ]
[ <wrapper clause> ]
)
| ALTER SEARCHMETHOD <name:identifier> (
[ I18N { <name:identifier> | DEFAULT } ]
[ CONSTRAINTS ( [ <constraint clause> ]+ ) ]
[ OUTPUTLIST ( <output clause> ) ]
[ <wrapper clause> ]
)
| DROP SEARCHMETHOD <name:identifier>
<alter column clause> ::=
{ ALTER COLUMN <name:identifier> RENAME <new name:identifier>
| ALTER COLUMN <name:identifier> MODIFY <new type:identifier>
[ <nullable clause> ]
| ALTER COLUMN <field name:identifier> ADD (
<source type property:identifier> = <source type value:literal>
)
| ALTER COLUMN <field name:identifier> ADD (
DESCRIPTION = <description:literal> )
}
<nullable clause> ::= { TRUE | FALSE }
<constraint clause> ::=
ADD <field> ( [ <operator> [, <operator> ]* ] )
{
<obligatoriness> <multiplicity>
[ ( <value_1:value> [ , <value_i:value> ]* ) ]
|
NOS { ZERO | 0 } ()
}
| DROP <integer>
<output clause> ::=
<field> [ ,<field> ]*
<wrapper clause> ::=
WRAPPER ( <wrapper type> <name:identifier> )
ALTERNATIVE_WRAPPERS ( JDBC <wrapper_name:identifier> [, JDBC <wrapper_name:identifier> ]* ) ]
| DROP WRAPPER
<wrapper type> ::=
CUSTOM
| DF
| ESSBASE
| JDBC
| JSON
| LDAP
| MONGODB
| ODBC
| SALESFORCE
| SAPBWBAPI
| SAPERP
| WS
| XML
<field> ::=
<identifier>[.<identifier>]*
<obligatoriness> ::=
OPT
| OBL
<multiplicity> ::=
ZERO
| ONE
| ANY
| <integer>
<operator> includes “any” to represent any operator.
<add key> ::=
ADD [ CONSTRAINT <name:literal> ]
PRIMARY KEY ( <field_name:literal> [, <field_name:literal>]*)
<drop key> ::=
DROP CONSTRAINT <name:literal>
| DROP PRIMARY KEY
<create table template> ::= <template_definition:literal>
[ DEFAULT( <parameter definition> [, <parameter definition> ]* ) ]
Note: You can use parameters within the template definition. Syntax: @{parameter_name}
<parameter definition> ::= <parameter_name:literal> = <parameter_value:literal>
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 table. 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 TABLE <base_view_name>
CREATE_TABLE_TEMPLATES (
cache = '<my template definition>'
)
After executing the command, Denodo will use this template to create the cache table of the view <base_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.
Privileges Required to Create, Edit and Delete an Index in the underlying data source
The commands used to manage the underlying indexes require the following privileges:
“Connect” over the Virtual DataPort database of the JDBC data source used to create the base view.
“Execute” over the JDBC data source used to create the base view.
“Connect” over the Virtual DataPort database in which the base view is located.
“Write” over the base view in which the index will be created.
The user account set in the JDBC data source has to be able to create indexes in the underlying database.