# Cost-Based Optimization Possible Decisions - Example
```
SELECT t1.c1, t2.c1, COUNT(*)
FROM t1 JOIN t2 ON t1.id = t2.id
WHERE t1.c2 LIKE '001%' AND t2.c2 = 'R007'
GROUP BY t1.c1, t2.c1
```
![`smart`](CBODecision01_aa_003_20240313.png)
------------------------------------------------------------------------------
Beginning from the buttom, the optimizer initially determines the data access method for tables, indexes, or partitions. The cost-based optimization process must choose between performing an index scan or a full table scan, based on the cardinality calculated from statistics. Additionally, when assessing multiple indexes, it evaluates various index statistics, such as the number of distinct values for different prefix columns, to select the most suitable composite index.

For example, on the slide
```
SELECT t1.c1, t2.c1, COUNT(*)
FROM t1 JOIN t2 ON t1.id = t2.id
WHERE t1.c2 LIKE '001%' AND t2.c2 = 'R007'
GROUP BY t1.c1, t2.c1
```

For the aggregation operator in a query like `GROUP BY t1.c1, t2.c1`, the execution plan might offer two options: `HASH_AGG` or `STREAM_AGG`. For the join operation between `t1` and `t2`, three methods are available: `HASH JOIN`, `MERGE JOIN`, and `INDEX JOIN`. Depending on the filter condition in the `WHERE` clause and the existing indexes on `t1` and `t2`, filtering can be performed using either `INDEX SCAN` or `FULL TABLE SCAN`. To achieve the optimal execution plan, cost-based optimization must rely not only on rule-based logical optimization but also on the evaluation of cluster resources and statistical information.