Topic: Database Administration >> SQL QUERY
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| |
Posted: Feb 21, 2008 03:51:17 AM |
Total Post: 26
Joined: Feb, 2008
|
| What is Index and what are the benefit of index for us? |
|
|
 Ratnaker |
| Posted: Feb 21, 2008 06:58:50 AM |  |
|
Total Post: 140
Joined: Apr, 2007
|
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...
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 843 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Gitesh Trivedi | 322 |
| Gopu Gopi | 268 |
| neeraj sharma | 252 |
| Ramesh Jois | 245 |
| Bishal Khetan | 207 |
|
|
