Examination

Lets have a look at various queries, and how they relate to each other from a performance point of view. First try: Execute

SELECT *
FROM ordertest;

through JDBC three times. The time this took was 64ms, 67ms and 63ms. Second try: execute

SELECT * 
FROM ordertest
ORDER BY ordercolumn;

through JDBC three times. The time this took was 380ms, 356ms and 366ms. As expected, the statement takes much longer by adding the ORDER BY. The execution plan for the query shows a SORT operation. Now, let’s delete the table and recreate it with an index:

DROP TABLE ordertest; 
CREATE TABLE ordertest ( 
    ordercolumn VARCHAR2(20),
    idcolumn NUMBER(10)
); 
CREATE INDEX orderindex ON ordertest(ordercolumn);

The creation of 1.000.000 records with the same pattern as above took approx. 6:54 minutes, which is 40 seconds more than before (most likely a result of adding the data to the index). Then, the same queries have been executed as above: First try: execute

SELECT *
FROM ordertest;

through JDBC three times. The time this took was 81ms, 66ms and 65ms. As expected, it took a similar time as without the index. Second try: execute

SELECT *
FROM   ordertest
ORDER BY ordercolumn;

through JDBC three times. The time this took was 447ms, 370ms and 344 ms which is similar to the ORDER BY query with no index. The execution plan shows a SORT operation – even though an index on the column we want to sort exists! The reason is that the column might contain NULL values so that the database cannot use the index for the ordering. This can be verified by the following query: Third try: execute

SELECT *
FROM   ordertest
WHERE ordercolumn IS NOT NULL
ORDER BY ordercolumn;

through JDBC three times. The time this took was 93ms, 63ms and 71ms. This is much better than the results from the previous try! The execution plan shows that no SORT operation has been used and that the index was properly used for the ORDER BY. Alternatively the ordercolumn can be set to NOT NULL which automatically assures that it must not contain NULL values:

ALTER TABLE ordertest MODIFY ordercolumn NOT NULL;

Then, the query can be executed without the IS NOT NULL condition:

SELECT * 
FROM ordertest 
ORDER BY ordercolumn;

This statement now also uses the index properly and the execution time is appropriately short. Lastly, let’s look at a statement where we use more selective queries which might be used in real life systems: Execute

SELECT * 
FROM ordertest 
WHERE ordercolumn LIKE '%AZ%' 
ORDER BY ordercolumn;

through JDBC three times. The time this took was 75ms, 65ms and 71ms which is again similar to the execution time without the additional LIKE condition.