So I decided to simulate and to see what happened ( I am learning oracle about 7 months, can be mistakes ), I know that in normal indexes DML operations maintained ( indexes updated when DML operations caused ), but I want to check invisible indexes in DML operations which maintained or not. Now I create table =>
create table emin1 ( id number primary key, nomre number );
insert into emin1 values(1,1);
insert into emin1 values(2,1);
First to get index name(I didnot create index), then I used analyze in normal index =>
SQL> select index_name,table_name from user_indexes a where table_name = 'EMIN3';
INDEX_NAME TABLE_NAME
--------------- ---------------
SYS_C008422 EMIN3
analyze index SYS_C008422 validate structure;
SQL> select name, lf_rows,distinct_keys from index_stats;
NAME LF_ROWS DISTINCT_KEYS
--------------- ---------- -------------
SYS_C008422 2 2
I dont know most columns in index_stats and I select DISTINCT_KEYS column(I know only this :)) ), after statictics I insert 2 row again and analyze again =>
insert into emin1 values(3,1);
insert into emin1 values(4,1);
analyze index SYS_C008422 validate structure;
SQL> select name, lf_rows,distinct_keys from index_stats;
NAME LF_ROWS DISTINCT_KEYS
--------------- ---------- -------------
SYS_C008422 4 4
So after insert operation we saw that changed in the index_stats( this mean maintained ) and after normal index I enforced this to invisible index =>
SQL> alter index SYS_C008422 invisible;
Index altered.
Inserts some rows=>
insert into emin1 values(5,1);
insert into emin1 values(6,1);
insert into emin1 values(7,1);
SQL> analyze index SYS_C008422 validate structure;
Index analyzed.
SQL> select name, lf_rows,distinct_keys from index_stats;
NAME LF_ROWS DISTINCT_KEYS
--------------- ---------- -------------
SYS_C008422 7 7
So it is changed again, I didnt know I am right in this or not but I want to know experts opinion because searched more in google about this but I cannot find clear answer and I simulated this to see what happened in DML operations, I found this approach and I think this will help more beginners.
Its easy to confuse two important attributes of an index:
1) Visibility. As you've seen, this does not impact the underlying maintenance of an index due to DML, it simply controls whether that index is eligible for use for the optimizer when it is working out the best way to run a query. The "visibility" pertains to the optimizer looking at it. For example, if you make an index invisible, but that index is defined as UNIQUE, then rest assured if you try to insert duplicates, that invisible index will still kick up an error.
2) Usability. An index can also be set to "unusable". This is where the database will no longer update the entries in the index as DML occurs. As such, the index cannot be used simply because it no longer represents the true state of the index. We make indexes unusable typically to perform large operations more efficiently on a table. At the conclusion of the large operation, we need to issue a REBUILD on that index in order to bring it back into alignment with the (changed) table data, and hence it becomes usable again.
Hope that helps.