solidDB® advanced replication uses publications for downloading incremental data from the master to replica databases. In a publication, you define the data for propagation from the master to the replica. Using the CREATE PUBLICATION statement, you specify the tables and the search criteria for selecting data to the replica. For details on publications, see Creating publications.
Below is a simple publication definition example:
CREATE PUBLICATION configuration_of_device (device_name VARCHAR) BEGIN RESULT SET FOR device BEGIN SELECT * FROM device WHERE name = :device_name; RESULT SET FOR device_cfg_parameter BEGIN SELECT * FROM device_cfg_parameter WHERE device_id = device.id; END END END
Internally, the queries of the RESULT SET FOR paragraphs are executed as regular SELECTs. Nested result sets always produce a join between an outer and inner result set. Therefore, in optimizing performance, the same indexing rules apply here as with other queries, which means you should:
▪create an index on columns of large tables that are used as search criteria
▪create an index on columns that are used for joins in the nested result set
In addition, we recommend that you avoid nesting result sets in publication definitions. See the following sections of for examples of nested vs. unnested result sets:
You can extract the SQL that is actually generated from the publication definitions by setting SQL trace on in the master database using the following command:
ADMIN COMMAND 'trace on sql'
The output of the trace goes to the standard trace file of solidDB®. The default name of the file is soltrace.out.