Re: Performance off "count(*)"

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


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
   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" <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.


Other related posts: