unindex 11.2 cross schema

Foreign keys might point to another schema. Here is an unindex query for such situation. My earlier queries unindex and unindex 11.2 were built on top of user_* views. Here the queries are using all_* views. I am not using dba_* views as all_* views are usable for wider audience than just dba privileged. Reminder: you probably do not need all of those, but they might explain you some poor execution times or TM lock waits.

select case when i.index_name is not null
            then 'OK'
            else '****'
       end ok
     , c.owner
     , c.table_name
     , c.constraint_name
     , c.cols
     , i.index_name
from (
  select a.owner
       , a.table_name
       , a.constraint_name
       , listagg(b.column_name, ' ' ) 
          within group (order by column_name) cols
      from all_constraints a, all_cons_columns b
     where a.owner = b.owner
       and a.constraint_name = b.constraint_name
       and a.constraint_type = 'R'
  group by a.owner,a.table_name, a.constraint_name
 ) c
 left outer join
 (
  select index_owner
       , table_name
       , index_name
       , cr
       , listagg(column_name, ' ' ) 
          within group (order by column_name) cols
    from (
        select index_owner
             , table_name
             , index_name
             , column_position
             , column_name
             , connect_by_root(column_name) cr
          from all_ind_columns
       connect by prior column_position-1 = column_position
              and prior index_name = index_name
              and prior index_owner = index_owner
         )
    group by index_owner, table_name, index_name, cr
) i on c.owner = i.index_owner and c.cols = i.cols and c.table_name = i.table_name
order by ok,owner,table_name,constraint_name;

The index could be unique

drop table uq;

create table uq(pk int constraint uq_pk primary key, col int, col2 int);

create index uq_col_idx on uq(col,pk);

Index uq_col_idx is not unique but it might be defined as such. It contains all columns in a unique primary key. To find such candidates is just another relational division question.

with co as (
select con.constraint_name
     , con.table_name
     , cco.column_name
     , count(*) over (partition by con.constraint_name) ccc 
  from user_constraints con 
 inner join user_cons_columns cco 
    on cco.constraint_name=con.constraint_name 
 where con.constraint_type in ('P','U') 
   and con.deferrable = 'NOT DEFERRABLE' 
), ix as (
select idx.table_name,idx.index_name,ico.column_name,idx.uniqueness
  from user_indexes idx 
 inner join user_ind_columns ico 
    on idx.index_name = ico.index_name
 where exists (
   select 0 from user_tab_columns col
    inner join user_ind_columns coi 
    on coi.table_name = col.table_name
   and coi.column_name = col.column_name
    where idx.index_name = coi.index_name
      and col.nullable = 'N'
 )
), ixco as (
select ix.table_name
     , ix.index_name
     , ix.column_name
     , co.constraint_name
     , co.ccc
     , count(*) over (partition by co.constraint_name,ix.index_name) ixc
     , ix.uniqueness
  from co, ix
 where co.table_name=ix.table_name 
  and co.column_name=ix.column_name
)
select distinct table_name,index_name could_be_unique,constraint_name based_on
  from ixco
 where uniqueness = 'NONUNIQUE'
   and ccc=ixc
 order by table_name,index_name
;

TABLE_NAME                     COULD_BE_UNIQUE                BASED_ON
------------------------------ ------------------------------ ------------------------------
UQ                             UQ_COL_IDX                     UQ_PK

drop index uq_col_idx;

create unique index uq_col_idx on uq(col,pk);

A table vanishing from a plan

Christian Antognini published his presentation slides Challenges and Chances of the 11g Query Optimizer. Worth reading to get a nice overview. The 11g new feature "join elimination" is mentioned there. There is a similar behavior even in 10gR2 optimizer. A table mentioned in a query text vanishes from the query plan. It is not a join but a correlated sub query with a aggregate.

select 1 
  from dual mai 
 where exists (
   select max(1) 
     from dual inn 
    where mai.dummy=inn.dummy
)
;

---------------------------------
| Id  | Operation        | Name |
---------------------------------
|   0 | SELECT STATEMENT |      |
|   1 |  FAST DUAL       |      |
---------------------------------

The sub query and the "inn" table is not seen in the plan. There is no need as the aggregate returns a row. This is not the case in my earlier post about not exists null. There the sub query is not correlated and it is using not exists. The not exists correlated makes similar elimination. But in addition adds a filter(NULL IS NOT NULL) just before entering even the first table.

select 1 
  from dual mai 
 where not exists (
   select max(1) 
     from dual inn 
    where mai.dummy=inn.dummy
)
;

---------------------------------
| Id  | Operation        | Name |
---------------------------------
|   0 | SELECT STATEMENT |      |
|*  1 |  FILTER          |      |
|   2 |   FAST DUAL      |      |
---------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(NULL IS NOT NULL)

One could ask, why would somebody write such a query...