Re: How to identiy tables without indexes

Something more like this would work better :

select distinct table_name, owner
  from dba_tables t
 where not exists (

       select 1
         from dba_indexes i
        where t.table_name = i.table_name
          and t.ower = i.table_owner)

I do not have access to the database while writing this so the fieldnames may be wrong.

Marc

Soeren Dalby wrote:

> Hi Guys,
>
> I am in a situation where I need to identify all the tables in an Oracle
> database which doesnt have an index (at least one is expected for all) and
> this actually turns into a SQL question because I have a theory: select all
> the tables in DBA_TABLES which is not represented in DBA_INDEXES. It could
> be something like
>
> select distinct table_name, owner from dba_tables where not in (select
> table_name, table_owner from dba_indexes )
>
> Is this way correct ?? I do not have access to the database while writing
> this so the fieldnames may be wrong
>
> Any input is mostly welcomed
>
> Thanks in advance.
>
> --
>
> Sincerely yours
>
> Dalby Data
> Sneppevej 15, st. th.
> 2400 Copenhagen NV
>
> Telephone +45 40 96 00 89
> Telefax +45 31 86 09 20
>
> Mail: dalby_data_at_post3.tele.dk
> Site: http://home3.inet.tele.dk/dalbydat
Received on Wed Jun 02 1999 - 11:56:09 CDT