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

learn more… | top users | synonyms (1)

1
vote
1answer
72 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
44 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
172 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
108 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
25 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.... ...
0
votes
0answers
16 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 ...
1
vote
1answer
48 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
40 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
25 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
44 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 ...
1
vote
1answer
66 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
78 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
126 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
62 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
48 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
65 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
14 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 ...
0
votes
1answer
51 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
87 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 ...
0
votes
0answers
26 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
48 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
159 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
36 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
199 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
96 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
72 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
24 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
96 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
38 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 ...
1
vote
0answers
32 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
164 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
71 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
51 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
145 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. ...
1
vote
1answer
44 views

Restore from SQL Server 2014 Enterprise to BI

Is it possible to restore a SQL Server 2014 Enterprise database with table partitioning (Production) to a SQL Server 2014 BI Version (Test/Development) ? Of course I don't expect to use the table ...
1
vote
0answers
21 views

In Postgres, is it possible for me to have a table partitioning constraint based on data type?

Say I have the following schema for my Postgres database: CREATE TABLE sources ( source TEXT NOT NULL, PRIMARY KEY (source) ); CREATE TABLE ipaddrs ( addr INET ) INHERITS (source): CREATE ...
0
votes
0answers
17 views

The most-recent-record problem with partitioned tables

Today's variant on the most-recent-record problem involves a table partitioned on the timestamp field relevant to the most-recent-record selection, and efficiently joining the result of the ...
0
votes
0answers
46 views

Why dropping TokuDB partition so slow

I have defined a simple time series table partitioned by UNIX timestamp (into 32 days). I then loaded ~17K entries, which were distributed among 8 partitions. There were at most 2880 rows in any ...
0
votes
0answers
25 views

Mysql partition - How to do list partitioning of a table that contains unique column?

I am doing mysql list partitioning. my table data is as below ---------------------------------------- id | unique_token | city | student_name | ---------------------------------------- 1 | xyz ...
3
votes
2answers
48 views

Dealing with partitioned tables in Postgresql

Due to historical reasons we used to use one really big table in our Postgresql database to store time-based series of measurements. After a long usage, as the table become 200+ GB in size, we decided ...
1
vote
0answers
56 views

PostgreSQL doesn't use index on inherited tables

I have two tables in PostgreSQL 9.4.5 deployed on Amazon RDS: an append only, weekly partitioned, All Positions table. These store all position readings from about 100,000 sensors. There are about ...
0
votes
0answers
47 views

Oracle - compressed subpartitions in uncompressed table

I am working with Oracle 11.2. I have table which on subpartition level has in DBA_Tab_subPartitions column COMPRESSION set to ENABLED for all subpartitions. But on partition level in ...
1
vote
1answer
126 views

How can I add multiple partitions to a Greenplum table using dynamic SQL

I have a function that adds a partition to a table, that works just fine. It takes a schema name, a table name, and a date, and generates and executes an ALTER TABLE statement to add a partition for ...
2
votes
0answers
34 views

Is there impact on query execution as more partitions are added to a table?

We are looking to heavily use partitioned tables in our data warehouse environment. We are looking to take advantage of fast partition switching as well as partition elimination during analytical ...
19
votes
2answers
295 views

Is it possible to force the optimizer to eliminate irrelevant tables in this partitioned view?

I'm testing different architectures for large tables and one suggestion that I've seen is to use a partitioned view, whereby a large table is broken into a series of smaller, "partitioned" tables. 1, ...
8
votes
1answer
224 views

To partition or not to partition?

Having already read several questions on SO, external blog posts and manual SO: Foreign Key constraint to partitioned table in Pg dba.SE: Different ways of handling FK to partitioned table in Pg ...
2
votes
1answer
63 views

Split Mysql Table internally

How to split the huge mysql table (size around 200GB) internally? Is there any mechanism other than partition? The table size is daily growing. Table created using innodb with indices. Thanks in ...