If you provide values for the map and join column properties, the Actuate SQL compiler uses these values to do cost-based query optimization. You can disable cost-based optimization using the pragma EnableCBO.
For example, consider the following query based on SQL Server and Oracle database tables:
SELECT
NATION.N_NAME,
SUM(LINEITEM.L_EXTENDEDPRICE * (1 - LINEITEM.L_DISCOUNT)) AS Revenue
FROM
"/SQL_Server/CUSTOMER.SMA" CUSTOMER,
"/Oracle/ORDERS.SMA" ORDERS,
"/SQL_Server/LINEITEM.SMA" LINEITEM,
"/SQL_Server/SUPPLY.SMA" SUPPLY,
"/Oracle/NATION.SMA" NATION,
"/Oracle/REGION.SMA" REGION
WHERE
CUSTOMER.C_CUSTKEY = ORDERS.O_CUSTKEY
AND LINEITEM.L_ORDERKEY = ORDERS.O_ORDERKEY
AND LINEITEM.L_SUPPKEY = SUPPLY.S_SUPPKEY
AND CUSTOMER.C_NATIONKEY = SUPPLY.S_NATIONKEY
AND SUPPLY.S_NATIONKEY = NATION.N_NATIONKEY
AND NATION.N_REGIONKEY = REGION.R_REGIONKEY
AND REGION.R_NAME = 'ASIA'
AND ORDERS.O_ORDERDATE >= TIMESTAMP '1993-01-01 00:00:00'
AND ORDERS.O_ORDERDATE < TIMESTAMP '1994-01-01 00:00:00'
GROUP BY
NATION.N_NAME
If you provide values for the map and join column properties, part of the query plan looks similar to Figure 6‑1.
Figure 6‑1 Example of part of the query plan for which values for the map and join column properties have been provided
To disable cost-based optimization for the query, set the pragma EnableCBO to False:
PRAGMA "EnableCBO" := 'false'
SELECT
NATION.N_NAME,
SUM(LINEITEM.L_EXTENDEDPRICE * (1 - LINEITEM.L_DISCOUNT)) AS Revenue
FROM
"/SQL_Server/CUSTOMER.SMA" CUSTOMER,
"/Oracle/ORDERS.SMA" ORDERS,
"/SQL_Server/LINEITEM.SMA" LINEITEM,
"/SQL_Server/SUPPLY.SMA" SUPPLY,
"/Oracle/NATION.SMA" NATION,
"/Oracle/REGION.SMA" REGION
WHERE
…
Now this part of the query plan looks similar to Figure 6‑2.
Figure 6‑2 Example of part of a query plan with cost-based optimization disabled
Disabling cost-based optimization changes the join sequence and the join algorithm. The Customer and LineItem, Supply database subqueries switch positions, and the merge join is replaced with a nested loop join.
If you create a query using an information object for which cost-based optimization is disabled, cost-based optimization is disabled for the query as well.
