oralce index

excellent guys!

Guy Hengel,

"the usual situation is after a full table truncate + reload...
"

and why it need to rebulid index then? no data there anymore.  why data reload needed? what situtation ?

all,

"use the DBMS_STATS.GATHER_INDEX_STATS on the indexes regularly. "

how to do it for all tables in a single command ?

ora-600,

"The only reason why I rebuild an index is to move it to a different tablespace or it became invalid.
"

rebuild index can relocate it at the same time? so here you mean separate the index and data ?

"or it became invalid."

how can you know it becomes invaild ?

because of the index fragmentation...

>how to do it for all tables in a single command ?
you cannot, on just the indexes, but on the full schema:
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SIMON', estimate_percent => NULL);

>rebuild index can relocate it at the same time? so here you mean separate the index and data ?
yes, which is a good DBA operation:
Alter index <index-name> rebuild   tablespace <new-tablespace>;
which also can be applied on a single partition (if the index is partitioned)


>how can you know it becomes invaild ?
monitoring...
 dba_indexes and dba_ind_partitions  system views have a status column can be one of three values, VALID, INVALID, or N/A. Anything INVALID needs to be rebuild

:"because of the index fragmentation..."

so just cause trucate the table, will delete a lot data and as we do'nt use that data any more , index still here and it create defragemation ?

then why reload of data create index fragmentation ?

"you cannot, on just the indexes, but on the full schema:
 EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SIMON', estimate_percent => NULL);"

may I know if this just update statistic of the index of table? what is the command of rebuilding index?

"yes, which is a good DBA operation:
 Alter index <index-name> rebuild   tablespace <new-tablespace>;
 which also can be applied on a single partition (if the index is partitioned)"

yes, for performance point of view, so the new added index will be created on the new tablespace or keep staying in the original tablespace of the table ?

"Anything INVALID needs to be rebuild "

invaild meansa the index hasn't been use for a long time? then rebuild it will remove it automaticaly ?

-> B-Tree Index is the default index type in oracle. "B" stands for "balanced" so you will never experience that a b-tree index gets fragmented. I'm not sure about other index types.

-> When you truncate a table all indexes get "unusable" or "invalid". There are some more situations when an index becomes invalid. They are all connected to operations which change the rowids of a table abnormally.
Normal change of a rowid is when you insert, update, delete.
Abnormal change is when you move a table to a different tablespace, truncate a table or partition of a table, load data into a table with imp, impdp, sqlLoader
These tools use high speed mechanism to load data into oracle. They avoid updating all indexes to improve performance and just make them invalid. Imp and impdp rebuild indexes after they are done importing when you use sqlLoader you have to do it manually.

-> An index always gets created in the tablespace you defined. The information is stored in the index definition. If you do not tell oracle where to locate the index it will be located in the default tablespace of the user.

-> "invaild meansa the index hasn't been use for a long time?"
No, an index does nut bet invalid if you don't use it. If you do not do any DBA-Tasks an index does not become invalid.

-> "then rebuild it will remove it automaticaly ?"
Yes, when you rebuild an index the previous index data will be removed. There is no need to drop it manually.

ora-600,

"-> B-Tree Index is the default index type in oracle. "B" stands for "balanced" so you will never experience that a b-tree index gets fragmented. I'm not sure about other index types.
"

so it seems B-Tree index is the best ? that B-Tree, from our point of view, sort in order only when data insert to the table.

"They avoid updating all indexes to improve performance and just make them invalid. Imp and impdp rebuild indexes after they are done importing when you use sqlLoader you have to do it manually.
"

so AFTER  this kind of operatoin that index is said to be vaild ? so invaild just temporary during this process ?

"-> "then rebuild it will remove it automaticaly ?"
 Yes, when you rebuild an index the previous index data will be removed. There is no need to drop it manually. "

this one very diff from MS SQL

Guy Hengel,

"you cannot, on just the indexes, but on the full schema:
 EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SIMON', estimate_percent => NULL);"

so here , full schema is the ownname ? so if I specify the schema here than it rebuild all index of that schema?

>so it seems B-Tree index is the best ?
it's the default index type, unless you specify something else, and indeed in most cases (but not all) it's the most efficient one. discussing all the index types is worth a full oracle training of several days


>so here , full schema is the ownname ? so if I specify the schema here than it rebuild all index of that schema?
not rebuild the indexes, just compute the stats on all the tables and indexes of that schema

"discussing all the index types is worth a full oracle training of several days"

I knew.

">so here , full schema is the ownname ? so if I specify the schema here than it rebuild all index of that schema?
not rebuild the indexes, just compute the stats on all the tables and indexes of that schema"

yeah!

so we use

Alter index <index-name> rebuild   tablespace <new-tablespace>;

