Splitting a database table into multiple segments for performance or manageability.

learn more… | top users | synonyms (1)

0
votes
0answers
37 views

pros and cons of partition or not-partition this sql server table that is highly hit?

I have a table that is highly hit on my database. this table has 370,370 records. when I run the following select: select [ImageSize] , the_num = count(*) from [dbo].[ProductImages] group by [...
1
vote
1answer
34 views

Eager spool for update and delete on partitioned table

When I update or delete on partition table it shows eager spool in execution plan. Image for Update query execution plan showing eager spool:- I can’t understand why it is doing eager spool only ...
0
votes
0answers
38 views

Issues with partitioning

Looking for scale out option for my SQL server database. I have a table that has 15 million rows and it adds 10,000 rows every day. From various options available to scale out I opted for partitioning,...
0
votes
0answers
28 views

Postgres cluster data on large rapidly changing table

I have a table in Postgres which will store statistical information on a range of metrics on a number of machines. Each record is fairly simple, with a date, machine ID, metric ID, and a value. Once ...
2
votes
1answer
58 views

Multiple column partitioning

I have a very large table that needs to be partitioned by two columns. One column is an ID and partitioning is simple enough. The second partitioning should be done daily on the date column. I need to ...
2
votes
1answer
42 views

Problem with a partitioning switch script

Working on some dynamic SQL to create a 13 month rolling partitioned table. See here for some background info. The dynamic SQL part is simple enough, and the first partition switches out without a ...
3
votes
2answers
68 views

How to sort the data fetched from horizontal partitioned tables

I have a telco billing software system. In it there are daily logs of users' calls. The logs are horizontally partitioned by date (month). Each partition is stored in a separate database and may be ...
0
votes
1answer
30 views

In Oracle how do I put certain tables or partitions in a different group so the rest of the db can be brought up with OR without them?

I've done this with MS SQL Server in the past where data that is not required for disaster recovery for instance is in a separate file group and file and not cloned to save significant space and ...
1
vote
0answers
67 views

PostgreSQL Partitioned table update trigger

I have a date-partitioned table (inheritance partitioninng just like the one pg_partman does) which is referenced by quite a few tables in my database. To resolve the FKs checking for referenced field ...
0
votes
1answer
32 views

MySQL - Move data between partitions aka re-partition

I have a mysql table whose partitions look as below p2015h1 - Contains data where date < 2015-07-01 (Has data from 2016-06-01. Hence only month worth of data) p2015h2 - Contains data where date &...
0
votes
1answer
46 views

Unable to view partition table in a schema

I am using oracle 11g enterprise edition, I have created a schema as "TESTSCHEMA" and in it i have created a partition table as "sales_test". The query for the table, create table sales_test (...
3
votes
1answer
18 views

Sybase ASE 15.5 : Create a custom function for partitionning

I want to create a partitioned table to store messages. If the message belongs to "an even week number" => go to the partEven partition ! If the message belongs to "an odd week number" => go to the ...
0
votes
1answer
22 views

Partition elimination and date variables

I have a table that is partitioned on a date column, with a default partition. In our deletion process, we drop partitions that are older than the retention date and then delete records that are older ...
1
vote
0answers
21 views

Two Dimensional Partition

We have a new application that we anticipate will grow substantially and we're exploring partitioning now. If we have a database that includes information, for example, across continents, and we want ...
3
votes
2answers
185 views

Partitioning a large table has not improved performance, why?

In SQL Server 2014 I have partitioned one of my large tables weekly and defined a sliding window scenario to switch the oldest week's data to the archive DB and create a new partition for the next ...
1
vote
0answers
16 views

Adding Partitions to cube In SSAS via VS 2013

I am trying to add a new partition to my cube via SSDT 2013. I am using the Wizard and doing all stages that needed. After I click "Finish" nothing happens and I cannot see that a new partition has ...
1
vote
0answers
37 views

MySQL partition on 2 tables one with millions of rows the other with 100's of millions

I have two mostly de-normalized tables in Amazon Aurora (MySQL). The first table had mostly dimensions that are used for selecting the proper data. Examples might be station, program name, operator, ...
1
vote
1answer
57 views

Copying Partitioned table data to another table on different SQL Server

We have a partition table on Production environment with 2338117620 rows and 206 partitions. Now we have to copy only last 2 year data in Test environment. But the problem is that , Import Export ...
3
votes
1answer
74 views

Drop 15 days records in monthly partition

I have created a monthly partition on my table like below: PARTITION BY RANGE (PARTITION_KEY) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) ( PARTITION MayPartition VALUES LESS THAN (TO_DATE('...
1
vote
0answers
42 views

Can a BRIN index be used instead of Table Partitioning in Postgres?

Given an append only table with a GUID and a timestamp (and a bunch of other columns) which can grow by ~50Mio entries / year. I want to keep the number of indices low and just use a normal (B-Tree) ...
0
votes
1answer
92 views

Splitting a large table

My scenario is I look after an old system with a VB6 based front end and a SQL back end (good news, the replacement is being developed but in the meantime I need to keep this up). Now the application ...
0
votes
1answer
26 views

Is there a way to have mysql automatically create partitions as needed?

We have a large table with columns, date, tag, country. We would like to partition the table by YEAR(date), tag, country, but would like to have mysql create the partitions on the fly. i.e. We don't ...
1
vote
2answers
95 views

Index not used on partitioned table

We have a table similar to this one mysql> SHOW CREATE TABLE my_requests\G *************************** 1. row *************************** Table: my_requests Create Table: CREATE TABLE `...
1
vote
2answers
53 views

Primary keys, clustered indexes and partitioning

We store data for financial records, splitting it up over 4 tables. I'm at a bit of a loss as to what's optimal for our setup here, or at least which direction to head in as I'm getting a lot of ...
6
votes
2answers
181 views

Does table size matter?

I have a table like this (with more columns though): CREATE TABLE [dbo].[MyTable]( [SnapKey] [int] NOT NULL, [SnapDt] [smalldatetime] NOT NULL, [Company] [varchar](4) NOT NULL, [...
1
vote
2answers
120 views

Validation of Partitioned table Performance

We are testing partitioning on a table which contains a datetime column (DateColumn). Partition function I am using is: -- 138623 + 16774 records CREATE PARTITION FUNCTION pf_TableName_DateColumn (...
1
vote
1answer
30 views

Partition Wise Joins in Mysql

Is it possible to create or simulate a partition-wise join in Mysql/Innodb?? Here is a good description of partition-wise joins.... https://blogs.oracle.com/datawarehousing/entry/...
0
votes
0answers
29 views

Pg_partman giving problem in partman.check_name_length function

I am using Pg_partman inpostgres to partition table using id. I am using the Partition Per 10 ID Values Starting With Empty Table Example given in the below link https://github.com/keithf4/...
1
vote
1answer
54 views

Rebuilding partitioned table clustered indexes

I need to know how can we rebuild the partition table clustered index with the table size being around 270 GB with 126 Partitions on it. Also, I want to execute it in the production environment, so ...
1
vote
0answers
40 views

How can I partition records vertically across machines?

I recently asked two questions: (1) how to store and connect partial records in two or more machines, with the record halves correctly associated, and (2) how to determine the increase in time/space ...
0
votes
1answer
52 views

SQL Server 2014 Transactional Replication Partition Switching

I am working with SQL Server 2014 Transactional Replication with tables that have partitions. The source tables are partition switched when they are loaded and so far I have been able to replicate ...
0
votes
1answer
36 views

mysql partition enabled or not?

When I run show variables like 'have_partitioning'; it return empty record. When I run show plugins I got this result partition | ACTIVE | STORAGE ENGINE|NULL | GPL| My MySQL version is 5.6.29 and ...
1
vote
0answers
47 views

indexes and partition switching - how it works

We use partition switches to quickly load data, switching entire tables. We load an empty Table A with INSERTs and then switch it with empty Table B. These tables have identical indexes. What I don'...
1
vote
1answer
85 views

How to add Subpartition to all partitions

I believe adding sub-partition in Oracle 11g is easy ALTER TABLE PART_TEST modify partition OCT19 add subpartition OCT19AXCS values ('AXCS'); I have close to 250 partitions..namely jan07 feb07 ... ...
2
votes
1answer
30 views

How do I add values to partition function and scheme?

We have a partition function defined as CREATE PARTITION FUNCTION [PartitionByPeriodFunction](int) AS RANGE LEFT FOR VALUES (20101228, ... , 20161227) where each date is the first date of our ...
1
vote
2answers
94 views

Error Updating Record on Table with Partition on a Read-Only Filegroup

We are working to partition our data warehouse (SQL Server 2012 Enterprise): moving old records we don't expect to change to a read-only filegroup on a monthly basis. We're getting errors that records ...
3
votes
2answers
162 views

SQL Error: ORA-14300: While partitioning and subpartitioning

I have tried this earlier and it works: I have T1 which has no DATE or STATE which needs to be partitioned on DATE and sub-partitioned on STATE Hence for the ID present in T1, fetch the DATE and ...
4
votes
1answer
69 views

Incremental statistics: find out which partitions have been resampled

TL;DR: Is it possible to find out which partition(s) have been resampled and which have not when incremental statistics are used? Platform is SQL Server 2014 Enterprise edition. The long version with ...
-1
votes
1answer
49 views

Alter - Subpartition a partitioned table

I have a table which is partitioned by RANGE on DATE key CREATE TABLE T1 DOC_ID VARCHAR NOT NULL. STATE VARCHAR ... DOC_DATE ... PARTITION BY RANGE (DOC_DATE)( PARTITION feb16 VALUES LESS ...
0
votes
1answer
189 views

How to change initial extent of an existing partition

I'm having trouble with something that should be rather simple ... I'm sure I'm missing something obvious. I'm trying to change the initial extent of an existing table partition. For some reason, I ...
0
votes
0answers
15 views

Parent Table not returning how much row has been affected

I have on table test and i have participate in multiple table testa,testb.......testz. I have inserted triggered before insert for data is gone in child tables. When i insert in data in parent table,...
0
votes
1answer
84 views

Very large subpartitions after table redefinition

I'm trying to redefine a table to be partitioned. The original table has about 1.5M rows and about 150MB size. select segment_type, segment_name, bytes / 1014 / 1024 as SIZE_MB from user_segments ...
3
votes
0answers
90 views

Best design to reference multiple tables from single column?

Proposed schema First and foremost, here is an example of my proposed schema to reference throughout my post: Clothes ---------- ClothesID (PK) INT NOT NULL Name VARCHAR(50) NOT NULL Color VARCHAR(...
0
votes
0answers
27 views

Query slow on 5 Mil fact table

I have a fact table with currently 5 mil rec. The record count will go up as ETL runs on daily basis. The below reporting dashboard query is taking 1.50 sec to execute. There are many number of ...
0
votes
0answers
64 views

oracle : Effect of inserting new rows when row movement is enabled

I have a table which is partitioned by key. I want to move few records (which I require later) to another partition so that I can drop this partition and get some free space. I am enabling row ...
3
votes
1answer
205 views

How many rows can a SQL Server 2012 table hold, and when should I partition it?

Say a table stores granular data about some event. It has the date of the event, a type dimension with around 30K types, and a category dimension with around 100 categories, plus some numeric facts. ...
3
votes
1answer
43 views

High availability on a subset of the data

On my site, I have a set of big databases (+6TB total) that is running on a SQL Server 2012 instance. One of these is the main DB and the other ones are archived data that are queried sometimes (...
0
votes
2answers
368 views

How do I efficiently partition a large MySQL (Aurora) table with multiple search columns?

I have a large and rapidly growing table. It takes in about 5k rows / second and that will be doubling in about a month. The table is Amazon's Aurora (heavily modified InnoDB.) As of today, total ...
1
vote
1answer
112 views

Slower running Query with better Filter?

mysql> SELECT * FROM history_uint h WHERE h.itemid='1825689' ORDER BY h.clock DESC LIMIT 2 OFFSET 0 ; Empty set (0.00 sec) Explain: id: 1 ...
1
vote
1answer
91 views

DBMS_REDEFINITION with WHERE clause

I wanted to know if DBMA_REDEFINITON package allows a WHERE clause to filter contents before migration. I have a partitioned table and wants to copy data and constraint to another table using ...