لینوکس و شبکه

لینوکس و شبکه
طبقه بندی موضوعی
آخرین مطالب
  • ۹۹/۱۱/۱۳
    java

۲ مطلب با کلمه‌ی کلیدی «oracle» ثبت شده است

۱۰
بهمن

  select
case
when b.table_name is null then
'unindexed'
else
'indexed'
end as status,
a.table_name as table_name,
a.constraint_name as fk_name,
a.fk_columns as fk_columns,
b.index_name as index_name,
b.index_columns as index_columns
from
(
select 
a.table_name,
a.constraint_name,
listagg(a.column_name, ',') within
group (order by a.position) fk_columns
from
dba_cons_columns a,
dba_constraints b
where
a.constraint_name = b.constraint_name
and 
b.constraint_type = 'R'
and 
a.owner = '&&schema_owner'
and 
a.owner = b.owner
group by 
a.table_name, 
a.constraint_name
) a
,(
select 
table_name,
index_name,
listagg(c.column_name, ',') within
group (order by c.column_position) index_columns
from
dba_ind_columns c
where 
c.index_owner = '&&schema_owner'
group by
table_name, 
index_name
) b
where
a.table_name = b.table_name(+)
and 
b.index_columns(+) like a.fk_columns || '%'
order by 
1 desc, 2;

  • behrooz mohamadi nsasab
۱۶
خرداد

select
case
when b.table_name is null then
'unindexed'
else
'indexed'
end as status,
a.table_name as table_name,
a.constraint_name as fk_name,
a.fk_columns as fk_columns,
b.index_name as index_name,
b.index_columns as index_columns
from
(
select
a.table_name,
a.constraint_name,
listagg(a.column_name, ',') within
group (order by a.position) fk_columns
from
dba_cons_columns a,
dba_constraints b
where
a.constraint_name = b.constraint_name
and
b.constraint_type = 'R'
and
a.owner = '&&schema_owner'
and
a.owner = b.owner
group by
a.table_name,
a.constraint_name
) a
,(
select
table_name,
index_name,
listagg(c.column_name, ',') within
group (order by c.column_position) index_columns
from
dba_ind_columns c
where
c.index_owner = '&&schema_owner'
group by
table_name,
index_name
) b
where
a.table_name = b.table_name(+)
and
b.index_columns(+) like a.fk_columns || '%'
order by
1 desc, 2;
  • behrooz mohamadi nsasab