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

learn more… | top users | synonyms (1)

4
votes
1answer
38 views

Truncate parent table but not children

I have a table in PostgreSQL 9.4 that I partitioned so that all inserts to the parent actually go into one of the child partitions. The problem is that I already have hundreds of millions of rows of ...
3
votes
4answers
536 views

Is database partitioning for performance reasons meaningful in a virtualized environment?

When running SQL server on physical hardware, placing database data and log files on separate disks or RAID arrays can benefit performance, resilience, and maintenance. Partitioning the database over ...
11
votes
1answer
6k views

Index optimization with dates

I have a large table of objects (15M+ row) in PostgreSQL 9.0.8, for which I want to query for outdated field. I want to divide the query by millions, for scalability & concurrency purposes, and I ...
2
votes
0answers
24 views

Why my database mirror break down after changing file group settings from RESTRICTED_USER to MULTI_USER?

My environment is the following: VMWare 5.5 vitalized server MS Windows Server 2008R2 Enterprise domain and SQL Server 2008 R2 Enterprise. Centralized storage with fibre-channel connection. I have ...
1
vote
1answer
45 views

Dynamic Partition Elimination when using “OR ” or “IN”

Say I have a query like this: SET @ContainerId = 5 SELECT * FROM Container.Container_Partitioned WHERE containerId = @ContainerId When I run this, I get a query plan with Actual Partition ...
-1
votes
0answers
28 views

Exchange Partition

