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

learn more… | top users | synonyms (1)

2
votes
1answer
415 views

InnoDB, Hash Table, Reference Table and Fragmentation

I have a big list of domains and URLs that I store in a database, there are around 150M domains and 300M URLs, I'm using InnoDB to store each in the format: CREATE TABLE IF NOT EXISTS `domains_list` ...
1
vote
1answer
179 views

Monitor Oracle query plans for PARTITION LIST (ALL)

Is there any way I can monitor all the queries running on an Oracle instance and report which queries are using PARTITION LIST (ALL)? What I'm essentially asking for is a way to automatically ...
2
votes
1answer
186 views

How do I know if my database partitioning is done well? [closed]

I have a sneaking suspicion that the guy who wrote the partitioning functions at my place of work did a fairly poor job. For certain queries (maybe 20% of them) we see incredible performance boosts ...
1
vote
1answer
946 views

Partitioning vs Indexes

I'm very new to databases, so I apologize if my question is very basic... Anyways, I'm creating a table with what seems like alot of data (500 million rows right now, and potentially twice as much in ...
3
votes
1answer
240 views

MySQL partitioned tables?

I have a database that supports a web application with several large tables. I'm wondering if partitioned tables will help speed up certain queries. Each of these tables has a colum called ...
3
votes
1answer
703 views

Will table partitioning speed up my queries?

My database is set up with what we call our staging tables and our live tables. Currently for we have about 2 million rows for a table called products. Our customers we call manufacturers load their ...
1
vote
0answers
17 views

How to take partitions or filegroups out of service [migration] [duplicate]

I'm an experienced SQL Server DBA but new to partitioning, and a had a couple of questions. Using SQL Server 2008 R2 Enterprise Edition. I've inherited a large customer metrics database that grows by ...
1
vote
3answers
3k views

How do I remove partitioning?

There is a table my_table which has not been created any partition in MySQL 5.6. And I'm trying to create partitions by procedure in my_table, when I run this code: alter table my_table remove ...
2
votes
1answer
726 views

How to partition a very large table with limited db space?

I have a very big table. I want to partition it, but I cannot. Database size: 1TB, Free space 200GB Table: Size: 165 columns (row lengh 4216 KB, no LOBs), 500 million rows, 600GB of data. ...
1
vote
1answer
226 views

Can a partitioned table span on multiple files across different file groups?

In SQL Server 2008 and above, if a table is partitioned, can it span across multiple files across multiple filegroups? Thanks
2
votes
1answer
426 views

Does SQL Server have a reference partitioning equivalent?

Oracle 11g has introduced a new feature that is called Reference Partition. Using reference partitioning, a child table can inherit the partitioning characteristics from a parent table. ...
0
votes
1answer
127 views

Is there an advantage to moving to 'real' date partitions on MySQL 5.5?

We are using partitions per day for some our tables. We started with MySQL 5.1, so we had to partition using the to_days (end_time is a datetime column): ENGINE=InnoDB DEFAULT CHARSET=utf8 ...
2
votes
1answer
214 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 ...
0
votes
0answers
92 views

Find the partition where data lives in MySQL

Is there a way to select the partition name where a specified record actually lives in? The idea is something like: SELECT <PARTITION_NAME> FROM mytable WHERE <where_condition> I found ...
2
votes
0answers
95 views

MySQL Partitioning Performance

I have a MyISAM table with billions of rows that is still causing me problems because the indexes, even after I shortened them as much as possible, do not fit in my 64GB of RAM. I am unable to index ...
4
votes
2answers
2k views

Partitioning and freeing disk space

I am running MySql innodb with innodb_file_per_table. As a daily script, I would like to create a new partition for a set of tables, as well as delete yesterday's partitions. What command would need ...
0
votes
2answers
480 views

Sync Indexes between two tables

I need to automate and Sync only indexes between two tables ( Primary and Stage_Table) within the same database. I tried using SSIS SQL Server Objects Task, but looks like it works only when we sync ...
1
vote
1answer
128 views

Key lookup partition pruning

I have a query inner joining multiple tables all of which are partitioned and have 10 partitions each: SELECT A.COL1, B.COL2, C.COL3 FROM A INNER JOIN B ON A.ID = B.ID INNER JOIN C ON A.ID = C.ID ...
1
vote
3answers
459 views

Performance issues moving 1 million rows between tables

I want to replicate 1 million rows from table1 to table2. I have written a job with a query like this: delete from table1 OUTPUT *.delete into table2 I am executing this job every hour, but it ...
1
vote
0answers
229 views

When to split a large table

I run the SQL Anywhere DBMS, and we have a table of about 10 columns(normal text, timestamps etc). However, in my use-case, every single day, the table gets 2160000 new rows. It's also crucial that ...
4
votes
1answer
196 views

Table partitioning filegroup

Can I use the same filegroup for multiple tables when partitioning a table? I partitioned a items table by year. It created five filegroups. Can I use the same file group for orders table or will I ...
1
vote
3answers
367 views

Improve performance by partitioning

I'm using SQL Server 2012. I’m using CTE to create an orders table from the items table. The final count for the orders table is around 120 million. It is taking about 2 hours for the whole process. ...
2
votes
3answers
7k views

Avoiding “Waiting for table metadata lock” when `ALTER TABLE DROP PARTITION`?

I have some tables that many users need to access to: mysql> show create table v3_cam_date\G *************************** 1. row *************************** Table: v3_cam_date Create Table: ...
5
votes
2answers
779 views

Recreate Indexes on 1 billion record table

I have a table with over 1 billion records and it has 6 indexes (including Clustered index (ID)). I need to partition this table on a new Clustered index with date column. I have just enough space ...
1
vote
0answers
98 views

MySQL Partitions

I've done a lot of work with MySQL but generally on a much smaller scale than this, so please excuse my ignorance. We've got an InnoDB table which is currently storing ~3.5 million rows, which I know ...
1
vote
1answer
2k views

Does 'update global indexes' not work if the index is already unusable?

I have a partitioned table in Oracle 11g with a PK (global index). If I truncate one of the partitions with ALTER TABLE tbl1 TRUNCATE PARTITION p1; the global index has the status UNUSABLE. If I ...
2
votes
1answer
3k views

When is data moved during an Oracle partition split?

I need to split an Oracle partition, and I'm confused about whether or not Oracle is going to physically relocate the data when I execute the split. Here's what I intend to do: alter table ...
6
votes
2answers
1k views

Making the Oracle optimizer use a virtual column to find out about a partition

I have a large table that has three columns like this: "START_DATE" DATE, "START_VALUE" NUMBER(10,7) "START_DATE_VALUE" NUMBER(18,7) GENERATED ALWAYS AS ( (extract(YEAR FROM START_DATE) * ...
1
vote
0answers
229 views

How to make Greenplum 4.2.3 only scan the intended partition?

When I uses unnest() in a View, and uses that View in a select statement, Greenplum seems to fail in only searching for the intended partition and search through all the partition of the main table ...
1
vote
1answer
198 views

Is there a way to write intelligent queries against a merge table in MySQL that represents a bunch of tables partitioned by date?

I have a set of MySQL tables that are partitioned by date, with a merge table representing all of them. For the query I am writing, the results are joined by date, so rows in table A_2012-12-05 will ...
3
votes
2answers
2k views

Partitioning on a single filegroup

I have some very large tables in my database, but a substantial chunk of this data is "old". Due to circumstances beyond my control, I am not allowed to remove this "old" data. The other ...
0
votes
1answer
403 views

Partitioning in SQL Server 2008

I want to partition some of our tables in a SQL Server 2008 database. The partition key will be date based. I have several questions: Is there a rule of thumb regarding the recommended number of ...
1
vote
2answers
2k views

MySql - partition by range and unique key

i'm trying to create the following table: CREATE TABLE `s_relations_with_partition` ( `id` int(11) NOT NULL AUTO_INCREMENT, `source_persona_id` int(11) NOT NULL, `relation_type` int(11) NOT ...
1
vote
1answer
4k views

conversion of high_value to date datatype

I need to comparte the high_value which is of LONG datatype to sysdate which is of DATE datatype. I need to fetch the partition names of all the partitions which is 90 days old. The code is some what ...
0
votes
1answer
345 views

horizontal partitioning or multiple tables?

I'm developing an application and need to propose a database schema. We plan for a MySQL database with millions of records which should be distributed over multiple machines for performance and ...
0
votes
2answers
209 views

MySQL Cluster - partitions on disk

I have question about MySQL Cluster: Is it possible to have database larger than amount of RAM? (to not have all cluster data in-memory, but some data will reside on clusters nodes hard disks) ...
0
votes
1answer
659 views

Postgres 9.1 query plan not using check_constraints on partitions after large data load

I've created a table in Postgres 9.1 called 'markets' that contains data about a market in a specific US zip code. I've partitioned the data on the zip code column, using the first digit of the zip ...
3
votes
1answer
2k views

Setting up a Postgresql Partition with foreign key

I have a Ubuntu 12.04 Server with PostgreSQL 9.1 installed. I am trying to setup Partitioning on a existing Postgres DB(say 'xyz') which has very huge tables and some have foreign keys set on them. ...
0
votes
0answers
145 views

XML errors while copying dependents during a redefinition to an interval partitioning scheme

I am partitioning an existing table. I used the following process: Checks DBMS_REDEFINITION.CAN_REDEF_TABLE(...); Successful Create duplicate table CREATE TABLE "USER"."MYTABLE_ONLINE" ( ...
5
votes
2answers
1k views

Get a record's current partition

In SQL Server 2008 R2, how would I determine what partition a record currently lives in?
2
votes
2answers
1k views

Best option to back up mysql innodb partition

We have a number of tables which we plan to partition based on months. The issue now before we can drop those partition we would like to perform backup. One option we found is this link ...
0
votes
2answers
255 views

Best Way to Partition By City / State in MySQL 5.5

I have a table with ~20M rows and every query against it will include state or city or a combination so I was thinking of partitioning the table using a combo of the two fields like so: ALTER TABLE ...
3
votes
3answers
2k views

Is it a good practice to apply Vertical Partition to a newly created model?

Suppose you are modeling the ER model for a new Object Oriented Web Application. There is a table with 20 columns, and there are going to be a lot of INSERTs populating 10 columns, and a lot of ...
0
votes
1answer
110 views

Multitable partition guide

We have a main table called tblLink in it will have a date field.The tblLink will have its primary key as foreign in few other tables too and one of the table will tblMainData which over time will be ...
0
votes
1answer
197 views

Where is my old partition in mysql show create table?

I am keep adding daily base partition for a table as e.g. ALTER TABLE tblData PARTITION BY RANGE (to_days(`dataInsertDateTime`)) ( PARTITION p20 VALUES LESS THAN (to_days('2012-12-25')), PARTITION ...
2
votes
0answers
345 views

Dynamic MySQL partitioning based on UnixTime

My DB design includes multiple MYISAM tables with measurements collected online, Each row record contains auto-incremented id, some data and an integer representing unixtime. I am designing an aging ...
1
vote
0answers
165 views

MySQL Partitioning, Reorganize Max Value partition (Known Bug / Feature)

I have written a crone job which reorganizes last max value partition every week into 2 new partitions , one for the previous week , and one for the future data (new max value partition which ...
2
votes
1answer
7k views

After truncating a single partition its Primary key's index becomes unusable and all inserts/updates into that partition fail

I have a partitioned table: SAMPLE_PARTITIONED_TBL with 60 partitions (no sub-paritions) based on the PERIOD_ID numeric field (Data set: 201001...201212.. and so on). This table has several local ...
5
votes
1answer
555 views

Expanding a partitioned table

I have a table that holds four years of data, where for each month I have a partition that is based on a filegroup with two files. Unexpectedly, I received eight more years of data and prepared the ...
1
vote
2answers
2k views

Best way to organize compound indexes on partitioned table (date by range)?

I have a table with an auto increment primary key, but in order to use the partition feature in MySQL I had to include the date in the primary key so I would use partition by range using the date ...