Oracle TOP optimizing

Question

I'm having trouble optimizing a statement. The corrisponding table (INTERVAL_TBL) contains about 11.000.000 rows which causes that statement to take round about 8 seconds on my test system. Even on a dedicated Oracle Server (24gb RAM, 17gb DB size) it takes about 4 - 5 seconds.

SELECT 
ID, STATUS_ID, INTERVAL_ID, BEGIN_TS, END_TS, PT, DISPLAYTEXT, RSC
FROM 
(
SELECT 
    INTERVAL_TBL.ID, INTERVAL_TBL.INTERVAL_ID, INTERVAL_TBL.STATUS_ID, INTERVAL_TBL.BEGIN_TS, INTERVAL_TBL.END_TS, INTERVAL_TBL.PT, ST_TBL.DISPLAYTEXT, ST_TBL.RSC,
    RANK() OVER (ORDER BY BEGIN_TS DESC) MY_RANK
FROM INTERVAL_TBL
INNER JOIN ST_TBL ON ST_TBL.STATUS_ID = INTERVAL_TBL.STATUS_ID
WHERE ID = '<id>'
)
WHERE MY_RANK <= 10

First of all I'd like to know if there is a way to optimize the Statement (Select most recent rows ordered by BEGIN).

Second I'd like to know if someone can make suggestions for an Index based on the statement.

EDIT:

Explain Plan:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT   |                  |   525K|    79M| 58469   (1)| 00:00:03 |
|*  1 |  HASH JOIN         |                  |   525K|    79M| 58469   (1)| 00:00:03 |
|   2 |   TABLE ACCESS FULL| ST_TBL           |    46 |  2438 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| INTERVAL_TBL     |   525K|    52M| 58464   (1)| 00:00:03 |


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ST_TBL"."STATUS_ID"="INTERVAL_TBL"."STATUS_ID")
   3 - filter("INTERVAL_TBL"."ID"='aef830a6-275b-4713-90da-9135f3f91a32'")

Rows in INTERVAL_TBL: 10.673.122
Rows in ST_TBL: 46
Rows in joined subset: 10.673.122
Rows in joined subset with filter on ID: 530.073

Ideally it would get down to about some milliseconds. Thats what that statement takes in MS SQL Server with 10.000.000 rows.

Tuning is all about the details. How many rows in each table? How many in the joined result set (is that the 11 million)? How many when filtered by `ID` (and what table does that belong to)? Also, it is pretty hard to make any concrete observation about query performance without seeing its explain plan. Where does the time go? — APC, May 12 '17 at 07:56
To be honest 4-5 seconds isn't too shabby for sorting 11 million rows. What time do you want to get it down to? — APC, May 12 '17 at 07:58
Did you try using `fetch first 10 rows only` instead of a window function? — , May 12 '17 at 09:31
Unfortunately that's not an option because I need to support older Oracle Versions. — Michael, May 12 '17 at 09:55
What is the data type of `interval_tbl.id`? According to your sql, you are passing a string, but being an ID, I would expect this to be numeric and by passing a string you are causing an implicit type conversion of the column values and not allowing Oracle to use a possible index on the column. — unleashed, May 12 '17 at 12:26

theRealBobbl · Answer 1 · 2017-05-12T09:03:56.090

First of all I would perform the inner join to ST_TBL in the outer scope, I got an speed up with this. It reduces the Cost, IO Cost an Bytes in my Execution Plan comparing both variants. I assume that columns PT, DISPLAYTEXT and RSC are part of the table ST_TBL.

SELECT 
    ID, STATUS_ID, INTERVAL_ID, BEGIN_TS, END_TS, SRESULT.PT, SRESULT.DISPLAYTEXT, SRESULT.RSC
FROM 
(
    SELECT 
        ID, INTERVAL_ID, INTERVAL_TBL.STATUS_ID, BEGIN_TS, END_TS,
        RANK() OVER (ORDER BY BEGIN_TS DESC) MY_RANK
    FROM INTERVAL_TBL
    WHERE ID = '<id>'
) SRESULT
INNER JOIN ST_TBL ON ST_TBL.STATUS_ID = SRESULT.STATUS_ID
WHERE MY_RANK <= 10

Oracle TOP optimizing

1 Answers1