Re: Count(*) slower than *

From: Randolf Geist <>
Date: Tue, 16 Mar 2010 01:50:22 -0700 (PDT)
Message-ID: <>

On Mar 15, 9:54 pm, "bob123" <> wrote:
> I have the following query (
> with * => <1s
> with count(*) => 7:52 min
> do you know why ?

If you want to understand why the usual advice applies: Trace the execution with 10046 extended trace to find out where the excess work happens. The COUNT(*) plan includes a INDEX FULL SCAN as inner row source of a NESTED LOOP operation that looks suspicious at first glance - it could be a bug caused by the FIRST_ROWS(n) mode in conjunction with the insane OICA and OIC parameters used by Siebel in your early version.

It also advisable to use DBMS_XPLAN.DISPLAY to format the EXPLAIN PLAN output. Unfortunately the AUTOTRACE feature in Oracle 9i still uses the old formatting (In 10g it uses the DBMS_XPLAN.DISPLAY output). In particular the "Predicate Information" section of the DBMS_XPLAN output is very helpful to understand which operation applies which filter.


Oracle related stuff blog:

Co-author of the "OakTable Expert Oracle Practices" book: Received on Tue Mar 16 2010 - 03:50:22 CDT

Original text of this message