Re: Performance off "count(*)"

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Sat, 19 Jul 2008 09:33:44 +0100



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

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.


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


Other related posts: