From oracle-l-bounce@freelists.org Fri Apr 8 11:59:08 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j38Gx8Ju028962 for ; Fri, 8 Apr 2005 11:59:08 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j38Gx6em028954 for ; Fri, 8 Apr 2005 11:59:07 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 54C83932F5; Fri, 8 Apr 2005 10:53:40 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 09717-10; Fri, 8 Apr 2005 10:53:40 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C3FEA93218; Fri, 8 Apr 2005 10:53:39 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding; b=m0CRUygKGd/kjBEA0vd82Btp0qiG1wzmtJUsW/jOyn6fqI0uDq/5W0jud8Q794c4xMD2sBT83aY5h3aCp4asO/BcY7rlWImYNbOygYWZxHBo0mThF20djXnxPluR2/ZvtwRSQSuzdf32o0vFxHACN8YGCm2fiJ+S9I4BF/GjhnE= Message-ID: <52a152eb0504080851632e82c5@mail.gmail.com> Date: Fri, 8 Apr 2005 11:51:47 -0400 From: Christo Kutrovsky To: oracle-l Subject: 10g visual glitch? - skip scan instead of range scan Mime-Version: 1.0 Content-type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-archive-position: 18214 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: kutrovsky.oracle@gmail.com Precedence: normal Reply-To: kutrovsky.oracle@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Checker-Version: SpamAssassin 2.60 (1.212-2003-09-23-exp) on air891.startdedicated.com X-Spam-Status: No, hits=0.0 required=5.0 tests=AWL,LINES_OF_YELLING autolearn=ham version=2.60 X-Spam-Level: Wolfgang, this one is specifically to you, given your endless interest in the undergrounds of the optimizer. I've started this one with the idea of testing random IO performance with ASM. So i've created a table and a simple index on the table, then I populate the table with random data, thus producing a very "unfriendly" index. Of course, I analyzed the table and index, and started testing only to discover then instead of the expected INDEX RANGE SCAN, oracle wanted to do an INDEX SKIP SCAN. However upon closer investigation of the ACCESS PREDICATES, it seemed that oracle was doing a range scan (and it behaved like that). The full scale of the test involved 1 million rows, which created both a table and an index of 8 gb each. I have reduced the test case to ~80 mb each, and provided the necessary statistics changes to produce hte same statistics on this smaller scale test. a - "unique" (randomly populated) b - same value c - same value d - same value I have a TAR open, and here's what I've submited originally (paste in notepad for better reading): SQL> create table k_indx ( a number, b char(2000), c char(2000), d char(2000)); Table created Executed in 0.21 seconds SQL> create index k_indx$ind on k_indx (a, b,c); Index created Executed in 0.2 seconds declare p number; begin for i in 1..1000000 loop insert into k_indx values (dbms_random.value(1,10000000), 'a','b','c'); if i mod 100000 = 0 then commit; end if; end loop; end; / (several hours pass by) SQL> explain plan for select a,b from k_indx k where 1=1 and a between 1 and 1000 and b = 'a' ; Explained Executed in 0.16 seconds SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 1192287170 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2023 | 4 (0)| 00:00:01 | |* 1 | INDEX SKIP SCAN | K_INDX$IND | 1 | 2023 | 4 (0)| 00:00:01 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A">=1 AND "B"='a' AND "A"<=1000) filter("B"='a') 14 rows selected Executed in 0.701 seconds During my efforts to reproduce this test for Oracle in a smaller scale, I've discovered that wether it uses SKIP SCAN or RANGE SCAN is dependand on the min/max values of the column ! Thus the modified (small scale) test case: drop table k_indx_M; create table k_indx_M ( a number, b char(2000), c char(2000), d char(2000)); create index k_indx_M$ind on k_indx_M (a, b,c); declare p number; begin insert into k_indx_m values (1, 'a','b','c'); insert into k_indx_m values (10000000, 'a','b','c'); for i in 1..10000 loop insert into k_indx_m values (dbms_random.value(1,10000000), 'a','b','c'); if i mod 1000 = 0 then commit; end if; end loop; end; / exec DBMS_STATS.GATHER_TABLE_STATS (user, 'K_INDX_M', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE , cascade => true); exec DBMS_STATS.SET_TABLE_STATS (user, 'K_INDX_M', numrows => 1000285, numblks => 1000001, avgrlen => 6024); exec DBMS_STATS.SET_COLUMN_STATS (user, 'K_INDX_M', 'A', distcnt=>1000285, nullcnt =>0); exec DBMS_STATS.SET_INDEX_STATS (user,'K_INDX_M$IND',numrows=>1010262, numlblks=>1010262, numdist=>1010262); I have both tables available, so if you want me to run any tests let me know. This is in RAC environemnt, but these tests are single nodes so it should not matter. I am waiting on Oracle to explain this right now. So far they have suggested histograms ... (method_opt => 'FOR ALL INDEXED COLUMNS SIZE 100'), but i've rejected this as a solution. I am asking them to confirm if this is a bug or not. What's your opion ? Anyone willing to jump in is welcome. -- Christo Kutrovsky Database/System Administrator The Pythian Group -- http://www.freelists.org/webpage/oracle-l