From oracle-l-bounce@freelists.org Tue Feb 15 12:26:54 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j1FIQsTm018026 for ; Tue, 15 Feb 2005 12:26:54 -0600 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 j1FIQKem017883 for ; Tue, 15 Feb 2005 12:26:21 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 22FBB7162F; Tue, 15 Feb 2005 12:25:06 -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 27763-02; Tue, 15 Feb 2005 12:25:06 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 903BC716AC; Tue, 15 Feb 2005 12:25:05 -0500 (EST) From: "Lex de Haan" To: "'Looney, Jason'" , , , Subject: RE: is it possible in pl/sql? Date: Tue, 15 Feb 2005 18:23:19 +0100 MIME-Version: 1.0 Content-type: text/plain Thread-Index: AcUTgjGsEOrJeeijR4ql5Ja18SjdegAAClUQ X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 In-Reply-To: Message-Id: <20050215172325.A60BC5079A2@ha-smtp2.tiscali.nl> Content-Transfer-Encoding: 8bit X-archive-position: 16195 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: lex.de.haan@naturaljoin.nl Precedence: normal Reply-To: lex.de.haan@naturaljoin.nl 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.1 required=5.0 tests=AWL autolearn=ham version=2.60 X-Spam-Level: no, the results of count(*) and count(1) have always been the same: it returns the number of rows in the result set, regardless the column values all being NULL or not. You can look at it from this perspective: select t.* , 1 from your_table t; So you add an extra column to the result table, containing the literal 1 for *every* row. the count(1) function returns the number of non NULL values in that column ... kind regards, Lex. --------------------------------------------- Visit my website at http://www.naturaljoin.nl --------------------------------------------- -----Original Message----- From: Looney, Jason [mailto:Jason.Looney@echostar.com] Sent: Tuesday, February 15, 2005 18:17 To: 'lex.de.haan@naturaljoin.nl'; thomas.mercadante@labor.state.ny.us; joelgarry@anabolicinc.com; oracle-l@freelists.org Subject: RE: is it possible in pl/sql? My understanding was in the past count(*) returned a count of all non-null rows, where count(1) (or any constant) returned a count of all rows. I just verified this with 10g and this is not the case. The performance difference was to perform a full table scan, instead of counting leaf blocks in a primary key index or something like that. So maybe this was true in 6 or 7? SQL*Plus: Release 10.1.0.2.0 - Production on Tue Feb 15 10:12:37 2005 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options system@LOCALDB> drop table test; Table dropped. system@LOCALDB> CREATE TABLE test (t1 VARCHAR2(25) NULL, t2 NUMBER NULL); Table created. system@LOCALDB> system@LOCALDB> INSERT INTO test values (NULL, NULL) 2 / 1 row created. system@LOCALDB> / 1 row created. system@LOCALDB> / 1 row created. system@LOCALDB> / 1 row created. system@LOCALDB> / 1 row created. system@LOCALDB> / 1 row created. system@LOCALDB> / 1 row created. system@LOCALDB> / 1 row created. system@LOCALDB> / 1 row created. system@LOCALDB> select count(*) from test; COUNT(*) ---------- 9 system@LOCALDB> select count(1) from test; COUNT(1) ---------- 9 system@LOCALDB> select count(t1) from test; COUNT(T1) ---------- 0 -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Lex de Haan Sent: Tuesday, February 15, 2005 6:01 AM To: thomas.mercadante@labor.state.ny.us; joelgarry@anabolicinc.com; oracle-l@freelists.org Subject: RE: is it possible in pl/sql? no, certainly not. count(*) and count() should return the same result, under all circumstances.there used to be a performance difference, in the past. kind regards, Lex. --------------------------------------------- Visit my website at http://www.naturaljoin.nl --------------------------------------------- -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Mercadante, Thomas F Sent: Tuesday, February 15, 2005 13:58 To: 'joelgarry@anabolicinc.com'; oracle-l@freelists.org Subject: RE: is it possible in pl/sql? Does anyone know what the difference between count(*) & count(1) was *supposed* to be? Is there a theoretical functional difference? -----Original Message----- From: Joel Garry [mailto:joelgarry@anabolicinc.com] Sent: Monday, February 14, 2005 7:35 PM To: oracle-l@freelists.org Subject: Re: is it possible in pl/sql? Anthony Wilson wrote: >it's a myth according to the venerable Tom Kyte. The SQL engine silently rewrites >count(1) to count(*): Funny, he mentions that is a fact, but I've missed the reference to where the fact is shown. Though I'm inclined to believe anything Tom says, I find this particular one ironic in that he is talking about facts and opinions when he says it. And I'm certainly glad he mentions the differing case of 7.x, as that means I didn't get the myth completely out of thin air or confused it with some other function. Just means that old habits die hard. Joel Garry http://www.garry.to -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l