From oracle-l-bounce@freelists.org Wed Feb 25 13:30:57 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i1PJUuE08377 for ; Wed, 25 Feb 2004 13:30:56 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i1PJUuo08372 for ; Wed, 25 Feb 2004 13:30:56 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0F85D39494E; Wed, 25 Feb 2004 14:32:21 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 25 Feb 2004 14:31:16 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from brmea-mail-3.sun.com (brmea-mail-3.Sun.COM [192.18.98.34]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9EBD7394D7E for ; Wed, 25 Feb 2004 14:31:13 -0500 (EST) Received: from phys-giza-1 ([129.147.4.102]) by brmea-mail-3.sun.com (8.12.10/8.12.9) with ESMTP id i1PJY0i5025057 for ; Wed, 25 Feb 2004 12:34:00 -0700 (MST) Received: from sun.com (sr1-ubrm-03.Central.Sun.COM [129.147.4.9]) by giza-mail1.Central.Sun.COM (iPlanet Messaging Server 5.2 HotFix 1.16 (built May 14 2003)) with ESMTP id <0HTN00CJJNOO2T@giza-mail1.Central.Sun.COM> for oracle-l@freelists.org; Wed, 25 Feb 2004 12:34:00 -0700 (MST) Date: Wed, 25 Feb 2004 12:34:00 -0700 From: Daniel Fink Subject: Index scan and redundant sorting To: oracle-l@freelists.org Message-id: <403CF8A8.6944A2CF@sun.com> MIME-version: 1.0 X-Mailer: Mozilla 4.79C-CCK-MCD [en] (X11; U; SunOS 5.9 sun4u) Content-Type: multipart/alternative; boundary=------------18BF2D93B26D07ED57C5DA85 X-Accept-Language: en X-archive-position: 1974 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Daniel.Fink@Sun.COM Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l --------------18BF2D93B26D07ED57C5DA85 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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) --------------18BF2D93B26D07ED57C5DA85 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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) --------------18BF2D93B26D07ED57C5DA85-- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@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 -----------------------------------------------------------------