W3cubDocs

/MariaDB

CONNECT TBL Table Type: Table List

This type allows defining a table as a list of tables of any engine and type. This is more flexible than multiple tables that must be all of the same file type. This type does, but is more powerful than, what is done with the MERGE engine.

The list of the columns of the TBL table may not necessarily include all the columns of the tables of the list. If the name of some columns is different in the sub-tables, the column to use can be specified by its position given by the FLAG option of the column. If the ACCEPT option is set to true (Y or 1) columns that do not exist in some of the sub-tables are accepted and their value will be null or pseudo-null (this depends on the nullability of the column) for the tables not having this column. The column types can also be different and an automatic conversion will be done if necessary.

Note: If not specified, the column definitions are retrieved from the first table of the table list.

The default database of the sub-tables is the current database or if not, can be specified in the DBNAME option. For the tables that are not in the default database, this can be specified in the table list. For instance, to create a table based on the French table employe in the current database and on the English table employee of the db2 database, the syntax of the create statement can be:

CREATE TABLE allemp (
  SERIALNO char(5) NOT NULL flag=1,
  NAME varchar(12) NOT NULL flag=2,
  SEX smallint(1),
  TITLE varchar(15) NOT NULL flag=3,
  MANAGER char(5) DEFAULT NULL flag=4,
  DEPARTMENT char(4) NOT NULL flag=5,
  SECRETARY char(5) DEFAULT NULL flag=6,
  SALARY double(8,2) NOT NULL flag=7)
ENGINE=CONNECT table_type=TBL
table_list='employe,db2.employee' option_list='Accept=1';

The search for columns in sub tables is done by name and, if they exist with a different name, by their position given by a not null FLAG option. Column sex exists only in the English table (FLAG is 0). Its values will null value for the French table.

For instance, the query:

select name, sex, title, salary from allemp where department = 318;

Can reply:

NAME SEX TITLE SALARY
BARBOUD NULL VENDEUR 9700.00
MARCHANT NULL VENDEUR 8800.00
MINIARD NULL ADMINISTRATIF 7500.00
POUPIN NULL INGENIEUR 7450.00
ANTERPE NULL INGENIEUR 6850.00
LOULOUTE NULL SECRETAIRE 4900.00
TARTINE NULL OPERATRICE 2800.00
WERTHER NULL DIRECTEUR 14500.00
VOITURIN NULL VENDEUR 10130.00
BANCROFT 2 SALESMAN 9600.00
MERCHANT 1 SALESMAN 8700.00
SHRINKY 2 ADMINISTRATOR 7500.00
WALTER 1 ENGINEER 7400.00
TONGHO 1 ENGINEER 6800.00
HONEY 2 SECRETARY 4900.00
PLUMHEAD 2 TYPIST 2800.00
WERTHER 1 DIRECTOR 14500.00
WHEELFOR 1 SALESMAN 10030.00

The first 9 rows, coming from the French table, have a null for the sex value. They would have 0 if the sex column had been created NOT NULL.

Sub-tables of not CONNECT engines

Sub-tables are accessed as PROXY tables. For not CONNECT sub-tables that are accessed via the MySQL API, it is possible like with PROXY to change the MYSQL default options. Of course, this will apply to all not CONNECT tables of the list.

Using the TABID special column

The TABID special column can be used to see from which table the rows come from and to restrict the access to only some of the sub-tables.

Let us see the following example where t1 and t2 are MyISAM tables similar to the ones given in the MERGE description:

create table xt1 (
  a int(11) not null,
  message char(20))
engine=CONNECT table_type=MYSQL tabname='t1'
option_list='database=test,user=root';

create table xt2 (
  a int(11) not null,
  message char(20))
engine=CONNECT table_type=MYSQL tabname='t2'
option_list='database=test,user=root';

create table toto (
  tabname char(8) not null special='TABID',
  a int(11) not null,
  message char(20))
engine=CONNECT table_type=TBL table_list='xt1,xt2';

select * from total;

The result returned by the SELECT statement is:

tabname a message
xt1 1 Testing
xt1 2 table
xt1 3 t1
xt2 1 Testing
xt2 2 table
xt2 3 t2

Now if you send the query:

select * from total where tabname = 'xt2';

CONNECT will analyze the where clause and only read the xt1 table. This can save time if you want to retrieve only a few sub-tables from a TBL table containing many sub-tables.

Parallel Execution

Parallel Execution is currently unavailable until some bugs are fixed.

When the sub-tables are located on different servers, it is possible to execute the remote queries simultaneously instead of sequentially. To enable this, set the thread option to yes.

Additional options available for this table type:

Option Description
Maxerr The max number of missing tables in the table list before an error is raised. Defaults to 0.
Accept If true, missing columns are accepted and return null values. Defaults to false.
Thread If true, enables parallel execution of remote sub-tables.

These options can be specified in the OPTION_LIST.

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.

© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/connect-tbl-table-type-table-list/