Indexes as quick sorting (I)

Part I – Why indexes?

Oracle Database now grows and grows. It becomes BigData. As modern age wants to do everything fast then there is the best way to select necessary data in time.
Let's see an example.

1. Create a simple table with data:

CREATE TABLE FOR_SORT ( COL1 VARCHAR2(10 BYTE) NULL, COL2 NUMBER(5,0) NULL )
/
-- Insert script for table FOR_SORT
INSERT INTO FOR_SORT(COL1, COL2) VALUES('ddd', 1)
/
INSERT INTO FOR_SORT(COL1, COL2) VALUES('ggg', 2)
/
INSERT INTO FOR_SORT(COL1, COL2) VALUES('ccc', 3)
/
INSERT INTO FOR_SORT(COL1, COL2) VALUES('fff', 4)
/
INSERT INTO FOR_SORT(COL1, COL2) VALUES('hhh', 5)
/
INSERT INTO FOR_SORT(COL1, COL2) VALUES('bbb', 6)
/
INSERT INTO FOR_SORT(COL1, COL2) VALUES('iii', 7)
/
INSERT INTO FOR_SORT(COL1, COL2) VALUES('aaa', 8)
/
INSERT INTO FOR_SORT(COL1, COL2) VALUES('eee', 9)
/

2. Explain plan for simple select statement (SELECT A.* FROM FOR_SORT A;) is the following:

The red colored line in Explain Plan results means that the statement is not optimized.

3. Create an index on one column:

CREATE INDEX IND_COL1 ON FOR_SORT ( COL1 )

4. Explain plan for a select statement with indexed column in WHERE clause (SELECT A.* FROM FOR_SORT A WHERE A.COL1 > 'c';) is more cheaper. Pay attention to "Cost" and "CPU Cost" values:

Note: data is ordered automatically by indexed column.

5. Let's compare explain plan for the same statement but with non-indexed column in WHERE clause(SELECT A.* FROM FOR_SORT A WHERE A.COL2 > 2;):

Result of not-ordered select statement is the same but Explain Plan differs - see "Cost" and "CPU Cost" values: 1/2 (indexed column in WHERE clause) -> 3 (not-indexed) and 14613/7321 (indexed) -> 37587 (not-indexed).

6. Compare explain plan for statements with ordered data, with and without mentioning an indexed column:
SELECT A.* FROM FOR_SORT A ORDER BY A.COL1 DESC;

SELECT A.* FROM FOR_SORT A WHERE A.COL1 IS NOT NULL ORDER BY A.COL1 DESC;

"Cost" and "CPU Cost" values show the best selection: ordering takes much resources but the clause with indexed column decreases it (4 -> 2, 3 -> 1, 36022939 -> 17753, 37137 -> 8921). Results of both select statements are equal, a short clause (any mention of indexed column COL1 in WHERE clause but not in ORDER BY clause) was added for execution speed up.

You see, index helps to execute the DML statements faster. About more index using, please, read the next topic.