From oracle-l-bounce@freelists.org Fri Oct 14 10:25:59 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j9EFPsYd032611 for ; Fri, 14 Oct 2005 10:25:59 -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 j9EFPgvX032570 for ; Fri, 14 Oct 2005 10:25:42 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 71A6C1FE424; Fri, 14 Oct 2005 10:25:28 -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 23848-04; Fri, 14 Oct 2005 10:25:28 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E1EC51FE071; Fri, 14 Oct 2005 10:25:27 -0500 (EST) Message-ID: <5B257A26B4845C469B87871B6CEFE5070331AEEE@usnjc04wmx003.tdwaterhouse.com> From: JayMiller@TDWaterhouse.com To: oracle-l@freelists.org Subject: RE: Why is Oracle choosing a different execution plan? Date: Fri, 14 Oct 2005 11:22:57 -0400 MIME-Version: 1.0 Content-Type: text/plain X-archive-position: 27001 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: JayMiller@TDWaterhouse.com Precedence: normal Reply-To: JayMiller@TDWaterhouse.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Level: X-Spam-Status: No, hits=-4.0 required=5.0 tests=AWL,BAYES_00,NO_REAL_NAME autolearn=no version=2.63 Also note that in case 2 it's still reading the entire index (fast *full* scan). This will certainly be faster than a full table scan but implies that either the cardinality is very low so Oracle believes a full scan will be faster or that there's an issue with the index. What %age of rows does 'J' take up (if you have a histogram on the column) or how many unique values of that column exist (if there is no histogram)? Also, what columns is IDX_ODJ_ARCHIVIEREN indexed on? Thanks, Jay Miller Sr. Oracle DBA x68355 -----Original Message----- From: Reidy, Ron [mailto:Ron.Reidy@arraybiopharma.com] Sent: Friday, October 14, 2005 6:11 AM To: HELMUT.DAIMINGER@wwk.de; oracle-l@freelists.org Subject: RE: Why is Oracle choosing a different execution plan? Because in query #1, you are asking for all columns. The optimizer sees this and makes the decision to FTS because (most likely) going to the index and then to the table will entail the use of more I/O. Query 2 is asking for a count of rows, not the data from all the rows. -- Ron Reidy Lead DBA Array BioPharma, Inc. -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] Sent: Friday, October 14, 2005 3:55 AM To: oracle-l@freelists.org Subject: Why is Oracle choosing a different execution plan? Hi! We are experiencing somewhat weird behavior when executing the following statements... Why is Oracle perfomring a full table scan in statement 1 and an index scan in statement 2? The table has a little over 200k rows and all statistics are newly calculated. 1) select * from odin_job where odj_archivieren = 'J'; ... PROD_1313235 ENV_1009473 1 14.10.2005 02:12:39 eff2 PROD_1317238 ENV_1013349 1 14.10.2005 02:15:16 eff2 PROD_1317240 ENV_1007975 1 14.10.2005 02:15:16 eff2 ... 4827 rows selected. Elapsed: 00:00:37.05 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1194 Card=49213 Bytes=21309229) 1 0 TABLE ACCESS (FULL) OF 'ODIN_JOB' (Cost=1194 Card=49213 Bytes=21309229) 2) SQL> select count(*) from odin_job where odj_archivieren = 'J'; COUNT(*) ---------- 4827 1 row selected. Elapsed: 00:00:00.05 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=36 Card=1 Bytes=2) 1 0 SORT (AGGREGATE) 2 1 INDEX (FAST FULL SCAN) OF 'IDX_ODJ_ARCHIVIEREN' (NON-UNI QUE) (Cost=36 Card=49213 Bytes=98426) Do you have an ideas? Thanks, Helmut ----------------------------------------- This message is confidential and sent by TD Waterhouse solely for use by the intended recipient. If you are not the intended recipient, you are hereby notified that any use, distribution or copying of this communication is strictly prohibited. This should not be deemed as an offer or solicitation, to buy or sell any product. Any 3rd party information contained herein was prepared by sources deemed reliable, but is not guaranteed. TD Waterhouse does not accept electronic instructions that would require an original signature. Information received by or sent from TD Waterhouse is stored, subject to review, and may be produced to regulatory authorities or others with a legal right to such. -- http://www.freelists.org/webpage/oracle-l