 What is the use of index [message #130863] |
Tue, 02 August 2005 23:28  |
vidya2005
Messages: 36
Registered: July 2005
Location: bangalore
|
Member |
|
|
hello
I want to know what is index and what is the use
of it.I know it will increase the performance.
But how it will improve the performance.
What is the use of packages.
please help me to know about matters.
regards
vidya
|
|
|
|
|
|
|
|
|
|
| Re: What is the use of index [message #130950 is a reply to message #130863] |
Wed, 03 August 2005 08:40  |
talvasson
Messages: 3
Registered: January 2005
Location: Guadeloupe - Romania
|
Junior Member |
|
|
Hi,
Index is used on a Table which is selected by the same column to accelrate accès without reading every records.
Example :
your table is
create table Table1 (
name varchar2(32),
address varchar2(32),
tel varchar2(15)
);
Your have 100 records in your table, non index and you want to select a record whith the name Vodkakovsky
select * from table1 where name ='Vodkakovsky';
explain plan => select full on table table1
=> not optimized for billion records
You create an index (btree index, not a bitmap index):
create index table1_name_index on table1(name);
and execute your select one more time,
it will be more quickly to have an answer because Oracle will look in the index table1_name_index owned by the table table1 before reading the table.
select * from table1 where name ='Vodkakovsky';
explain plan => index scan => acces by rowid on the table
=> optimized for billion records
Using index if index on columns will increase access on records for a table where the predicat comports this columns (especially with columns name = ?)
But more index you have on a table, more time and space it take for insert, update rows on a table...
For more information on indexes, look in google, orafaq , otn etc whith these keywords :
"Bitmap and Btree index"
"Index Organized Table"
"monitoring index"
"tuning sql"
@+
|
|
|
|
| Re: What is the use of index [message #131276 is a reply to message #130863] |
Fri, 05 August 2005 01:42 |
sharan_it
Messages: 140
Registered: July 2005
Location: Chennai
|
Senior Member |
|
|
Hi Vidya,
Normally tables undergone full table scan.If u use index basically increase speed
(U can see type of scan in the execution plan)
Oracle picks index randomly and increase the speed basically.
That is index-by scan increase speed.
U can have more than one index for a table.That is multiple indexes.But again,Creating too many indexes upon a single table is not advisable.(I think in this case select statements work faster but update or insert takes more time)
(Creating Primary key or unique key implicitly creates indexes and even we can create indexes explicitly using
create [unique/bitmap] index index_name on table_name(col1,col2....))
But,this is applicable for retrieving selective records.
If ur rows to be retrieved is more,i say more than 60%,as index is only by random basis there is chance for picking same rows more than once and so it will take more time than full-table scan.
Upon function-based index and bitmap index it will increase performance drastically.Bitmap index should be created for low cardinality colums..I say for Sex the value should be 'M' of 'F'..It will definetly have multiple duplicates..
Again there is some feature called index-organised table(Normal table have indexes only logically) But this Index organised table(IOT) have indexes both logically and physically..IOT will have only half of I/O compared to normal index tables.
Sharan..
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
