Oracle-DB: How to enforce the optimizer to do group operations at the most inner level
The most efficient way of processing GROUP BY or SORT operations is often if they are executed only at the really necessary DB objects and the smallest number of rows and columns. The optimizer itself tends to move GROUP BY and SORT operations out to the last operation of a SQL, thus executing these operations on larger data sets than necessary. One way to improve that is to use the so called VIEW_PUSHED_PREDICATE feature forced by the NO_MERGE hint. Prepare example task You have two tables ORDER and POSITION. CREATE TABLE Orders ( ID NUMBER, Customer_ID NUMBER, CreationDate DATE ); INSERT INTO Orders SELECT Level, dbms_random.random, SYSDATE - Level/10000.0 FROM DUAL CONNECT BY Level <= 1000000 ; COMMIT; ALTER TABLE Orders ADD CONSTRAINT Orders_PK PRIMARY KEY (ID); CREATE INDEX Order_Customer ON Orders(Customer_ID); EXEC DBMS_Stats.Gather_Table_Stats(SYS_CONTEXT('USERENV', 'SESSION_USER'), 'ORDERS'); CREATE TABLE Positions ( Order_ID