RE: Table design

From: Powell, Mark <mark.powell2_at_hp.com>
Date: Tue, 29 Jan 2013 15:44:30 +0000
Message-ID: <1E24812FBE5611419EFAFC488D7CCDD117CD09D1_at_G5W2734.americas.hpqcorp.net>



I am going to agree with Hans. Chained rows are rows that are too large to fit into a block and as such a high chain count such as shown for the table with all but 4 rows chained is not an indication of a potential performance problem but rather only indicates the rows are bigger than your database block size. Migrated rows which Oracle records in the chained row count along with chained rows on the other hand are potential performance problems for indexed access, but that is not what your statistics show.

Splitting the data into multiple tables could in fact result in more IO to fetch the data for queries that need most of the row.

If the CLOB data is usually greater in length than 2K bytes and is not needed on most queries then moving the entire CLOB out of line would likely be beneficial, but you have several fairly lengthy columns so the rows are still likely to chain.

If all the data is not present on initial insert verifying that the pctfree is set to reserve adequate space for the data added later would be a good idea.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hans Forbrich Sent: Monday, January 28, 2013 8:12 PM
To: oracle-l_at_freelists.org
Subject: Re: Table design

Are you thinking about the redesign *because* of the chained rows? If so, why is this worrying you? (My reasoning - with the number of CLOBS you have in some of those tables, I don't see you significantly avoiding chained rows in any case, as long as they are inline.) If you split the tables vertically, how often will you end up recombining (joining) to make the application happy? What will that cost (in i/o)?

Have you considered moving the CLOBS off to LOB segments?

How many of the principles from the SecureFiles and Large Object Developer's Guide (http://www.oracle.com/pls/db112/portal.portal_db?selected=5&frame=#application_development_concepts) have you put into practice?

/Hans

On 28/01/2013 4:35 PM, Raju Angani wrote:
> Hi,
> I have 2 tables in a schema of 800+ tables with column count of 60+. I
> see lot of chained rows in all 3 tables.
> I'm thinking of restructing the tables as multiple tables, also
> considering using cluster(create cluster). read about clustering table
> not good for lot of updates.
>
> ...
> c30 INTEGER NOT NULL,
> c31 INTEGER NOT NULL,
> c32 NCLOB,
> c33 NCLOB,
> c34 DATE,
> c35 DATE,
> c36 NVARCHAR2(21),
> c37 NCLOB,
> c38 NVARCHAR2(21),
> c39 INTEGER,
> c40 INTEGER,
> c41 NUMBER(1) DEFAULT 0 NOT NULL,
> c42 NVARCHAR2(2000),
> c43 NVARCHAR2(2000),
> c44 NVARCHAR2(255),
> c45 NVARCHAR2(2000),
> c46 NUMBER(1) DEFAULT 0 NOT NULL,
> c47 NCLOB,
> c48 NCLOB,
> c49 NVARCHAR2(255),
> c50 NCLOB,
> c51 NUMBER(38),
> c52 NUMBER(38),
> c53 NUMBER(38),
> c54 DATE,
> c55 INTEGER,
> c56 INTEGER,
> c57 NCLOB,
> c58 NCLOB,
> c59 NVARCHAR2(255),
> c60 NVARCHAR2(255),
> c61 INTEGER,
> c62 INTEGER,
> c63 INTEGER,
> c64 NUMBER(1) DEFAULT 0 NOT NULL,
> c65 NCLOB,
> c66 NCLOB,
> c67 NCLOB,
> c68 NCLOB
> );

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


--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jan 29 2013 - 16:44:30 CET

Original text of this message