range scan [message #19579] |
Fri, 29 March 2002 02:56 |
jaf
Messages: 2 Registered: October 2000
|
Junior Member |
|
|
What is meant by range scan .
If range scan is happening whether index will be used or not.
In what way range scan is better than full table scan
|
|
|
Re: range scan [message #19585 is a reply to message #19579] |
Fri, 29 March 2002 08:06 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
A range scan is an execution plan that using the index to retrieve a range of values.
For example, we can:
sql>create table t as select * from all_objects;
Table created.
sql>create index t_index on t(created);
Index created.
sql>set autotrace on
sql>select count(*) from t
2 where created between
3 to_date('06/01/2001', 'mm/dd/yyyy')
4 and to_date('06/30/2001 11:59:59pm', 'mm/dd/yyyy hh:mi:ssam');
COUNT(*)
---------
2022
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'T_INDEX' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
190 bytes sent via SQL*Net to client
311 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Here we add a hint to force a full-table scan.
sql>select /*+ full(t) */ count(*) from t
2 where created between
3 to_date('06/01/2001', 'mm/dd/yyyy')
4 and to_date('06/30/2001 11:59:59pm', 'mm/dd/yyyy hh:mi:ssam');
COUNT(*)
---------
2022
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=9)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=6 Card=10 Bytes=90)
Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
101 consistent gets
0 physical reads
0 redo size
203 bytes sent via SQL*Net to client
311 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
You can see how the range scan plan can answer the query by only looking at the index - it never has to go to the table itself. The full-table scan requires a read of the table - and the associated db block gets and consistent gets.
There are cases when a full-table scan would be better than a range scan. If there is a column in your WHERE clause or your SELECT list that is not in the index expression, then once the number of rows satisfied by your condition reaches a certain percentage of the total rows in the table, a full-table scan might just end up being more efficient that reading most of the index and having to locate the corresponding row in the table. So, it really depends on the exact circumstances.
|
|
|