RE: Performance off "count(*)"

From: Marco Gralike <>
Date: Sun, 20 Jul 2008 23:58:01 +0200
Message-ID: <3FA1E6332C73EB4391F729E4EDB7CED9AD5DDD@amisnt30.AMIS.local>

A made a post for my special "count(*)" test case called : XMLDB Performance: The Side Effects of a Simple "count(*)" ( <> )  

Thanks you all for finding some of the answers.  



Van: Jonathan Lewis [] Verzonden: za 19-7-2008 10:33
Onderwerp: Re: Performance off "count(*)"


XMLType table are a version of object tables, which means they have a hidden OID column called sys_nc_oid$.

The problem seems to be that the sys_nc_oid$ column (the objects unique object id) does not get declared with a not null constraint. So the unique index on this column cannot be used for the count.

I can't think of a mechanism that could (legally) ever allow the OID to be null, so it seems that you should be safe issuing

    alter table XXX modify sys_nc_oid$ not null;

If you do, then Oracle will automatically use the index in the count(*).

I'd raise an SR to get confirmation that this is safe before doing it on live data though.


Jonathan Lewis <>

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ

  • Original Message ----- From: "Marco Gralike" <> To: "Gints Plivna" <>; "Riyaj Shamsudeen" <> Cc: <> Sent: Friday, July 18, 2008 8:25 PM Subject: RE: Performance off "count(*)"

Thanks all for the examples. A lot (re-)think and I guess I will have to do some further testing.

In case you wonder, I am working on a: XMLType table, based on Binary XML Securefile storage with has the compression parameter set to high.


-- Received on Sun Jul 20 2008 - 16:58:01 CDT

Original text of this message