# Cost-Based Optimization Join Reorder Decisions - Example
```10
~!@t1.a = t2.a@!~ AND
~!@t1.a = t3.a@!~ AND
~!@t3.a = t2.a@!~ AND 
t1.b ~!@=@!~ 700 AND 
t2.c ~!@>@!~ 200 AND 
t3.d ~!@LIKE@!~ 'd001%'
```
![`smart`](CostBasedSQLPhysicalOptimization03_aa_002_20230922.png)
------------------------------------------------------------------------------
To estimate the cost of executing a task, it is essential to consider relevant statistical information, including the row count of the table, column statistics, histograms, and the number of distinct values (NDV) in the columns. A more detailed discussion of statistical information management will be provided in the chapter dedicated to this topic.

This slide illustrates a common scenario for the cost-based optimization of join reordering, where cost estimation is based on the number of rows in the source table. The optimizer may use one of the following methods for join reordering:
- Greedy Algorithm: This method estimates the join cost by evaluating the joining of the table with the fewest rows with other tables and selecting the join path with the lowest cost. The process is repeated until all tables have been joined.
- Dynamic Programming: This approach involves trying every possible combination of joins through exhaustive enumeration and computing the intermediate result sets.

In the case of dynamic programming reordering, consider a table join query involving tables `t1`, `t2`, and `t3`, with the condition `t1.a = t2.a AND t1.a = t3.a AND t3.a = t2.a AND t1.b = 700 AND t2.c > 200 AND t3.d LIKE 'd001%'`:

- **Stage 1:** Determine the data access paths for tables `t1`, `t2`, and `t3` based on the conditions `t1.b = 700 AND t2.c > 200 AND t3.d LIKE 'd001%'`, and estimate the cardinalities for each table after filtering.
  
- **Stage 2:** For each pair of tables participating in a join, identify the optimal join method. For instance, after joining `t1` with `t2`, the cardinality is 800; with `t1` and `t3`, it's 200; and with `t2` and `t3`, it's 900. Thus, joining `t1` with `t3` is the optimal method.
  
- **Stage 3:** Repeat stages 1 and 2 for the intermediate result set and the remaining tables. In the illustration, the intermediate result of joining `t1` with `t3` is finally joined with `t2`.