RE: Performance off "count(*)"

A made a post for my special "count(*)" test case called : XMLDB Performance: The Side Effects of a Simple "count(*)" (http://www.liberidu.com/blog/?p=494 <http://www.liberidu.com/blog/?p=494> )  

Thanks you all for finding some of the answers.  

Regards  

Marco

Van: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk] Verzonden: za 19-7-2008 10:33
Aan: oracle-l_at_freelists.org
Onderwerp: Re: Performance off "count(*)"

Marco,

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.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com <http://jonathanlewis.wordpress.com/>

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- From: "Marco Gralike" <Marco.Gralike_at_AMIS.nl> To: "Gints Plivna" <gints.plivna_at_gmail.com>; "Riyaj Shamsudeen" <riyaj.shamsudeen_at_gmail.com> Cc: <oracle-l_at_freelists.org> 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.

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Sun Jul 20 2008 - 16:58:01 CDT