SELECT

SELECT#

The user can either use the location descriptor('@') or the pass-through style to SELECT a remote database object with Altibase Database Link.

We recommend to use the REMOTE_TABLE keyword of the pass-through method with Altibase Database Link. The location descriptor('@') is supported for compatibility with older versions.

Both methods can only use database links in the FROM clause.

For detailed information on string notation used in the SELECT clause, refer to "Using the Character Strings" in the General Reference.

Location Descriptor#

Statements that use the location descriptor '@' are supported to maintain compatibility with older versions.

The location descriptor '@' can be used as shown below, to indicate that the given object exists in the remote server:

SELECT * FROM t1@link1;

Queries that include location descriptors retrieve all records in the table of the remote server to the local server, as for the previous version. Therefore, the use of the REMOTE_TABLE keyword can enhance the processing speed, according to the characteristics in a query.

Since queries using location descriptors, as the one below, retrieve all records of the remote table to the local server, they are accompanied with the additional burden of network costs, local server operational costs and, when necessary, disk I/O costs. However, the use of the REMOTE_TABLE keyword delivers better performance; with the REMOTE_TABLE keyword, the query is processed on the remote server and the local server retrieves only one record as the result.

Table name: T1
Table schema:
C1 VARCHAR(1024),
C2 VARCHAR(1024),
C3 VARCHAR(1024),
...
C50 INTEGER,
...
C100 VARCHAR(1024),
Total number of records: 1,000,000 (1 record has the c50 column value of 50)

Using the location descriptor:#

SELECT c50, c100 FROM t1@link1 WHERE c50 = 50;

Using the REMOTE_TABLE keyword:#

SELECT * FROM REMOTE_TABLE( link1, 'select c50, c100 from t1 where c50 = 50' );

In conclusion, the use of the REMOTE_TABLE keyword, rather than the location descriptor, is recommended for queries retrieving the same results using database links.

Examples

<Query 1> SELECT all of table t1 which exists in the remote server, pointed to by link1, using the location descriptor.

SELECT * FROM t1@link1;

<Query 2> Using the location descriptor, SELECT columns a1, a2 in the remote table.

SELECT a1, a2 FROM ( SELECT * FROM t1@link1 );

REMOTE_TABLE#

With the REMOTE_TABLE keyword of the pass-through style, the SELECT statement can be executed on the remote server.

When a query that contains the REMOTE_TABLE keyword is executed on the remote server, the query result is written to a memory buffer, which is passed to the query processor, and then dropped. A query that needs to repeatedly access dropped results (e.g., join operations) must be re-executed on the remote server

The syntax for the REMOTE_TABLE keyword is as follows:

REMOTE_TABLE (
    dblink_name     IN VARCHAR,
    statement_text  IN VARCHAR )

dblink_name: the name of the database link object.
statement_text: the SELECT statement to be executed on the remote server.

Examples

<Query 1> Using the REMOTE_TABLE keyword, SELECT all the rows from table t1 which exists in the remote server.

SELECT * FROM REMOTE_TABLE( link1, 'select * from t1' );

<Query 2> Using the REMOTE_TABLE keyword, SELECT columns a1, a2 from the remote table.

SELECT * FROM REMOTE_TABLE( link1, 'select a1, a2 from t1' );

REMOTE_TABLE_STORE#

Altibase stores the results of queries that contain the REMOTE_TABLE keyword and have been executed on the remote server into disk temporary tables to allow repeated access.

WHERE Clause#

When querying the remote server using database links, the WHERE clause can be used in the same manner as when querying with the SELECT statement on a local server.

Since the use of the location descriptor or the REMOTE_TABLE keyword is invalid in the WHERE clause, an alias which is specified in the FROM clause should be used in the WHERE clause.

Examples

<Query 1> SELECT a row with a value larger than 100, from column a1 in table t1 of the remote server pointed to by link1.

SELECT * 
  FROM REMOTE_TABLE( link1, 'select * from t1 where a1 > 100' );

or

