What to do when the SQL index does not solve the problem of performance?

Question

This query ONE

SELECT * FROM TEST_RANDOM WHERE EMPNO >= '236400' AND EMPNO <= '456000';

in the Oracle Database is running with cost 1927.

And this query TWO :

SELECT * FROM TEST_RANDOM WHERE EMPNO = '236400';

is running with cost 1924.

This table TEST_RANDOM has 1.000.000 rows, I created this table so:

Create table test_normal (empno varchar2(10), ename varchar2(30), sal number(10), faixa varchar2(10));

Begin 
    For i in 1..1000000 
    Loop 
        Insert into test_normal values(
            to_char(i), dbms_random.string('U',30), 
            dbms_random.value(1000,7000), 'ND'
        ); 
        If mod(i, **10000)** = 0 then 
            Commit; 
        End if; 
    End loop; 
End; 

Create table test_random 
as
select /*+ append */ * from test_normal order by dbms_random.random;

I created a B-Tree index in the field EMPNO so:

CREATE INDEX IDX_RANDOM_1 ON TEST_RANDOM (EMPNO);

After this, the query TWO improved, and the cost changed to 4.

But the query ONE did not improve, because Oracle Database ignored it, for some reason Oracle Database understood that this query is not worth it to use the plan execution with the index...

My question is: What could we do to improve this query ONE performance? Because the solution of the index did not solve and its cost continues to be expensive...

Answer 1

For this query, Oracle does not use an index because the optimizer correctly estimated the number of rows and correctly decided that a full table scan would be faster or more efficient.

B-Tree indexes are generally only useful when they can be used to return a small percentage of rows, and your first query returns about 25% of the rows. It's hard to say what the ideal percentage of rows is, but 25% is almost always too large. On my system, the execution plan changes from full table scan to index range scan when the query returns 1723 rows - but that number will likely be different for you.

There are several reasons why full table scans are better than indexes for retrieving a large percentage of rows:

1. Single-block versus multi-block: In Oracle, like in almost all computer systems, it can be significantly faster to retrieve multiple chunks of data at a time (sequential access) instead of retrieving one random chunk of data at a time (random access).
2. Clustering factor: Oracle stores all rows in blocks, which are usually 8KB large and are analogous to pages. If the index is very inefficient, like if the index is built on randomly sorted data and two sequential reads rarely read from the same block, then reading 25% of all the rows from an index may still require reading 100% of the table blocks.
3. Algorithmic complexity: A full table scan reads the data as a simple heap, which is O(N). A single index access is much faster, at O(LOG(N)). But as the number of index accesses increases, the benefit wears off, until eventually using the index is O(N * LOG(N)).

Some things you can do to improve performance without indexes:

1. Partitioning: Partitioning is the idea solution for retrieving a large percentage of data from a table (but the option must be licensed). With partitioning, Oracle splits the logical table into multiple physical tables, and the query can only read from the required partitions. This can create the benefit of multi-block reads, but still limits the amount of data scanned.
2. Parallelism: Make Oracle work harder instead of smarter. But parallelism probably isn't worth the trouble for such a small table.
3. Materialized views: Create tables that only store exactly what you need.
4. Ordering the data: Improve the index clustering factor by sorting the table data by the relevant column instead of doing it randomly. In your case, replace order by dbms_random.random with order by empno. Depending on your version and platform, you may be able to use a materialized zone map to keep the table sorted.
5. Compression: Shrink the table to make it faster to read the whole thing.

That's quite a lot of information for what is possibly a minor performance problem. Before you go down this rabbit hole, it might be worth asking if you actually have an important performance problem as measured by a clock or by resource consumption, or are you just fishing for performance problems by looking at the somewhat meaningless cost metric?