Oracle SQL in 10 Minutes - Asim Abbasi
CH1: SQL Basics | CH2: SQL Operators | CH3: SQL Built-in Functions | CH4: SQL Joins | CH5: UPDATE, INSERT & DELETE Statements | CH6: CREATE, ALTER & DROP Statements | CH7: Constraints | CH8: Linking Tables vs Joining Tables | CH9: SQL Statements for Other Database Objects | CH10: SQL Statements for Database Security
Chapter 9: SQL Statements for Other Database Objects
Next we would to talk about are the Indexes. We have seen previously
that an index gets created whenever you create a PRIMARY KEY constraint
on any column of a table, implicit. 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 book. Imagine a book
having no index and comprised of 1000 pages and 30 likes 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 we
would be using a lot in there 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, its
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.
Chapter 9: SQL Statements for Other Database Objects
CH1: SQL Basics | CH2:
SQL Operators |
CH3: SQL Built-in Functions | CH4:
SQL Joins | CH5:
UPDATE, INSERT & DELETE Statements |
CH6: CREATE, ALTER & DROP Statements | CH7:
Constraints | CH8:
Linking Tables vs Joining Tables | CH9:
SQL
Statements for Other Database Objects | CH10:
SQL Statements for
Database Security Share with others: