Indexing and Performance Tuning
--------------------------------

The two most important elements of Performance Tuning are
indexing and buffer size manipulation.
We will work on indexing.

Let us remember what an index does.
Imagine a phone book and you want to find people
by FIRST NAME. You would have to read the whole book
line-by-line. Or, you create a list of first names,
and then for every first name a pointer [e.g., see page 209, line 38]
to where a person of that first name exists in the
phone book. This list of first names and pointers is called an
INDEX. If you have an unsorted, unindexed column,
Oracle reads the data line-by-line. This is not a good
idea for fast access.

How do I even know that I improved things?

One possible approach is to measure times of executions.
The table people contains 320,000 rows.

SELECT TO_CHAR(SYSTIMESTAMP, 'SSSSS.FF') FROM DUAL
/
SELECT * from people
/
SELECT TO_CHAR(SYSTIMESTAMP, 'SSSSS.FF') FROM DUAL
/


The other possibility is that we use
the time returned by SQL Developer.

WARNING: Oracle does some caching, so the second run
of the same select statement is likely to be faster.

Also note that sometimes an index actually makes
things slower, because you have first a disk access to
get the index and then a second disk access to get the data.
Disk access is the slowest operation of all, the bottle neck.

--------------------------

The importance of good query design:

Consider the table containing the gender and age of
320,000 people. If you wanted to obtain
all the males aged 34 and the table had an index
on gender and an index on age there
would be two possibilities.

* Use the gender index to first obtain all males
and then go through the result set finding the 34 year olds

We know there are 320,000 people in the table. If we
used the index to find all males it is fair to say we
would return about 160,000 rows. We would then have
160,000 rows which we have to search in order (one by one)
for 34 year olds.

* Use the age index to first obtain all 34 year olds
and then search the result set finding the males

Assume people are between 0 and 100.
These will not be equally distributed.
There are many more people aged 34 than people aged 99.
Now VERY approximately we would return about 2% of the rows
(about 6400) which we have to search in order (one by one)
for males.

So, the second approach is obviously much better.
Use the index that will give you the smallest result set.


----------------------------------------------------------------------
Surprise: Oracle does not HAVE TO use an index,
even if you create one.
We have to force the system to use index structures.

You can give "hints" to the optimizer.
Hints look like comments INSIDE THE select statement,
starting with a + sign. The FULL hint requires a full
table scan, as opposed to using an index.


==========================================
Bad design, no index.


SELECT *
FROM (
SELECT *
FROM PEOPLE
WHERE GENDER = 'M'
)
WHERE AGE = 34
/




==========================================
Good design, no index.

SELECT *
FROM (
SELECT *
FROM PEOPLE
WHERE AGE = 34
)
WHERE GENDER = 'M'
/



==========================================
create index forgender on PEOPLE(GENDER)
/

Bad design with index.

SELECT *
FROM (
SELECT /*+ INDEX( PEOPLE, FORGENDER ) */ *
FROM PEOPLE
WHERE GENDER = 'M'
)
WHERE AGE = 34
/



drop index forgender
/

==========================================
create index forage on PEOPLE(AGE)
/

Good design with index.

SELECT *
FROM (
SELECT /*+ INDEX( PEOPLE, FORAGE ) */ *
FROM PEOPLE
WHERE AGE = 34
)
WHERE GENDER = 'M'
/






drop index forage
/

Conclusion: