Oracle JOIN elimination using FK

ora_fk_join_elimination.sql

SQL> ALTER TABLE CLIENTS_TEST ADD CONSTRAINT CLIENTS_TEST_PK PRIMARY KEY (CLIENT_ID)
TABLE altered.
 
SQL> CREATE TABLE CLIENTS_DIM (
      CLIENT_ID   NUMBER
    , ATTR1       NUMBER
    , ATTR2       NUMBER
)
TABLE created.
 
SQL> INSERT INTO CLIENTS_DIM
SELECT ROWNUM, 10,20
FROM DUAL
CONNECT BY ROWNUM<100
99 ROWS created.
 
SQL> ALTER TABLE CLIENTS_DIM ADD CONSTRAINT CLIENTS_DIM_PK PRIMARY KEY (CLIENT_ID)
TABLE altered.
 
SQL> ALTER TABLE CLIENTS_DIM
    ADD CONSTRAINT CLIENTS_DIM_FK
        FOREIGN KEY (CLIENT_ID) REFERENCES CLIENTS_TEST (CLIENT_ID)
TABLE altered.
 
SQL> EXPLAIN PLAN FOR
SELECT ATTR1,CLIENT_ID FROM CLIENTS_DIM JOIN CLIENTS_TEST USING (CLIENT_ID)
EXPLAIN complete.
SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY())
/*                                                                                                                                                                                                                                                                                                            
---------------------------------------------------------------------------------                                                                                                                                                                                                                          
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                                          
---------------------------------------------------------------------------------                                                                                                                                                                                                                          
|   0 | SELECT STATEMENT  |             |    99 |  2574 |     2   (0)| 00:00:01 |                                                                                                                                                                                                                          
|   1 |  TABLE ACCESS FULL| CLIENTS_DIM |    99 |  2574 |     2   (0)| 00:00:01 |                                                                                                                                                                                                                          
---------------------------------------------------------------------------------
*/