优化sql 有没有回优化查询sql的

Question

            select TABLE_NAME
            from user_tables
            where REGEXP_LIKE(TABLE_NAME ,'^' || #{tablePrefix} || '[0-9]+$')
            ORDER BY TABLE_NAME DESC
            OFFSET 0 Row FETCH NEXT 1 ROWS  ONLY

sql 如上 查询时间20s 作用是 查询最新的天表 有什么办法优化

Answer 1

还需要了解一点信息

1. user_tables的记录数有多少？
2. 执行这个sql时，耗时最长的几个等待事件是什么？
3. 不带order by和行数限制时，查询速度怎么样？

分析：

1. user_tables中可以查询到当前用户的所有表，而且理论上，表再多也不会多到哪里去（当然不排除某些极端情况），但是由于这实际上是个视图，而且需要访问到数据字典信息，因此不排除当前存在数据字典被占用的情况而导致查询缓慢，所以要看下上面说的1和2的情况
2. order by 的时候会有一些额外的开销，但不清楚你实际生产是个什么情况，所以需要用上面3的方式来做个对比

另外，可以尝试下面这个sql，但是可能需要对用到的两个表进行查询授权

select NAME
  from sys.obj$
 WHERE  TYPE# = 2
   AND OWNER# = (SELECT USER# FROM sys.user$ u WHERE NAME = USER)
   AND  REGEXP_LIKE(NAME ,'^' || #{tablePrefix}|| '[0-9]+$')
     ORDER BY NAME DESC
       OFFSET 0 Row FETCH NEXT 1 ROWS  ONLY

这个是把视图拆开，只获取必要信息，减少了user_tables视图中的很多逻辑