Modifying the CTE Configuration

Scenario

If a table or common table expression (CTE) contained in a query appears multiple times and has the same projection and filter, you can enable the CTE reuse function to cache data in memory. In this way, you do not need to read data from disks for multiple times, reducing the time required for query execution.

Procedure

1. Log in to FusionInsight Manager.
2. Choose Cluster > Services > HetuEngine > Configurations > All Configurations and configure related parameters by referring to Table 1.

   Table 1 CTE configuration parameters

   Parameter	Description	Recommended Value	Default Value	Parameter File
   optimizer.reuse-table-scan	Whether to enable the CTE table data reuse function.	true	false	coordinator.config.properties and worker.config.properties
   experimental.spill-reuse-tablescan	Whether to enable the function of spilling memory to disks during tablescan reuse.	true	false	coordinator.config.properties and worker.config.properties
   optimizer.cte-reuse-enabled	Whether to enable CTE reuse. If this function is enabled, CTE is executed only once irrespective of the number of times the same CTE is being used in the main query.	true	false	coordinator.config.properties and worker.config.properties
   dynamic-filtering-max-per-driver-size	Maximum volume of data that can be collected by each driver when dynamic filtering starts.	100MB	1MB	coordinator.config.properties and worker.config.properties

3. Click Save.
4. Choose Cluster > Services > HetuEngine > More > Restart Service and enter the password to restart the HetuEngine service for the parameters to take effect.
5. Restart the HetuEngine compute instance that is running.
   1. Log in to FusionInsight Manager as a HetuEngine administrator and choose Cluster > Services > HetuEngine.
   2. In the displayedDashboard tab, find the Basic Information area, and click the link next to HSConsole WebUI.
   3. Click Compute Instances, select the running HetuEngine instance, click Restart, and operate as prompted.