# Subquery Optimization - Case Study 4 - Cont'd

+ `NO_DECORRELATE()` hint tells the optimizer not to perform de-correlation for the correlated subquery.
+ `T1` contains only 10 rows and `T2` has a highly selective index on the `col1`, this approach enhances query performance. See `actRows` and `memory` sections.

![`smart`](Subquery10_aa_001_20240220.png)

------------------------------------------------------------------------------
The `/*+ NO_DECORRELATE() */` hint directs the optimizer to use the `Apply` operator. Specifically, this hint instructs the optimizer not to perform de-correlation on correlated subqueries within the specified query block. It is applicable to `EXISTS`, `IN`, `ANY`, `ALL`, `SOME`, and scalar-valued subqueries containing correlated columns.

When applied, the optimizer refrains from de-correlating correlated columns between the subquery and its outer query block, opting instead to always use the `Apply` operator for execution.

For example, consider the query:

```
SELECT T1.col4, T1.col5, (SELECT SUM(T2.col2) FROM T2 WHERE T2.col1 = T1.col1) FROM T1
```

By changing it to:

```
SELECT T1.col4, T1.col5, (SELECT /*+ NO_DECORRELATE() */ SUM(T2.col2) FROM T2 WHERE T2.col1 = T1.col1) FROM T1
```

the optimizer will no longer rewrite the `Apply` operator as a `left outer join`. Instead, it processes each row from the outer query `SELECT T1.col4, T1.col5, T1.col1 FROM T1` through the inner query `SELECT SUM(T2.col2) FROM T2 WHERE T2.col1 = T1.col1`. Given that `T1` contains only 10 rows and `T2` has a highly selective index on the `col1` column, this approach enhances query performance, resulting in a reduced value in the `actRows` column. Additionally, the `memory` column reflects decreased memory usage.