| Query Performance Across Date Ranges [message #268480] |
Tue, 18 September 2007 16:55  |
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 #268539 is a reply to message #268485] |
Wed, 19 September 2007 00:57  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Assuming without testing is a dangerous thing 
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 |
rleishman
Messages: 3728
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 |
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 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
