Solved

oralce index

Posted on 2014-12-04
25
217 Views
Last Modified: 2014-12-27
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?
0
Comment
Question by:marrowyung
  • 13
  • 10
  • 2
25 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 300 total points
Comment Utility
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
 
LVL 1

Assisted Solution

by:ora-600
ora-600 earned 200 total points
Comment Utility
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
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
:"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
 
LVL 1

Expert Comment

by:ora-600
Comment Utility
-> 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
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>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
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
"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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>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
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
"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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
what i can see is we have also N/A index, what is that mean? also need rebuilt index?
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
"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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
exactly
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
"this is for partition indexes, for those you need to check the index partitions individually "

how to do it ?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
tks.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now