CONNECT Table Types - XCOL Table Type

You are viewing an old version of this article. View the current version here.

XCOL tables are based on another table or view, like PROXY tables. This type can be used when the object table has a column that contains a list of values.

Suppose we have a 'children' table that can be displayed as:

namechildlist
SophieVivian, Antony
LisbethLucy,Charles,Diana
Corinne
ClaudeMarc
JanetArthur, Sandra, Peter, John

We can have a different view on these data, where each child will be associated with his/her mother by creating an XCOL table by:

CREATE TABLE xchild (
  mother char(12) NOT NULL,
  child char(12) DEFAULT NULL flag=2
) ENGINE=CONNECT table_type=XCOL tabname='chlist'
option_list='colname=child';

The COLNAME option specifies the name of the column receiving the list items. This will return from:

select * from xchild;

The requested view:

motherchild
SophiaVivian
SophiaAntony
LisbethLucy
LisbethCharles
LisbethDiana
CorinneNULL
ClaudeMarc
JanetArthur
JanetSandra
JanetPeter
JanetJohn

Several things should be noted here:

  • When the original children field is void, what happens depends on the NULL specification of the "multiple" column. If it is nullable, like here, a void string will generate a NULL value. However, if the column is not nullable, no row will be generated at all.
  • Blanks after the separator are ignored.
  • No copy of the original data was done. Both tables use the same source data.
  • Specifying the column definitions in the CREATE TABLE statement is optional.

The "multiple" column child can be used as any other column. For instance:

select * from xchild where substr(child,1,1) = 'A';

This will return:

MotherChild
SophiaAntony
JanetArthur

If a query does not involve the "multiple" column, no row multiplication will be done. For instance:

select mother from xchild;

This will just return all the mothers:

mother
Sophia
Lisbeth
Corinne
Claude
Janet

The same occurs with other types of select statements, for instance:

select count(*) from xchild;      -- returns 5
select count(child) from xchild;  -- returns 10
select count(mother) from xchild; -- returns 5

Grouping also gives different result:

select mother, count(*) from xchild group by mother;

Replies:

mothercount(*)
Claude1
Corinne1
Janet1
Lisbeth1
Sophia1

While the query:

select mother, count(child) from xchild group by mother;

Gives the more interesting result:

mothercount(child)
Claude1
Corinne0
Janet4
Lisbeth3
Sophia2

Some more options are available for this table type:

OptionDescription
Sep_charThe separator character used in the "multiple" column, defaults to the comma.
MultIndicates the max number of multiple items. It is used to internally calculate the max size of the table and defaults to 10. (To be specified in OPTION_LIST).

XCOL tables based on specified views

Instead of specifying a source table name via the TABNAME option, it is possible to retrieve data from a “view” whose definition is given in a new option SRCDEF . For instance:

create table xsvars engine=connect table_type=XCOL
srcdef='show variables like "optimizer_switch"'
option_list='Colname=Value';

Then, for instance:

select value from xsvars limit 10;

This will display something like:

value
index_merge=on
index_merge_union=on
index_merge_sort_union=on
index_merge_intersection=on
index_merge_sort_intersection=off
engine_condition_pushdown=off
index_condition_pushdown=on
derived_merge=on
derived_with_keys=on
firstmatch=on

Note: All XCOL tables are read only.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.