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

learn more… | top users | synonyms (1)

0
votes
0answers
52 views

Recreate partition in mysql

I have a table having datetime column partitioned by RANGE (DAYOFYEAR(datetime_col)) By this I want to drop oldest of 4th week partition data and recreate immediately for next year similar week ...
0
votes
1answer
284 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
0answers
43 views

Oracle estimate cardinality on partitioned table

How does Oracle estimate cardinality on a partitioned table if the query gets the list of partitions from a subquery or a join? For example, if I have a query like this select * from ...
0
votes
0answers
44 views

SQL Server partitioning of many-to-many tables - triggers

Many to many tables usually have only foreign keys to related tables, like EmployeeId PositionId Is it good idea to add column only for partitioning and set its value with INSTEAD OF INSERT/UPDATE ...
0
votes
0answers
112 views

Performance degradation after partioning tables using DBMS_REDEFINITION package

We have created partitions on per-existing tables in my application using DBMS_REDEFINITION package. just for the sake of improving performance. Now , We are facing an issue in the performance ...
0
votes
0answers
22 views

How to figure out how to split a table using the proper scheme

If you have a big table that you want to partition and you have 2 candidates as the partitioning key (in the sense that 50% of the queries use one column and 50% of the queries use the other column ...
0
votes
0answers
41 views

Adding Partitions to an Existing Filegroup

For maintenance reasons, the organization that controls the servers where I work has decided to limit the number of file groups and files for partitioning to just 1 of each. Their reason for this is ...
0
votes
0answers
74 views

Is it best practice to include partition keys within foreign keys when referencing partitioned tables?

The large tables in our database are partitioned by an id in order to separate data and speed up IO. The table of the data is partitioned by the partition key, but the primary key is only on an ...
0
votes
0answers
59 views

How do triggers work on partitioned tables?

I have partitioned a table on the basis of a column. This column can either have 0 or 1. So, only two partitions (p0, p1) have been created internally. On this table there are 3 triggers which execute ...
0
votes
0answers
143 views

Copying a table with its structure, indexes, and associated triggers?

I have studied this post on Stack Overflow: Fastest way to copy a table in MySQL. But no answer is copying everything like the table's structure, data, indexes as well as associated triggers. Is it ...
0
votes
0answers
18 views

Incorporating Partition Views into a Data Warehouse

I've read some material on how to accomplish this but I want to validate it before proceeding. My understanding is that you: 1) Define the table that we want to add into the partition with the ...
0
votes
1answer
62 views

Want to create partitions of a MySQL table and change the charset of new partitions

I have a database table which contains large amount of data. I have written a script to create new partitions every week and drop old partitions (which are of no use). Currently, I have a column in ...
0
votes
0answers
55 views

Sharding TPC-C Benchmark with MySQL Fabric

Currently I have a setup of 5 mysql database nodes (db01 ~ db05). MySQL Fabric and backstore have been installed and running in db01. I configured one global_group (i.e. group_02) with db02 and three ...
0
votes
1answer
52 views

MySQL 5.1: How to rollback partitioned table

I have partitioned a table on the basis of one flag column have value either 0 or 1. Now, I want to roll back these tables. How can I do this?
0
votes
1answer
189 views

How to split the Wordpress database?

FYI - I originally posted this question under the Wordpress Development site here: http://wordpress.stackexchange.com/questions/145861/how-to-split-the-wordpress-database I have a site that is used ...
0
votes
0answers
102 views

Greenplum's poor dynamic partition elimination

I have large fact tables in Greenplum with many years of history that are partitioned by a surrogate key (which represents time). Of course almost all queries are for recent time periods where the ...
0
votes
0answers
91 views

dynamic sql: merge partitioned tables (annual tables into a single table of all years)

I need to merge (UNION) data from annual tables into one large table. I am confused about how dynamic SQL (or else) should be used for this. Below is an updated version of my question on ...
0
votes
0answers
56 views

MySQL 5.6 TO_DAYS partitioning schema always searching in first partition for queries which include <,> or BETWEEN operators in where clause

I have this table definition: CREATE TABLE ClientInfo ( IPAddr VARCHAR(16), RepDate DATETIME NOT NULL )ENGINE=MyISAM; PARTITION BY RANGE(TO_DAYS(RepDate)) (PARTITION pNULL VALUES LESS THAN ...
0
votes
1answer
128 views

Does Oracle Partition by Reference optimizes join queries

Oracle 11g has a new feature in partitioning tables called partition by reference. I want to know whether Oracle joins (nested-loop-joins or hash-join or ...) rows from proper partitions when joining ...
0
votes
0answers
79 views

MySQL: Optimizing for large but discrete data sets

In brief: I'm developing a database that handles GTFS datasets from multiple transit agencies. Each dataset contains millions of rows in the stop_times.txt file (and thus its corresponding table). ...
0
votes
0answers
89 views

How do I debug an Oracle Linux Diskspace Issue?

I have about 429GB Diskspace allocated to this VM. I see that only 179GB is being used. Now when I try to add a 128GB file I get an I/O error saying I do not have any space left. I need to be able ...
0
votes
1answer
43 views

SELECT to only see Offsetting Transactions in table?

I have a temp table like this I have been playing with it but I don't know how to get same PortCode,Cusip combo to give me only offsetting transactions (Buy,Sells) not (Purchase,Purchase) combos. ...
0
votes
1answer
126 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 ...
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 ...
0
votes
1answer
666 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 ...
0
votes
1answer
117 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 ...
0
votes
0answers
233 views

Renaming the system generated partitions during interval partitioning

I am trying to rename the system-generated partition names (generated during interval partitioning). I try to do this while my table is live(data read and write is happening). I need two scripts. I ...
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" ( ...
0
votes
0answers
59 views

How to check on my partitions?

I am a newbie into partition. First I have a table with auto increment with its primary key and many other fields. I wanted to partition based on dataInsertDateTime column. It have me error so I had ...
0
votes
1answer
6k views

open files limit

I am getting "too many open files" error when I try to insert records in a partitioned table. mysql> insert into test.ox_data_geo_span select * from vserv.ox_data_geo_span limit 100; ERROR 1016 ...
0
votes
1answer
39 views

How to keep archive table accessible for some users which is not frequently accessible?

I have partitioned my table based on some boundary ranges. My Database is built at MSSQL server 2008 R2. I have done partitioning for a table which has 40 million records in it. Now it's time to ...
0
votes
1answer
46 views

How does partitioning and the lack of a primary key affect ON DUPLICATE KEY UPDATE

Consider a table with the following columns: phone BIGINT NOT NULL PRIMARY KEY, name VARCHAR(64), state VARCHAR(64) We would like to partition the table on state, however that would require ...
0
votes
3answers
226 views

The column order of composite clustered primary key on a partitioned table? [closed]

I have the following table. create table T (K1 date, K2 int, ....) on partitionScheme(K1) The table will be partitioned by K1 (K1 has low selective. the data will be appended in the order by K1). ...
-1
votes
2answers
130 views

What are the possible risks involved in partitioning large database tables? [closed]

There has been much talk about the benefits of table partitioning in Relational DBMS. I would like to know: Are there any risks to table partitioning ? What measures are there to take to minimize ...
-1
votes
1answer
484 views

Oracle Partition Table Space Issue

I have a table with list partitions on it. When i insert data i see that all the partitions have default initial size of 8 MB and I also noticed that when i truncate full table and check size it still ...