RE: Table design
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <mark.powell2@xxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 29 Jan 2013 11:31:31 -0500
Did I miss a reason why nvarchar2 is used instead of varchar2?
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Powell, Mark
Sent: Tuesday, January 29, 2013 10:45 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Table design
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Hans Forbrich
Sent: Monday, January 28, 2013 8:12 PM
To: oracle-l@xxxxxxxxxxxxx
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=#applicat
ion_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
> );
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
