All_rows hint is used in an opposite view to first_rows hint. This hint is mainly used in datawarehouse environments. if You want to instruct the optimizer to choose the best plan in getting all the rows of the query by the minimum resources of the machine.

This hint is designed mainly  to consume less machine resources like CPU, MEMORY as well as DISK I/O.

All_rows mainly targets on througput of the query rather than response time. So this hint is mainly used in dataware house environments rather then transactional environments.

The result of using an All_rows hint on optimizer Plans:

All_rows hint favours more of full table scans rather than index usage.

All_rows hint favour more of Sort_merge or Hash Joins rather than nested loops.

Synatx of ALL_ROWS HINT:

SELECT /*+ ALL_ROWS */ ….. FROM ……