SELECT * 
  FROM REMOTE_TABLE( link1, 'select * from t1' ) t1_alias
 WHERE t1_alias.a1 > 100;

or

SELECT * FROM t1@link1 WHERE a1 > 100;

<Query 2> SELECT the name of the employee working for the department 'rnd', from all employees stored in the table emp2 of the remote server and the table emp1 of the local server pointed to by link1.

SELECT emp_name
FROM ( SELECT emp_no, emp_name FROM emp1
    UNION ALL
    SELECT emp_no, emp_name FROM REMOTE_TABLE( link1, 'select emp_no, emp_name from emp2' ) ) v1,
    dept
WHERE v1.emp_no = dept.emp_no AND dept.dept_name = 'rnd';

or

SELECT emp_name
FROM ( SELECT emp_no, emp_name FROM emp1
    UNION ALL
    SELECT emp_no, emp_name FROM emp2@link1 ) v1,
    dept
WHERE v1.emp_no = dept.emp_no AND dept.dept_name = 'rnd';

Other SELECT Statement Features#

Database links support the use of joins, subqueries, set operators and aggregation functions in SELECT statements. Also, when a SELECT statement exists in a DDL or DML statement in the form of a subquery, database links can also be used in the subquery statement.

Examples

<Query 1> SELECT the unduplicated values in column a1 in table t1 of the remote server pointed to by link1.

SELECT * FROM REMOTE_TABLE( link1, 'select distinct a1 from t1' );

or

SELECT DISTINCT a1 FROM t1@link1;

<Query 2> Find the departments of all employees by joining tables t_member and t_dept of the remote server pointed to by link1, and group the departments with an ID value of 0 or larger by department ID, and return the number of employees and their average age for each department.

SELECT t1.dept_id, COUNT(*), AVG(age)
FROM REMOTE_TABLE( link1, 'select * from t_member' ) t1,
   REMOTE_TABLE( link1, 'select * from t_dept' ) t2,
WHERE t1.dept_id = t2.dept_id
GROUP BY t1.dept_id
HAVING t1.dept_id >= 0;

or

SELECT t1.dept_id, COUNT(*), AVG(age)
FROM t_member@link1 t1,
   t_dept@link1 t2
WHERE t1.dept_id = t2.dept_id
GROUP BY t1.dept_id
HAVING t1.dept_id >= 0;

<Query 3> Find all employees by joining tables t_member and t_dept of the remote server pointed to by link1, and select three employees under the age of 30, in descending order of ID, and return their names, ages, and the sum of their ages.

SELECT t1.name, t1.age
    ( SELECT * FROM REMOTE_TABLE( link1, 'select sum(age) from t_member' ) ) sum
FROM REMOTE_TABLE( link1, 'select dept_id, member_id, dept_name, age from t_member where age < 30' ) t1,
   REMOTE_TABLE( link1, 'select dept_id, dept_name, from t_dept' ) t2
WHERE t1.dept_id = t2.dept_id AND t1.age < 30
ORDER BY t1.member_id DESC LIMIT 3;

or

SELECT t1.name, t1.age
    ( SELECT SUM(age) FROM t_member@link1 ) sum
FROM t_member@link1 t1,
    ( SELECT dept_name, dept_id FROM t_dept@link1 ) t2
WHERE t1.dept_id = t2.dept_id AND t1.age < 30
ORDER BY t1.member_id DESC LIMIT 3;

<Query 4> Retrieve the name and age in table t2 of the remote server, pointed to by link1, and insert the values into table t1 of the local server.

INSERT INTO t1 SELECT * FROM REMOTE_TABLE( link1, 'select name, age from t2' );

or

INSERT INTO t1 SELECT name, age FROM t2@link1;

Administrator

Reference

Developer

Tools

External Tools

SELECT

SELECT#

Location Descriptor#

Using the location descriptor:#

Using the REMOTE_TABLE keyword:#

REMOTE_TABLE#

REMOTE_TABLE_STORE#

WHERE Clause#

Other SELECT Statement Features#