Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Index scan and redundant sorting

RE: Index scan and redundant sorting

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Wed, 25 Feb 2004 14:37:33 -0500
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF03B452C7@bosmail00.bos.il.pqe>


Dan,  

Only an INDEX FULL SCAN (walks the tree, does single block reads) provides sorted output.
An INDEX FAST FULL SCAN (does not walk tree structure, does multi-block reads, discards branch blocks) does NOT provide sorted output.  

-Mark
   

Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Imagination was given to man to compensate him for what he is not, and a sense of humor was provided to console him for what he is." --Horace Walpole

	-----Original Message-----
	From: Daniel Fink [mailto:Daniel.Fink_at_Sun.COM] 
	Sent: Wednesday, February 25, 2004 2:34 PM
	To: oracle-l_at_freelists.org
	Subject: Index scan and redundant sorting
	
	
	A query (with an order by) is able to satisfy it's column list
by scanning an index. This scan will return the rows in sorted order, but the query still executes a sort (confirmed by 10046 trace). Should not the result set from the fast full scan be correctly ordered? This would make the sort redundant, but very expensive in terms of response time.           
	Table: 
	 random_data 
	 Name                Null?    Type 
	 ------------------- -------- ------------------- 
	 REC_NO              NOT NULL NUMBER 
	 INSERT_TEXT                  VARCHAR2(200) 
	 INSERT_DATE                  DATE 
	 LARGE_RANDOM_NUM             NUMBER 
	 SMALL_RANDOM_NUM    NOT NULL NUMBER  <--- COLUMN OF INTEREST 
	 ROWID_BLOCKNUM               NUMBER 
	 ROWID_ROWNUM                 NUMBER 
	  

	select column_name 
	from user_ind_columns 
	where index_name = 'IX_RD_SMALL_RN' 

	COLUMN_NAME 
	----------------- 
	SMALL_RANDOM_NUM 
	  

	set autotrace traceonly explain 
	select small_random_num 
	from random_data 
	order by small_random_num; 

	Execution Plan 
	---------------------------------------------------------- 
	   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=7477
Card=1000000 Bytes=2000000) 
	   1    0   SORT (ORDER BY) (Cost=7477 Card=1000000
Bytes=2000000)       <------ Is this sort needed? 
	   2    1     INDEX (FAST FULL SCAN) OF 'IX_RD_SMALL_RN'
(NON-UNIQUE) (Cost=722 Card=1000000 Bytes=2000000)

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
Received on Wed Feb 25 2004 - 13:34:32 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US