聚合前的结果集越小越有优化空间，你光一个语句啥也看不出来

正常来说, 这个SQL已经没有优化空间了.  但是如果有特殊情况, 比如item_id的唯一值比较少, 就可以通过一些改写操作, 再配合索引, 大幅提高执行效率.   

你的数据库版本是多少(跟sql写法是否支持, row_number分析函数是否开启了优化有关), 表有多大, 当前执行时间是多少, 希望的执行时间是多少, 表上字段的数据分布情况如何,是否有not null约束,  表上的索引情况等,  有了这些信息, 才能更好的分析和解决问题.

表数量：

SQL> select count(1) from table1;
  COUNT(1)
----------
    967891

数据库版本：
oracle 19.17

当前执行时间：> 2分钟

希望执行时间： < 10秒
索引情况

表结构

从MySQL角度看。这个SQL可以这样优化

如果 每个ITEM_ID分组下的SYNC_DATE是唯一的

则可以建立一个索引idx(ITEM_ID,SYNC_DATE)，先实现
select ITEM_ID,max(SYNC_DATE ) from L group by ITEM_ID 的松散扫描。 再用这个结果集去关联原L表。这样原L表的其它等值条件也可以使用索引了。

速度还是会比窗口函数快很多。

根据表记录数不到100万, 执行时间2分钟的事实, 可以推断出字段的平均行长应该比较长, 占的空间较多(应该占用了几个G的磁盘空间); 磁盘是普通磁盘, 不是SSD盘; PGA不够大, 因为row_number分析函数需要较多的内存, 内存放不下就要写和读临时表空间 .

你的这个版本的row_number算法是做了改进的, 但是需要在session 级别设置一个fix_control, 能减少pga的使用, 也就减少了读写临时表空间的数据量. 

另外, 使用并行能增加pga的使用, 进一步减少分析函数对临时表空间的读写. 

需要你再查查 select count(distinct ITEM_ID) from table1;   这个值如果不大, 还可以改写SQL, 这种情况提速会比较多; 否则就只能通过fix_control和并行来提速, 想达到10秒以下比较困难.

SQL> select count(ITEM_ID),count(distinct ITEM_ID) from table1;