# Subquery Optimization - Case Study 1 - Cont'd

+ The `/*+ SEMI_JOIN_REWRITE() */` hint tells the optimizer to rewrite the semi-join query to an ordinary join query
+ Currently, this hint only works for `EXISTS` subqueries
+ Query rewritten to 

```
SELECT T1.* FROM T1, (SELECT T2.col1 FROM T2 GROUP BY T2.col1) TMP WHERE T1.col1 = TMP.col1
```

![`smart`](Subquery05_aa_001_20240220.png)

------------------------------------------------------------------------------
By using the `/*+ SEMI_JOIN_REWRITE() */` hint, you can guide the optimizer to convert a `semi join` in the query statement to an inner join. Currently, this hint is applicable only for `EXISTS` subqueries.

For instance, altering the query from 
```
SELECT * FROM T1 WHERE EXISTS (SELECT col1 FROM T2 WHERE T2.col1 = T1.col1)
```
to 
```
SELECT * FROM T1 WHERE EXISTS (SELECT /*+ SEMI_JOIN_REWRITE() */ col1 FROM T2 WHERE T2.col1 = T1.col1)
```
enables the optimizer to potentially avoid using a `semi join` by rewriting the SQL as:
```
SELECT T1.* FROM T1, (SELECT T2.col1 FROM T2 GROUP BY T2.col1) TMP WHERE T1.col1 = TMP.col1
```
This rewrite makes it possible to select table `T2` as the driver for an index join or index hash join.

As demonstrated, this optimized SQL statement improves efficiency.