oracle-databaseperformanceindexingoracle11g

Oracle Strange Index Behavior?

I have a non-clustered index on this column (TOM) in this table (SP_ORD_1PC_PASS).

This query returns instantly:

SELECT MAX(TOM) FROM SP_ORD_1PC_PASS

This query also returns instantly:

SELECT MIN(TOM) FROM SP_ORD_1PC_PASS

However, this query takes 3 minutes to return:

SELECT MIN(TOM), MAX(TOM) FROM SP_ORD_1PC_PASS

Anything I try to do with TOM column besides a single SELECT MIN or SELECT MAX results in very long query times.

I've never seen this type of behavior on an indexed column.

Other notes:

enter image description here

EDIT

I did alot of testing with this.

This query returns in 3 minutes:

SELECT * FROM SP_ORD_1PC_PASS
ORDER BY TOM ASC

This query returns instantly:

SELECT * FROM SP_ORD_1PC_PASS
WHERE TOM > SYSDATE-1000000
ORDER BY TOM ASC

... even though the WHERE clause doesn't exclude any data from the query result ...

I guess, problem solved. But... why???

Solution

  • For the first problem, the MIN/MAX index optimization only works for either MIN or MAX, but not both. But a simple rewrite of the query into two subqueries will enable the index access, as described in this answer here.

    For the second problem, the condition TOM > SYSDATE-1000000 provides useful information to Oracle that enables index access. While you may know that the column TOM is always non-null, Oracle does not know unless you create a non-null constraint. Without that constraint, Oracle must assume that the column contains a NULL value. And since B-tree indexes do not (usually) contain NULLs, the index cannot in the query. But with that new condition, only non-NULL values could possibly be returned, so Oracle knows the index access is possible. (This is why constraints are an under-appreciated way to improve performance.)

    Create test schema

    -- drop table sp_ord_1pc_pass;
create table sp_ord_1pc_pass(a number, tom date);

insert into sp_ord_1pc_pass
select level, sysdate + level
from dual
connect by level <= 400000;

create index sp_ord_1pc_pass_idx on sp_ord_1pc_pass(tom);

begin
    dbms_stats.gather_table_stats(user, 'SP_ORD_1PC_PASS');
end;
/

    Query without expression uses full table scan

    This explain plan sorts all of the data before any is returned to the client, which makes it look slow.

    explain plan for
select *
from sp_ord_1pc_pass
order by tom asc;

select * from table(dbms_xplan.display(format => 'basic'));

Plan hash value: 3972713478
 
----------------------------------------------
| Id  | Operation          | Name            |
----------------------------------------------
|   0 | SELECT STATEMENT   |                 |
|   1 |  SORT ORDER BY     |                 |
|   2 |   TABLE ACCESS FULL| SP_ORD_1PC_PASS |
----------------------------------------------

    Query with expression uses an index

    This explain plan reads an index in order, so it will start returning data almost immediately. However, the total time to return all the results is likely actually going to take longer than a full table scan and sort. This query most likely only feels faster in an IDE that only returns the top 50 rows.

    explain plan for
select * from sp_ord_1pc_pass
where tom > sysdate-1000000
order by tom asc;

select * from table(dbms_xplan.display(format => 'basic'));

Plan hash value: 1314632260
 
-----------------------------------------------------------
| Id  | Operation                   | Name                |
-----------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |
|   1 |  TABLE ACCESS BY INDEX ROWID| SP_ORD_1PC_PASS     |
|   2 |   INDEX RANGE SCAN          | SP_ORD_1PC_PASS_IDX |
-----------------------------------------------------------