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@xxxxxxxxxxxxxxxxxx]
Verzonden: za 19-7-2008 10:33
Aan: oracle-l@xxxxxxxxxxxxx
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@xxxxxxx>
To: "Gints Plivna" <gints.plivna@xxxxxxxxx>; "Riyaj Shamsudeen"
<riyaj.shamsudeen@xxxxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>
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

Other related posts: