ablog

不器用で落着きのない技術者のメモ

partition by で分類した後に row_number() を使って絞り込む

Oracle SQL

select * from (
	select 
		address,
		hash_value,
		plan_hash_value,
		child_number,
		users_opening,
		fetches,
		executions,
		row_number() 
			over (partition by
					length(sql_fulltext),
					plan_hash_value,
					parsing_user_id 
				order by executions desc, address) rn
	from v$sql
		where parsing_user_id <> 0
		and plan_hash_value <> 0)
where rn = 1;

参考