Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Posts
    14

    Unanswered: Query takes very long time

    Hi

    I use the following query to fetch the index names and thier status from user_objects table. I'm using the subquery to avoid those index names which are created when the primary / unique key is created. But this query takes a long time to execute. Is there any way to optimize it ?

    SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_TYPE = 'INDEX' AND GENERATED='N' AND OBJECT_NAME NOT IN (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS)

    thanks in advance,
    saravanan

  2. #2
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    I find an /*+ ALL_ROWS */ hint often helps in data dictionary queries.

  3. #3
    Join Date
    Feb 2005
    Posts
    14
    Thanks. I tried this. With hint and without hint does not make much difference in my environment. Anyother ideas ?

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Cut down on the number of rows you are looking at ...

    in the subquery add "where constraint_type = 'P'

    I would also query against user_indexes instead of user_objects
    You can also query this type of situation in other ways ... ie,

    select count(*) From user_indexes a where not exists (select 'x' from
    user_constraints where constraint_type = 'P' and constraint_name = a.index_name);

    Another way ...

    SELECT FCODE
    FROM (
    SELECT f.INDEX_NAME AS FCODE,
    o.CONSTRAINT_NAME AS OCODE
    FROM USER_INDEXES f,
    USER_CONSTRAINTS o
    WHERE o.constraint_name(+) = f.index_name
    )
    WHERE OCODE IS NULL
    /

    HTH
    Gregg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •