RE: Performance off "count(*)"
Date: Sun, 20 Jul 2008 23:58:01 +0200
Thanks you all for finding some of the answers.
Van: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk]
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.
Author: Cost Based Oracle: Fundamentals
The Co-operative Oracle Users' FAQ
- 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 Received on Sun Jul 20 2008 - 16:58:01 CDT