nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

partition table llattrdata, Llattrblobdata.

Roman_Osipov

Dear colleagues,

We want to partition the table:

- llattrdata

- Llattrblobdata.

because their size is currently very big and our DBA says that problems may arise in the event of further growth

The most optimal for us: table partitioning by date of the document creation, and sections by years or months.

But they are no fields with date that can be used for partitioning.

It would be possible to make a reference partitioning, but in these tables do not have foreign keys (which can be used for partitioning).

Could you advices us what algorithm can be used to partition?

Can we use the following algorithms and how critical it is?

Algorithm I:

1. Add CREATE_DATE column and a trigger (on INSERT) in llattrblobdata. The trigger will add SYSDATE insert a row into a table.

2. The current records fill by values from the dtreecore table data

3. Create a foreign key (one-to-many) llattrblobdata.ID-> llattrdata.ID

4. Make a reference partitioning CREATE_DATE field

Algorithm II:

1. Remove Nullable flag DTreeCore.CreateDate column

2. Add foreign keys to DTreeCore.DataId-> llattrdata.ID (One-to-many) and DTreeCore.DataId-> llattrblobdata.ID (one-to-one)

3. Make a reference to DTreeCore partitioning table by creation date

Algorithm III: (partition on LLATTRBLOBDATA.ID)

1. Create a foreign key (one-to-many) llattrblobdata.ID-> llattrdata.ID
2. Make a reference partitioning llattrblobdata.ID field

Find more posts tagged with

Comments

David Templeton

Roman

First off, we have been looking into better management of these tables and will take the options you have presented under advice

Can you get a couple of numbers from the DBA please. First the total number of rows in LLAttrData and DTree and second the following SQL result

selectcount(*) from LLAttrData where ValDate is null and ValIntis null and ValReal is nulland ValStr is null

As for the options you mention below, such changes while seemingly fine from the DBA point is not currently supported and unless significant testing is done and very likely lead to issues with the application. The first of which is any schema upgrades that are performed. You didn’t not mention the version & update this is applying to.

David

John Simon

David,

Another data point for you...

dtree - 32,684,432
llattrdata - 341,467,130
query results - 58,721,564

version - 10.0.0.33 (update 11)

Cheers...

Appu_Nair1

David,

A similar sized company similar to John's

dtree - 50,847,785 (you don't get deleted with dtree does that matter ? )
llattrdata - 833,884,401
query results - 130,213,656

version - Content Server 10 Update 11 (version 10.0.0.2559)

Ciao...Appu

Breemer_Rick

Hi David,

One more:

DTree: 46.420.127
LLAttrData: 379.916.843
Query results: 123.933.729

Version: Content Server 10.5 Update 2016-03 (version 10.5.0.2006) (upgrading to Content Server 16.0.2 (2016-09), build 1136 in March)

Cheers,
Rick

Roman_Osipov

Hi, David!

Get the number of records in the tables are already problematic (too long a request is made). Because llattrdata table size close to 1Tb. Other tables take up less, but too much.

Content Server 10 Update 13 (version 10.0.0.2738)

David Templeton

Quoted Roman Osipov on 01/09/2017 02:00 PM:

Hi, David!

Get the number of records in the tables are already problematic (too long a request is made). Because llattrdata table size close to 1Tb. Other tables take up less, but too much.

Content Server 10 Update 13 (version 10.0.0.2738)

try this for the table sizes

SQL:

SELECT o.name AS 'Name', ddps.row_count AS 'Approx Num Rows' FROM sys.indexes AS i INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID AND i.index_id = ddps.index_id
WHERE o.name in ( 'DTreeCore', 'LLAttrData' ) and i.index_id < 2 AND o.is_ms_shipped = 0 ORDER BY o.NAME

Oracle:

SELECT TABLE_NAME, NUM_ROWS AS APPROX_NUM_ROWS FROM user_tables where TABLE_NAME in ( 'DTREECORE', 'LLATTRDATA');

Just gets the last known rows counts without actually doing the count.

Roman_Osipov

	TABLE_NAME	APPROX_NUM_ROWS
1	DTREECORE	231544228
2	LLATTRDATA	8363945503

David Templeton

Roman

do you have the result of the other query?

Roman_Osipov

Results of the query are not yet available

Roman_Osipov

select count(*) from LLAttrData where ValDate is null and ValInt is null and ValReal is null and ValStr is null

3386453243

David Templeton

Roman

Thank you for the data point

David