Re: unbalanced indexes -- common wisdom?

No, it's not a bug.

The index rebuild is a DDL statement, which moves the entire content of the index to a new location, using a whole new set of blocks and generating (virtually) no UNDO. You are left (depending on the exact mechanism you choose) with an index which looks like the result of a   create index
statement, with no history.

The table is left in place, and the table blocks contain pointers to UNDO blocks which contain earlier versions of the data that was in those blocks. When you query using flashback (as of timestamp), the table has a history which can be mined, the index is a new object with no history.

If you did:

    alter table emp1 move;
    alter index emp1_pk rebuild
then your flashback would fail on both paths.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html



Mikito Harakiri wrote in message ...


>Thank you, Richard.

>

>One of the reasons may be oracle unique "read history" transaction

>isolation semantics. It is easy to understand how it works for

>ordinary blocks with table data, but what really happens with index

>blocks? My guess is that oracle doesn't want to reorganize blocks

>unnecessarily, because it will complicate restoring the state back.

>

>Here is an interesting experiment. My table with a PK index contained

>1 record as of yesterday. I inserted 9999 more records, then deleted

>the old record and half of the new ones. I'm still able to read my

>yesterday's data as:

>

>SELECT --+full(emp1)

>   empno FROM emp1

>   AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY)

>   WHERE empno = 7934   -- yesterday's record; no longer in the table

>

>both through FTS and index. Now, if I rebuild the index then the index

>path gives me an ORA-08176 error, while FTS still works. I think this

>is a bug.

>