Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from puck1183.startdedicated.com (localhost [127.0.0.1]) by puck1183.startdedicated.com (Postfix) with ESMTP id 45D431961072 for ; Thu, 10 Oct 2013 17:20:54 +0200 (CEST) Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by puck1183.startdedicated.com (Postfix) with ESMTP for ; Thu, 10 Oct 2013 17:20:54 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B754F26351; Thu, 10 Oct 2013 11:20:52 -0400 (EDT) X-Virus-Scanned: Debian amavisd-new at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id cp5+P-KhG7W1; Thu, 10 Oct 2013 11:20:52 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C1A5526344; Thu, 10 Oct 2013 11:20:11 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 10 Oct 2013 11:19:30 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 24CEE2629B; Thu, 10 Oct 2013 11:19:30 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id aRVNyIksMYaW; Thu, 10 Oct 2013 11:19:30 -0400 (EDT) Received: from hobvmisav07.pubnet.metro (metmail3.nashville.gov [170.190.30.103]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D836526294; Thu, 10 Oct 2013 11:19:29 -0400 (EDT) Received: from hobvmisav07.pubnet.metro (unknown [127.0.0.1]) by IMSVA (Postfix) with ESMTP id 39643378092; Thu, 10 Oct 2013 10:19:12 -0500 (CDT) Received: from HOBSVISHC01.nashville.org (unknown [170.190.14.23]) by hobvmisav07.pubnet.metro (Postfix) with ESMTPS id 209BA37808A; Thu, 10 Oct 2013 10:19:12 -0500 (CDT) Received: from DCSOSVMS03.dcso.org (10.56.67.68) by HOBSVISHC01.nashville.org (170.190.14.23) with Microsoft SMTP Server (TLS) id 14.2.318.4; Thu, 10 Oct 2013 10:19:28 -0500 Received: from DCSOSVMS02.dcso.org ([169.254.1.26]) by DCSOSVMS03.dcso.org ([169.254.2.211]) with mapi id 14.02.0247.003; Thu, 10 Oct 2013 10:19:28 -0500 From: "Storey, Robert (DCSO)" To: =?iso-8859-1?Q?David_Ram=EDrez_Reyes?= CC: "Uzzell, Stephan" , "Laimutis.Nedzinskas@seb.lt" , Oracle L , "oracle-l-bounce@freelists.org" Subject: RE: Full scan vs index Thread-Topic: Full scan vs index Date: Thu, 10 Oct 2013 15:19:27 +0000 Message-ID: References: In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [170.190.82.98] Content-type: text/plain MIME-Version: 1.0 X-TM-AS-MML: No Content-Transfer-Encoding: 8bit X-archive-position: 50903 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: RStorey@DCSO.nashville.org Precedence: normal Reply-To: RStorey@DCSO.nashville.org List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l To follow up, Chris pointed out my error in my hint. Correctly formatted but wrong index. When I used the correct index in the hint, the index was used and the plan cost jumped from 1152 to 1840. Thanks all for the responses. Its been a learning morning From: David Ramírez Reyes [mailto:dramirezr@gmail.com] Sent: Thursday, October 10, 2013 9:11 AM To: Storey, Robert (DCSO) Cc: Uzzell, Stephan; Laimutis.Nedzinskas@seb.lt; Oracle L; oracle-l-bounce@freelists.org Subject: Re: Full scan vs index In some way yes, but more than the number of records on the table is the average of records on the results; the first query gives you as result the 14.5% (87,000 of 600,000) of the total records, which make the optimizer evaluate as a better option the Full Table scan; if the number of records were less (probably less than 5%), it may probably use Index instead of Full table scan. Just one more questions: Is the ordernum column defined (the PK) autoincrement -with a sequence and trigger, of course-? -- http://www.freelists.org/webpage/oracle-l