Home » SQL & PL/SQL » SQL & PL/SQL » range scan
range scan [message #19579] Fri, 29 March 2002 02:56 Go to next message
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 Go to previous message
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.
Previous Topic: Re: urgent
Next Topic: About select into
Goto Forum:
  


Current Time: Thu Mar 28 06:55:34 CDT 2024