create or replace table t1 (a int, b int, c int, index(a, b, c));

create or replace table t2 (a int, b int, c int, index(a, b, c));

insert into t1 select seq, seq, seq from seq_1_to_100000;

insert into t2 select seq, seq, seq from seq_1_to_100000;

explain

select * from 

(

select t1.a a, t2.b b, t2.c, sum(t2.c) over ( partition by a,b,c )

from t1 join t2

where 

t1.a=t2.a and t1.b=t2.b

)

v1 where (a, b, c) = (50000,50000,50000);

+------+-------------+------------+-------+---------------+------+---------+-------------------------------------+--------+-------------------------------------------+

| id   | select_type | table      | type  | possible_keys | key  | key_len | ref                                 | rows   | Extra                                     |

+------+-------------+------------+-------+---------------+------+---------+-------------------------------------+--------+-------------------------------------------+

|    1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL | NULL    | NULL                                | 100000 | Using where                               |

|    2 | DERIVED     | t1         | index | a             | a    | 15      | NULL                                | 100000 | Using where; Using index; Using temporary |

|    2 | DERIVED     | t2         | ref   | a             | a    | 10      | tdsdbsgowner.t1.a,tdsdbsgowner.t1.b |      1 | Using index                               |

+------+-------------+------------+-------+---------------+------+---------+-------------------------------------+--------+-------------------------------------------+