Database Reference
In-Depth Information
Next we would like to talk about the Indexes. We have seen
previously that an index gets created whenever you create a
PRIMARY KEY constraint on any column of a table, implicitly.
We call it an implicit index because you haven't executed any
index creation statement but it gets created as a result of
something else we wanted to do or in other words you can say
the term it's a “by product” of PRIMARY KEY constraint.
There are certain pros and corns associated with index. The sole
purpose or you can say the benefit of index is, it speeds up the
search process. Just like the index available to you at the end of
topic. Imagine a topic having no index and comprised of 1000
pages and 30 lines per page. I hope you got the essence, the
beauty, and the power of having an index. Now the next question
comes in mind. What's the criterion of selecting a column for
index purpose? or in other words which columns should we
create index on? And what's wrong with having indexes created
on all the columns of all the tables? Do think for a while at least
before reading below the reasons.
The criterion of selecting a column for index purpose is, the
column that we will be using a lot in the WHERE clause. The
emphasis is on “a lot”. How to figure out which columns will be
used a lot? Some columns are apparent e.g. in SSA (Social
Security Administration) office or NID (National Identity) Card
office, it's obvious that most of the time they would be pulling
up the information using ssn or most of times requests coming to
SSA to look up for something would be through ssn. It doesn't
mean that you cannot make a search based on other columns.
Each time you create a index on any column of a table Oracle
creates a implicit table that can help it in locating the records
much faster. That table size depends on the size of column on
which you have created the index and both of these quantities are
in direct relationship with each other i.e. more the number of
values in column, on which you have created the index, more
would be the size of implicit table that Oracle will create. So
creating indexes on all the columns of all the table inside the
database will tremendously increase the size of the database and
Search WWH ::
Custom Search