Fetching data is too slow in Oracle DB (query comparison)

Question

I am hitting my head with the following problem: I have a table with more than 1,000,000,000 data. Now I am running the following query (acc_no is the primary key):

select acc_no from user where acc_no between 753976276998100 and 78776276998199

The above query ran in less than a second and fetched 100,000 records

But if I add one more column ("service_no") in the same query,

select acc_no,service_no from user where acc_no between 753976276998100 and 78776276998199

.. it is taking more than a minute. Why is that? Why is the first query taking less than a second, and the second query is taking more than a minute?

FYI : service_no is a NUMBER column

The first query uses the PK index to determine which rows to return and to get the values to return. It never has to leave the index. The second seeks within the index to determine which rows to return, but then it has to go out to the table to get the service_no value. — Ed Gibbs
– Ed Gibbs, Commented Jan 27, 2015 at 18:27
I am really sorry. I dont understand your answer. Both queries determine which rows to return in the same way right? since both have same condition. The only difference is that, i am fetching one more column. Will you please rephrase your answer. — arunan
– arunan, Commented Jan 27, 2015 at 18:32
Did you really name your table 'user'? It is a reserved word in Oracle and I am surprised it works. — Jeff
– Jeff, Commented Jan 27, 2015 at 18:36
Take a look at Alex's answer below; he's saying the same thing but he gives a lot more detail. — Ed Gibbs
– Ed Gibbs, Commented Jan 27, 2015 at 18:37
@Jeff I dont want to reveal the production table name.. so i came up with that dummy name :) — arunan
– arunan, Commented Jan 27, 2015 at 18:38

Alex Poole · Accepted Answer · 2015-01-27 18:40:33Z

If you look at the execution plan for both queries, you'll see that the first query is fulfilled with just an index range scan:

explain plan for
select acc_no from t42
where acc_no between 753976276998100 and 78776276998199;

select * from table (dbms_xplan.display);

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |     
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |     1 |    10 |     0   (0)|          |
|*  1 |  FILTER           |              |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| SYS_C0090827 |     1 |    10 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

... which can be quite fast; but the second query has an additional step, table access by index rowid:

explain plan for
select acc_no, service_no from t42
where acc_no between 753976276998100 and 78776276998199;

select * from table (dbms_xplan.display);

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    14 |     0   (0)|          |
|*  1 |  FILTER                      |              |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T42          |     1 |    14 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SYS_C0090827 |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

When you only query for columns that exist in the index - acc_no in this case, which is in the primary key's backing index - only the index has to be touched. There is no need to go and look at the underlying table data for the values you already have from the indexed column.

When your select list includes columns that are not in the index the table data has to be retrieved too, because the other column - service_no is not in the index. That is another disk operation access the data blocks in the table segments. The table data is likely to be scattered across more blocks than the index as well, which amplifies the effect as you might have to fetch a different block for every matching row.

Basically it's having to do much more work to access more data from the disk, so it's going to take longer.

Is there anything that can be done to make table access quicker? De-fragmentation?

Collectives™ on Stack Overflow

Fetching data is too slow in Oracle DB (query comparison)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related