Home » RDBMS Server » Performance Tuning » What is the use of index
icon7.gif  What is the use of index [message #130863] Tue, 02 August 2005 23:28 Go to next message
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 #130865 is a reply to message #130863] Tue, 02 August 2005 23:49 Go to previous messageGo to next message
milind_sri
Messages: 70
Registered: February 2005
Location: Pune
Member
Hi,

Have you read the documentation or searched on Google. You will find a lot of matter there to resolve your query regarding Indexes.

Milind.
Re: What is the use of index [message #130866 is a reply to message #130865] Tue, 02 August 2005 23:56 Go to previous messageGo to next message
vidya2005
Messages: 36
Registered: July 2005
Location: bangalore
Member
I will search.

thank you milind

vidya
Re: What is the use of index [message #130867 is a reply to message #130866] Wed, 03 August 2005 00:02 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member

Vidya,

Try this link to get online docs regarding ur queries.

http://otn.oracle.com/pls/db92/db92.federated_search

regards,
tarun
Re: What is the use of index [message #130950 is a reply to message #130863] Wed, 03 August 2005 08:40 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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..
Re: What is the use of index [message #131419 is a reply to message #131276] Fri, 05 August 2005 23:04 Go to previous message
vidya2005
Messages: 36
Registered: July 2005
Location: bangalore
Member
Thank You very much

vidya
Previous Topic: UNION QUERY OPTIMIZE
Next Topic: 8.1.6 control file too large, how to shrink?
Goto Forum:
  


Current Time: Fri Mar 29 00:21:22 CDT 2024