oralce index

Dear all,

Any good article and link coach me :

1) how many diff type of index oracle can use.
2) what is the diff between diff type of index.
3) when SHOULD we rebuild index as normally no index shoud be rebuilt in Oracle.
4) who to update the statistic of oracle for all tables?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
this is a huge question, and the only thing I can refer to for such questions is the official documentation of oracle:
https://docs.oracle.com/cd/B19306_01/server.102/b14231/indexes.htm

hence, answer for Q1 is :
Oracle Database provides several indexing schemes that provide complementary performance functionality. These are:
    B-tree indexes: the default and the most common
    B-tree cluster indexes: defined specifically for cluster
    Hash cluster indexes: defined specifically for a hash cluster
    Global and local indexes: relate to partitioned tables and indexes
    Reverse key indexes: most useful for Oracle Real Application Clusters applications
    Bitmap indexes: compact; work best for columns with a small set of values
    Function-based indexes: contain the precomputed value of a function/expression
    Domain indexes: specific to an application or cartridge.

Open in new window


A2: see the usage and functionality for each of them.

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

A4: use the DBMS_STATS.GATHER_INDEX_STATS on the indexes regularly. plenty of documentation and recommendations exist for this topic, largely discussed, depends on many factors of the db / index
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ora-600Oracle Database AdministratorCommented:
There's not much to add to the things Guy Hengel said.
A3: The internet is full of people who recommend that indexes should be rebuild frequently to avoid fragmentation. But this is not the case. The only reason why I rebuild an index is to move it to a different tablespace or it became invalid.

A4: I recommend analyzing tables with the following statement:
begin
  DBMS_STATS.GATHER_TABLE_STATS (
     ownname          => 'SCOTT'
     tabname          => 'EMPLOYEE'
--     partname         => ''  -- For partitioned tables you may want to analyze partition one by one.
     estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
     method_opt       => 'FOR ALL COLUMNS SIZE AUTO'
--     degree           => ''  -- If you have Enterprise Edition you can use parallel execution
     granularity      => 'AUTO'
     cascade          => TRUE
end;
/

estimate_percent: There is no need to analyze 100%. It does not make any difference to the optimizer if your stats say 100.000 rows instead of the actual 99.323 rows.
method_opt: I recommend analyzing columns which are not indexed as well.
cascade: TRUE -> Analyze indexes as well.
0
marrowyungSenior Technical architecture (Data)Author Commented:
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 ?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
marrowyungSenior Technical architecture (Data)Author Commented:
:"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 ?
0
ora-600Oracle Database AdministratorCommented:
-> 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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
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?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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 ?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
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 ?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
marrowyungSenior Technical architecture (Data)Author Commented:
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 ?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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 ?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
marrowyungSenior Technical architecture (Data)Author Commented:
what i can see is we have also N/A index, what is that mean? also need rebuilt index?
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
marrowyungSenior Technical architecture (Data)Author Commented:
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 ;

?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
exactly
0
marrowyungSenior Technical architecture (Data)Author Commented:
"this is for partition indexes, for those you need to check the index partitions individually "

how to do it ?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
marrowyungSenior Technical architecture (Data)Author Commented:
tks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.