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

learn more… | top users | synonyms (1)

0
votes
0answers
7 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
39 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
16 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
0answers
12 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
18 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
173 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
14 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
31 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
35 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
73 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
31 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
88 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
19 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
88 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
47 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
175 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
118 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
29 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
22 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
50 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
47 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
28 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
45 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
80 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
28 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
84 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
141 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
68 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
117 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
73 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
88 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
55 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
178 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
37 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
264 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
104 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
81 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 ...
0
votes
0answers
30 views

How to Efficiently Handle Queries On a Table Subset

I have a MySQL database where one table in the database contains a datetime field and the other fields contain numeric data. One application frequently queries the server using a WHERE clause on one ...
1
vote
1answer
98 views

SQL Server Partition

I am trying to create partitions on a table of one of my test databases called WingTipToys2012. Following the recomendations of many sources, I created the three file groups called FgOne, FgTwo and ...
0
votes
0answers
46 views

How to drop partitioned index in SQL Server 2005

I am unable to drop partitioned index due to partition scheme and partition function existence. Table has a Primary Key, a Clustered Index and non clustered indexes. I am running the following query:...
1
vote
0answers
33 views

Table clustered-index and partitioning in innoDB

I have a table with 45 columns, 3 of which are relevant: table Transactions(tr_id : int primary key, client : int, tr_date : datetime); The inserts to the table are done client-by-client The ...
5
votes
2answers
176 views

Total billions of rows and daily millions of row insert: What database system suits best [closed]

We developed a system with very high volume of data but very simplified data structure. We had only cellX, cellY, timeStamp and value columns. Operations are: Insert 455+ thousand rows per day ...
0
votes
1answer
34 views

Partitioned view with dedicated “routing” column

I have an application (Microsoft Dynamics NAV to be exact) that splits up data for different companies in tables with the exact same structure. I can not change that behavior, so I built VIEWs that ...
1
vote
1answer
72 views

Partitioning a table on a column in another table

I have a fact table called FactAccount which has the following columns: AccountId (identity) AccountMonthId - FK referring to DimMonth AccountYear The DBA has done the partitioning on the ...
0
votes
2answers
55 views

MySQL : How do I partition based on Month of date

I have a table having id as primary key and date column. I want to partition the data.. I felt month would be the best option so that I have 12 partitions.. How do I achieve that ! I am using mysql ...
1
vote
1answer
193 views

Drop partition and rebuild global indexes “online”

I am using Oracle 11g. I have a requirement to drop a partition and rebuild global indexes. The query below does job well but BLOCKS all DML operations on the table until the indexes are rebuilt. ...