CODEWITHSUNDEEP
sqloracle-databasedatecounttimestamp
Iozan
Iozan

Reputation: 31

Oracle Timestamp index very slow

I've got this table:

CREATE TABLE "TEST"."TEST_REQUESTS"
  (
    "REQUEST_ID" NUMBER(*,0) NOT NULL ENABLE,
    "RECEPTION_TIME" TIMESTAMP (6) NOT NULL ENABLE,
    "OPERATION" VARCHAR2(25 BYTE) NOT NULL ENABLE,
    "XML_HEADER_IN" CLOB,
    "XML_BODY_IN" CLOB NOT NULL ENABLE,
    "STATUS_ID" NUMBER(*,0) NOT NULL ENABLE,
    "CUSTOMER"  VARCHAR2(25 BYTE)
  )

and it has 2 indexes:

CREATE INDEX "TEST"."TEST_REQUESTS_STATUS_IDX" ON "TEST"."TEST_REQUESTS"
    (
      "STATUS_ID" DESC
    )
CREATE INDEX "TEST"."TEST_REQUESTS_TIME_IDX" ON "TEST"."TEST_REQUESTS"
    (
      "RECEPTION_TIME"
    )

I also have a procedure more or less like this:

PROCEDURE TEST_LoadHistoryRequestRange (diasMantenidosIN IN number, [...] numRequestOut OUT number [...]) IS

tsStartLimit TIMESTAMP;

BEGIN

tsStartLimit := sysdate - diasMantenidosIN;

SELECT count(REQUEST_ID) into numRequestOut FROM TEST_REQUESTS WHERE STATUS_ID=0 and RECEPTION_TIME<tsStartLimit;

[...]

END;

Now the table has grown up to near 15 million registers... but, is it logical to take near 10 minutes long to make that simple COUNT? What can be wrong with this?

Thank you very much!!

EDIT: This is the explain plan for the select count:

Plan hash value: 378939817

----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                           |     1 |    14 |     4   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |                           |     1 |    14 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TEST_REQUESTS             |  1125 | 15750 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | TEST_REQUESTS_STATUS_IDX  |     1 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Upvotes: 0

Views: 2652

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

It can take a long time. The best index for your query is a composite index:

CREATE INDEX "TEST"."TEST_STATUS_REQUESTS_TIME_IDX" ON "TEST"."TEST_REQUESTS"
    (
      STATUS,
      "RECEPTION_TIME",
      REQUEST_ID
    );

Note that the order of the columns in the index makes a difference.

Upvotes: 2

Related Questions