Home » SQL & PL/SQL » SQL & PL/SQL » Query Performance Across Date Ranges
Query Performance Across Date Ranges [message #268480] Tue, 18 September 2007 16:55 Go to next message
gbix
Messages: 3
Registered: September 2007
Junior Member
We have a large table that contains real-time tracking data. It is updated almost continuously with new records. We often need to query the table to display records within a range i.e. last_dtm > sysdate - 4 hours. The size of the table makes full-table scans take quite a while.

My question is, is there any way to improve the performance of a SELECT query that is examining a range of data across datetime?

thanks,
gbix
Re: Query Performance Across Date Ranges [message #268482 is a reply to message #268480] Tue, 18 September 2007 16:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Do you have an index on that date-column?
Re: Query Performance Across Date Ranges [message #268485 is a reply to message #268480] Tue, 18 September 2007 17:13 Go to previous messageGo to next message
gbix
Messages: 3
Registered: September 2007
Junior Member
ah - should have mentioned that: we do not. I was under the impression that an index on a date field doesn't help performance for range queries, only for direct matches.
Re: Query Performance Across Date Ranges [message #268539 is a reply to message #268485] Wed, 19 September 2007 00:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Assuming without testing is a dangerous thing Smile
SQL> create table ind_test as select rownum as id, last_ddl_time from dba_objects;

Table created.

SQL> set autotrace traceonly explain
SQL> select * from ind_test where last_ddl_time > sysdate - 5;

Execution Plan
----------------------------------------------------------
Plan hash value: 2297781061

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |    21 |   462 |    12  (17)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| IND_TEST |    21 |   462 |    12  (17)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - filter("LAST_DDL_TIME">SYSDATE@!-5)

Note
-----
   - dynamic sampling used for this statement

SQL> create index test_ind on ind_test(last_ddl_time);

Index created.

SQL> select * from ind_test where last_ddl_time > sysdate - 5;

Execution Plan
----------------------------------------------------------
Plan hash value: 2881918769

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |    21 |   462 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| IND_TEST |    21 |   462 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IND |    21 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - access("LAST_DDL_TIME">SYSDATE@!-5)

Note
-----
   - dynamic sampling used for this statement


[Edit: Note however that having the index has (some) influence on the performance of the inserts.]

[Updated on: Wed, 19 September 2007 00:59]

Report message to a moderator

Re: Query Performance Across Date Ranges [message #268606 is a reply to message #268539] Wed, 19 September 2007 02:53 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Also note that Range Scans are much costlier per row than full table scans. So they're great for <1% of the table, but often rubbish for >10% of the table.

Range partitioning would be another thing to consider; it gives you the speed of a full scan but on a reduced data set.

Ross Leishman
Re: Query Performance Across Date Ranges [message #268727 is a reply to message #268480] Wed, 19 September 2007 08:34 Go to previous messageGo to next message
gbix
Messages: 3
Registered: September 2007
Junior Member
Intereseting. Now can anyone explain why this is so? My understanding of an index is that it would assist the query engine to find individual dates, but searching across a range of unsorted data would still require a full table scan - there isn't anything to lookup. Is it because Oracle performs the comparison only against the index values itself which is that much faster than searching through the actual dataset?
Re: Query Performance Across Date Ranges [message #268731 is a reply to message #268727] Wed, 19 September 2007 08:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You are right in that sorting across a range of unsorted data will generally require a full table scan, but an index is NOT unsorted data.
By creating an index, you explicitly create a sorted list of dates, with each date pointing to a single record in the table.
Thus Oracle can scan through a range of dates in the index and work out which rows in the table it needs to read
Re: Query Performance Across Date Ranges [message #268849 is a reply to message #268731] Wed, 19 September 2007 22:35 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you are still interested, here is an article on Understanding Indexes

Ross Leishman
Previous Topic: blob data migration
Next Topic: Snapshot refresh fails with ORA-12008, ORA-01410
Goto Forum:
  


Current Time: Tue Dec 06 00:08:44 CST 2016

Total time taken to generate the page: 0.14779 seconds