Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Posts
    62

    Angry Unanswered: Query return bad result because of index

    When I launch the query :
    select * from product where uni_sid=32753 and com_com_sid3 = 13063;
    The result is "no rows selected"

    When I launch the query
    select * from product where pro_sid = 657219 and uni_sid=32753 and com_com_sid3 = 13063;
    The query return 1 row.

    I look at the explain plan an dsaw the first query use an non-unique index IDX_Product2. I drop this index and recreate it. The first query now return a correct result.

    Is there a way to verify indexes integrity and rebuild only tose who are corrupt ?

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    1.
    SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER = 'xxx'
    and object_type = 'INDEX' AND STATUS = 'INVALID;

    2.
    SELECT a.name, a.obj#, a.dataobj#, b.dataobj#
    FROM obj$ a, ind$ b
    WHERE a.obj# = b.obj#
    AND a.dataobj# != b.dataobj# ;

    3. ANALYZE TABLE xxx VALIDATE STRUCTURE CASCADE

    note: #3 WILL CAUSE TABLE LOCKS TO OCCUR ... DON'T DO IT WITH USERS
    ACCESSING THE TABLE

    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
  •