First blog post

This is your very first post. Click the Edit link to modify or delete it, or start a new post. If you like, use this post to tell readers why you started this blog and what you plan to do with it.

Взято с http://www.sql.ru/forum/173314/explain-tablicy

и слегка переработано:

with
  tmp (target_id,source_id,source_type,level,object) as (
    select 
      s.target_id,
      s.source_id,
      s.source_type,
      0 level,
      cast(NULL as char(128)) object
    from
      explain_stream s
    where
      target_id=1
    union all
    select
      s.target_id,
      s.source_id,
      s.source_type,
      tmp.level+1 level,
      substr('('||rtrim(s.object_schema)||'.'||rtrim(s.object_name)||')',1,128)
        object
    from
      tmp,
      explain_stream s
    where
      s.target_id=tmp.source_id
      and tmp.level < 10000
  ) 
select distinct
  op.operator_id,
  substr(
    space(coalesce(t1.level,t2.level)*2)
      ||rtrim(op.operator_type)
      ||coalesce(t2.object,'')
    ,1,100
  ),
  coalesce(t1.level,t2.level) level,
  op.*
from 
  explain_operator op
    left outer join  tmp t1 on t1.target_id=op.operator_id and t1.source_type='O'
    left outer join  tmp t2 on t2.target_id=op.operator_id and t2.source_type='D'
order by 
  op.explain_requester,
  op.explain_time,
  op.source_name,
  op.source_schema,
  op.source_version,
  op.explain_level,
  op.stmtno,
  op.sectno,
  op.operator_id
@
Реклама
First blog post