Oracle的SQL语句中,With查询语句不是以select开头,而是以with关键字开头。
with...as语句增加了SQL 的可读性,构造子查询,结构会更加清晰。可以“一次分析,多次使用”,可以提高性能,达到“少读”的目标。
具体使用with....as.....的SQL语句举例:
with sql_conc_waits as
(select sql_id,
substr(sql_text, 1, 80) sql_text,
concurrency_wait_time / 1000 con_time_ms,
elapsed_time,
round(concurrency_wait_time * 100 / elapsed_time, 2) con_time_pct,
round(concurrency_wait_time * 100 / sum(concurrency_wait_time)
over(),
2) pct_of_con_time,
rank() over(order by concurrency_wait_time desc) ranking
from v$sql
where elapsed_time > 0)
select sql_text, con_time_ms, con_time_pct, pct_of_con_time
from sql_conc_waits
where ranking <= 10
order by ranking;
所有的奇迹,只是因为不抛弃不放弃!