I want to monthly move partitions of my current table into archive. For example, I have two tables with primary key constraint: create table tableSource( id number, create_date timestamp ...
0
votes
0answers
31 views

SQL Server 2012/SSIS - How to Import Partitioned Table Data to Reporting DB?

What's a recommended SSIS process flow for importing a daily partitioned table to a reporting DB table (SQL Server 2012)? Will be switching partitions on main DB to a staging table each day and ...
3
votes
1answer
22 views

SQL Server 2012 - How to Remove Partition on Publisher but not Subscriber?

Is it possible to setup partitioning on a SQL Server 2012 publisher table, so that older data can be removed without affecting the same table on a subscriber table? Table exists on Database A (SQL ...
5
votes
0answers
37 views

Error in partition switching in Sql Server 2014 when migrating a database from Sql Server 2005

I am migrating an old database from SQL Server 2005 to SQL Server 2014. This database has a partitioned table; a scheduled job periodically moves old data from the "online" table to the the historical ...
0
votes
1answer
26 views

Sql Server thinks I have different Data Compression when swaping partitions

I am setting partition switching for my database. I created a script using SqlCmd variable so that the same script would make both my source and target tables for partition swapping. Here is an ...
0
votes
1answer
28 views

Table partition in aws MYSQL RDS master slave setup

we are looking to do a table partition in MYSQL RDS running 5.6, table size is large and takes around 6 hours to partition. Can we partition on rds slave and then later promote it to master. is this ...
0
votes
1answer
41 views

Table partitioning in MYSQL Master Slave

I have to Partition a MYSQL table using Range partitioning. Table is 200M rows big and I have Master/Slave replication. What I want to do is to stop replication on slave, partition the table on ...
2
votes
1answer
34 views

Difference between clustered index and partitioning column

As I understand partitioning, the column that you partition on is how things are divided into partitions. But does the partitioning column have anything to do with order on the disk? (Aside from ...
0
votes
1answer
123 views

Add partition works but not the drop partition

We have set an event as below. What we notice is that the add partition is working well as we can see on a daily basis the partition list is growing but the drop partition is not working well any ...
1
vote
2answers
41 views

Choosing what table fields to partition on

I'm having a tough time deciding whether or not to partition my table based on a unix timestamp or by a descriptor attribute. It's just over a billion rows currently. My table basically contains: ...
0
votes
1answer
394 views

Oracle 11g r2: Cannot create partition on timestamp field

I have data with a timestamp field of the form '2014-01-01 00:00:00.99' in one of the columns. I want to create a new partition for the table every month. For this I did something like this. CREATE ...
0
votes
2answers
32 views

Partitioning visitor logs database

I have set up a table that logs every page view a visitor makes. This is going to result in a table with about 50,000 entries per week. To keep things manageable and performing well I am intending on ...
0
votes
1answer
24 views

Working on a blog creation website; should I be creating a new table for each blog, or keep everything on the same table?

So I'm working on this blog creation website as a side project. I don't actually expect it to become anything, but I want to make it as efficient as possible, even if just for practice. The idea is ...
2
votes
2answers
49 views

Why Does Partition Range Right Skip Deletes/Inserts?

I have two example Queries (shown below). Both (drop and) create a database and a partitioned table in it. The table has several partitions, with data in each of them (including the last, unbounded, ...
1
vote
1answer
126 views

Is there any way to use “like” operator during oracle table partitioning

I am using Oracle 11g database and I have 45 million records. I want to partition the table, but if it's possible for partitioning to support the like operator then it will be much helpful for me. In ...
0
votes
1answer
37 views

Inefficient queries on partitioned tables

Consider a scenario in SQL Server that includes a partitioned table with over 500 partitions. The number of partitioning column values in a temp table are less than 10. We join the temp table to the ...
0
votes
1answer
58 views

Postgres Partition Table Trigger inserting duplicate records

I have a trigger applied on my database table. But when i insert data into it via hibernate jpa it creates duplicate rows in master table. Here is the trigger CREATE OR REPLACE FUNCTION ...
2
votes
1answer
119 views

Shrinking old partitions

I have a large partitioned time series table where each partition is stored in it's own file. Since it's time series based partitioning divided by week and I'm only storing data that is new I have ...
3
votes
1answer
56 views

How to maintain a global index on a large table undergoing regular partition purges?

Problem I've got giant table haystack weighing in at nearly 3 TB, partitioned by month (date) and subpartitioned by client. I need to create a global index that spans all clients to facilitate ...
0
votes
2answers
60 views

sql server transactional - replication - the internal procedures often deadlock with user processes

Very often I have deadlocks on my subscription server, involving the replication internal stored procedures and user processes either ad hoc or procedure queries. For example: Deadlock of the ...
2
votes
2answers
8k views

Adding subpartitions to existing partitioned table

In Oracle 11g, is there a way to introduce subpartitions into an existing partitioned table? I cannot seem to find a combination of EXCHANGE and SPLIT partition that does the right thing. SPLIT will ...
3
votes
1answer
76 views

Why can an Oracle SET UNUSED COLUMN cause internal UPDATE SET X=X and huge log generation?

On Oracle 11.2.0.4.1, we tried to run a simple ALTER TABLE PARTITIONED_LARGE SET UNUSED COLUMN XXXXX; on a large ref-partitioned child table to decommission a column. The table has hundreds of ...
4
votes
0answers
35 views

SQL Server 2008 R2 Partitioning - same FileGroup, 1 File, 2 partition_numbers - HELP

It's my first go at partitioning in SQL Server, I learned from the Brent Ozar guide which is fantastic :) A few times I have ran into a weird scenario; when I run: SELECT * FROM ph.FileGroupDetail ...
8
votes
2answers
118 views

Getting a realistic query plan when partitioning with low volumes of data

We're using partitioning to reduce the amount of blocking our OLTP system experiences due to locks, with a partition scheme that splits the working tables into 100 partitions based on a customer id. ...
2
votes
1answer
248 views

Unique Non Clustered Column in Partitioned table

We have partitioned one of our SQL Server tables on a Clustered Index datetime column, we have a Primary key Non-clustered index column with Identity data type and this column is setup as a ...
2
votes
1answer
32 views

remove a paritition table

I need to remove a partition table for an application upgrade. It is SQL Server 2008 R2. The partition column is on column col1 data type smallint, null. Col1 has length 10 and precision 5. As you ...
0
votes
2answers
49 views

Partition Function Question - DatetimeOffset

I am planning to partition a large table on [datetimeoffset]. I thought the data in that column were all yyyy-mm-dd hh:mm:ss -08:00, but there are multiple offsets in that column such as -07:00 and ...
0
votes
1answer
62 views

postgres fetch record from partitions without the check constraints

I am using postgres 9.4, I have a messages table that has 100M records, messages belongs to one feed_id, so I decided to partition the tables by posted_at. Table "public.messages" ...
0
votes
2answers
39 views

Table partition should not be selected

I am looking to partition a table (nearly 30M rows) by date ranges (mysql version : 5.1.73) CREATE TABLE `TABLE_X` ( `name_x` varchar(50) NOT NULL, `type_x` varchar(5) NOT NULL, `status_x` ...
0
votes
1answer
739 views

Are there any disadvantages to partitioning on financial year?

Our current set up has one table per financial year (May 1- April 30). Each table has approx 1.5 million rows. We have about 8 years of data, and will obviously be adding each year. The majority of ...
1
vote
2answers
34 views

How to define partition according to Date?

I am currently using MySQL 5.6. In my database, I have a log table called paramlog_ems. It has 2M rows I want like to partition this log table according to Date. Here is the table structure: ...
1
vote
2answers
112 views

Many columns vs few tables - performance wise

Yes, I am aware that data normalization should be my priority (as it is). I've got a table with 65 columns storing vehicle data with columns: used_vehicle, color, doors, mileage, price and so forth, ...
2
votes
3answers
50 views

SQL Server uses only PRIMARY partition by default

My database has 2 partitions files: PRIMARY, DATABASE_ARCHIVED. A lot of tables have a column "archived". When archived is 1 row is in the DATABASE_ARCHIVED partition. When archived is 0 row is in the ...
0
votes
1answer
124 views

How to split a MySQL table file?

I have a table in a MySQL database for which innodb_file_per_table is enabled. One of my tables is about 20GB and growing. How can I partition the table into separate files? Suppose that I have a ...
1
vote
2answers
505 views

Snapshotting Database Tables

I have a reporting database (in SQL Server) that takes nightly snapshots of the production data (Oracle) for users to query against for the next day, but now the requirement is getting to be that they ...
4
votes
3answers
1k views

Slow queries on billions-rows-table // index used

Since I'm a young developer and not really skilled in using databases (PostgreSQL 9.3) I ran into some problems with a project, where I really need help with. My project is about collecting data of ...
0
votes
1answer
33 views

Does it makes sense to partition on a foreign key?

I have a MySQL table which looks a bit like this: ID (BIGINT, PK), OtherID (INT, FK, indexed), NegativeTimestamp (BIGINT, indexed) Selects should go very fast, updates almost never happens, inserts ...
8
votes
1answer
425 views

Storing and querying rolling data in PostgreSQL

I have a large quantity of weather model data being put into a PostgreSQL database. The machine has 8 cores and 16 GB of RAM. I'm running PostgreSQL 9.3 with PostGIS 2.1. Each table will have a ...
0
votes
1answer
258 views

How to split a large table in Teradata

I'm trying to load data from a very large table (200 million rows) into a Presentation Datamart (PDM) for an Enterprise Data Warehouse using Teradata 14.10. I want to split this large table into ...
7
votes
1answer
414 views

Switching Data In Fails with “allows values that are not allowed by check constraints or partition function on target table”

Given the following -- table ddl create table dbo.f_word( sentence_id int NULL, sentence_word_id int NULL, word_id int NULL, lemma_id int NULL, source_id int NULL, ...
2
votes
2answers
52 views

Benefits of table partitioning on same disk

Maybe is stupid question, but I must ask to be clear with that. I know that table partitioning is powerful mechanism of speeding data search when partitions is placed on different disks. What I need ...
1
vote
2answers
138 views

Compressing partition - what about index?

To save space on disk I want to compress some partitions of my tables: alter table TEST_TAB move partition TEST_TAB_2014 compress for all operations; I have also indexes on this partition. Should I ...
0
votes
0answers
10 views

Delete datafile from parttioned table

I'm used SQL Server 2014. My big table partitioned by date column and each partition has own datafile. I want delete oldest partition data and data file too. How to do it?
0
votes
0answers
32 views

Is there a workaround for the lack of foreign keys constraint to a partitioned table in PostgreSQL?

I have a big table A (~30 million lines) at the core of my model. Another table B has a foreign key referencing A. I would like to partition A using the well-known inheritance hack. Is there a ...
0
votes
0answers
18 views

Moving Replicated & Partitioned SQL2005 Enterprise to SQL 2008R2 Enterprise

Our SQL2005 Enterprise has 4 databases which are all partitioned, each have 5 files and a handful of tables replicated, compatibility mode is 90. They are on an old Windows Server 2003 32bit machine ...