FAQ Database Discussion Community


Do I have to drop the primary key or reload the table data for partitioning in MySQL InnoDB?

mysql,innodb,database-performance,database-partitioning,pruning
I have to partition a few tables by date range. Do I have to drop the primary or reload the complete data to be able to partition these tables? Some of the tables do contain a lot more than 50 million rows. alter table temp_table_test1 partition by range (unix_timestamp(created_at)) (...

Many tables or one big table with horizontal partitioning

mysql,database-partitioning
I'm design database for new project. I need to create a custom multi-chat system but I have a question: many tables (one for one chat) or one big table with horizontal partitioning ? My database is mysql and the chat are isolated each other. I ask this because the project...

Partitioning a related set of Oracle tables by day when they don't all have Time information

oracle,database-partitioning
I have a set of tables that look similar to this: Time_Table (relatively small): Time (TIMESTAMP) timeId (NUMBER) Data... (NUMBER) Table2 (large, about 30 rows per time_table row): timeId (NUMBER) table2Id (NUMBER) Data... (NUMBER) Table3 (very large, around 10 rows per table2 row, currently 1.4 billion rows after a couple...

Oracle Partition by 2 columns Interval

oracle,database-partitioning
Im trying to find on google my situation but no one talk about this situation. i have a table thats is gonna be partitionized with 2 columns. for 2 columns partitions can anyone show an example for the interval? In this case i have only one. For this example how...

SQLite database partitioning

database,sqlite,sqlite3,database-partitioning
I want to create sqlite partition structure, I created 3 databases, I attached those 3 databases to another database which will be parent with ATTACH DATABASE '1.db' as 1 ATTACH DATABASE '2.db' as 2 ATTACH DATABASE '3.db' as 3 after i .quit the parent database the database attachments detach, How...

Partition pruning based on check constraint not working as expected

sql,postgresql,postgresql-9.3,database-partitioning,postgresql-performance
Why is the table "events_201504" included in the query plan below? Based on my query and the check constraint on that table I would expect the query planner to be able to prune it entirely: database=# \d events_201504 Table "public.events_201504" Column | Type | Modifiers ---------------+-----------------------------+--------------------------------------------------------------- id | bigint |...

PostgreSQL 9.3 Partitions Not Working

postgresql,database-partitioning
I have a table, e, with range partitions setup daily. The main table and partition tables have been defined like so. CREATE TABLE e ( toi TIMESTAMP WITH TIME ZONE WITH ( OIDS=TRUE ); CREATE TABLE e_20150501 CONSTRAINT e_20150501_toi_chk CHECK (toi >= ‘2015-05-01’::DATE AND toi < ‘2015- 05-02’::DATE) INHERITS (e)...

How to Mimic Postgres Foreign Keys into a Partitioned Table

postgresql,foreign-keys,postgresql-9.3,database-partitioning
I have a partitioned table (call it A) with a serial primary key that is referenced by another table (call it B). I know that I can't actually create a foreign key from one to the other (since I don't know from which partition the data is actually stored), so...

Alter table switch statement failed due to check constraints

sql,sql-server-2008-r2,database-partitioning
I have a scenario where i load the flat file into 3 staging tables and switch to partitioned table. When i try to switch from staging to partition table, am getting the following error. Could you help? Here are the file group names PRIMARY FG1 FG2 FG3 FG4 and here...