to rebuilt one index? how about all index on that tablespace?

after that we run
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SIMON', estimate_percent => NULL);

to update statistics.

"A3: indeed, indexes are rarely rebuild. the usual situation is after a full table truncate + reload..."

why we need rebuilt index after data reload ?

>to rebuilt one index? how about all index on that tablespace?
you can script that out, if needed, from the ALL_INDEXES or DBA_INDEXES view:
select 'Alter index ' || index_name || ' rebuild   tablespace <new-tablespace>;' from all_indexes;
copy/paste the outcome to a sql/plus for example...

>why we need rebuilt index after data reload ?
for b-tree indexes, this is not needed. for other index types, this is eventually needed or even recommended.
for example, bit-map indexes shall be fully rebuild after such an operation.

tks.'

"you can script that out, if needed, from the ALL_INDEXES or DBA_INDEXES view:
 select 'Alter index ' || index_name || ' rebuild   tablespace <new-tablespace>;' from all_indexes;
 copy/paste the outcome to a sql/plus for example...
"

tks.

"for example, bit-map indexes shall be fully rebuild after such an operation. "

I got it, but my question is why . why even reload data needs that? I just can't get it.

it seems I can construct a update statistics statement using this kind of statement:

 select 'Alter index ' || index_name || ' rebuild   tablespace <new-tablespace>;'

and for that, only:
1) ownname    
 2)  tabname    

is important ?

so how can I find out all user tables to update stasticis ?

the all_indexes and dba_indexes views also have owner columns, so you can filter on that, and indeed add it to the script:
 select 'Alter index ' ||owner ||'.' || index_name || ' rebuild   tablespace <new-tablespace>;' from all_indexes where owner = 'xxx';

>I got it, but my question is why . why even reload data needs that? I just can't get it.
by the internal way the index structure is maintained.

let me use an image: say in your garage you add boxes with foods, each box has some color on it so you can quickly identify what shall be in the box.

normally, to get some specific food you will get there; only look at the box colors, pick up the right box (by the color), and get the food you want. when you buy new food, you will just add it to the existing boxes, unless a box is full, you then just add a new box (same color)

when removing the food, some boxes may become empty. you have then the choice to leave the box where it is (coice 1; because you assume you will refill it with new food the next day), or completely remove the box (choice 2).

roughly:
Choice 1 is what happens for bit-map index (for example)
Choice 2 is what happens with B-Tree indexes

the choice not to remove the boxes (index internal structure) is related to overall performance.
you may consider that the empty boxes should be removed to free up space, but if you know you will need the space anyhow later, and recreating the box takes hence unnecessary time, you hence choose to keep the boxes in place

so you mean :

"Choice 1 is what happens for bit-map index (for example)"

is the reason why reload data need rebuilt index? so bit-map don't need to rebuilt index as the box still there and b-tree will remoe the box so it create index fragementation ?

remember: my "picture" was just to give you some idea about what, more or less, would be the synonym of what really happens down in the technical part of the oracle db. you should not need to know all the details, just remember that for some index types, you shall perform a index rebuild after a large data reload, and for the others, you don't need to do it.

"you should not need to know all the details, just remember that for some index types, you shall perform a index rebuild after a large data reload, and for the others, you don't need to do it. "

yes I knew, but good start point is necessary ! correct direction is necessary, agree ?


">how can you know it becomes invaild ?
 monitoring...
  dba_indexes and dba_ind_partitions  system views have a status column can be one of three values, VALID, INVALID, or N/A. Anything INVALID needs to be rebuild "

but when I select * from dba_indexes; , it seems the invaild index is referred as UNUSABLE or N/A, which one it really means invaild ?

UNUSABLE => also need to rebuild the index
INVALID and UNUSABLE have different "root causes" in regards to why the index needs a rebuild, but both require the index to be rebuild

what i can see is we have also N/A index, what is that mean? also need rebuilt index?

"Alter index <index-name> rebuild   tablespace <new-tablespace>;"

by this, it will rebuild index for all table using that tables space, right? we can't rebuild index for just one table, right?

that will rebuild only the 1 index specified by the index-name, "moving" to that tablespace (if you specify the tablespace the index is already on, it's not a "move", but still a rebuild.

>N/A index
this is for partition indexes, for those you need to check the index partitions individually

so, this is move the index to a new table space:

Alter index indexA rebuild   tablespace_old tablespace_new;

and this is an index rebuilt:

Alter index  indexA  rebuild   tablespace_old ;

?

exactly

"this is for partition indexes, for those you need to check the index partitions individually "

how to do it ?

dba_ind_partitions:
https://docs.oracle.com/cd/B12037_01/server.101/b10755/statviews_2177.htm

tks.