RE: how to determine number of chained rows

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 16 Apr 2013 00:55:48 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D90CE7E2_at_exmbx06.thus.corp>

For just a count:

analyze table XXX compute statistics for table;

It will populate user_tables.chain_cnt.

Remember afterwards to
analyse table XXX delete statistics;

Otherwise the optimizer will use the chain_cnt modify the cost of indexed access to the table; and you will need to gather the table stats again with dbms_stats.

See http://jonathanlewis.wordpress.com/2009/04/30/analyze-this/

Regards
Jonathan Lewis

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Jeffrey Beckstrom [JBECKSTROM_at_gcrta.org] Sent: 15 April 2013 15:57
To: oracle-l-freelists; oracle-db-l
Subject: how to determine number of chained rows

DBMS_STATS does not compute number of chained rows. analyze table x list chained rows - this does but if the table is large and has lots of chained rows, then need a lot of rollback space for rows inserted into the chained_rows table.

Is there another way to just get a count of chained rows?

Jeffrey Beckstrom
Database Administrator
Greater Cleveland Regional Transit Authority Information Systems
1240 W. 6th Street
Cleveland, Ohio 44113

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

Received on Tue Apr 16 2013 - 02:55:48 CEST