partitioning

Oracle table partition

Can any one help me referring a book for Oracle table partitioning. Oracle 10g complete reference contains the basics but i need elaborate with example. Thank you ...

Oracle refuses to use index

I have a partitioned table like so: create table demo ( ID NUMBER(22) not null, TS TIMESTAMP not null, KEY VARCHAR2(5) not null, ...lots more columns... ) The partition is on the TS column (one partition per year). Since I search a lot via the timestamp, I created a combined index: create index demo.x1 on demo (ts, k...

What is MYSQL Partitioning?

I have read the documentation (http://dev.mysql.com/doc/refman/5.1/en/partitioning.html), but I would like, in your own words, what it is and why it is used. Is it mainly used for multiple servers so it doesn't drag down one server? So, part of the data will be on server1, and part of the data will be on server2. And server 3 will "p...

How to partition Mysql across MULTIPLE SERVERS?

I know that horizontal partitioning...you can create many tables. How can you do this with multiple servers? This will allow Mysql to scale. Create X tables on X servers? Does anyone care to explain, or have a good beginner's tutorial (step-by-step) that teaches you how to partition across multiple servers? ...

T-SQL - create partition function and scheme - SQL Server 2008

I am creating partition function and schemes. In SQL Server 2008, it only defines range partitioning and not list partitions. Dont we have list partitioning in SQL Server? I am using SQL Server 2008 Enterprise edition. ...

SQL Server Automatic Partitioning of Large Database Tables

I am dealing with database tables with tens of millions of rows (with the potential to go to the hundreds of millions over time), and am looking at implementing database partitioning to try to keep performance stable as the row count increases. This is what I'd like to do: Say I have a table that stores animals. One of the fields is the...

Partition Or Separate Table

I am designing database for fleet management system. I will be getting n number of records every 3 seconds. Obviously, there will be millions of record in my table where I am going to store current Information of vehicle in the current_location table. Here performance is an BIG issue. To solve this, I received the following suggestions...

database partiton Function (sql server 2008)

HI, I want to write partiton in sql server 2008 for my fleet management system. I want to write a partition function such that --values (vehicle number) like for example mh-30-q-126,mh-30-a-126,mh-12-fc-126 should be moved to respective partiton, depending upon middle values like ,q,a,fc respectively My trial function:- CREATE PA...

Partitioning Oracle tables used for logging

I have an application that records activity in a table (Oracle 10g). The logging records should be kept for at least 30 days. I expect about 20 million rows to be added to this table every month. The DBA suggested that the table be split in partitions containing one week of data. The weekly maintenance script would then delete the oldes...

MySql Partitioning

Hi Guys, I'm trying to create partitions in one big table, but I'm getting this error: 1505 - Partition management on a not partitioned table is not possible There is any command to convert the table? Or do I have to create a new one and import all data? There is any problem when using Partitioning with Replication? Thanks in advanc...

Is it possible to partially refresh a materialized view in Oracle?

I have a very complex Oracle view based on other materialized views, regular views as well as some tables (I can't "fast refresh" it). Most of the time, existing records in this view are based on a date and are "stable", with new record sets having new dates. Occasionally, I receive back-dates. I know what those are and how to deal wit...

Can MySQL create new partitions from the event scheduler

I'm having a table looking something like this: CREATE TABLE `Calls` ( `calendar_id` int(11) NOT NULL, `db_date` timestamp NOT NULL, `cgn` varchar(32) DEFAULT NULL, `cpn` varchar(32) DEFAULT NULL, PRIMARY KEY (`calendar_id`), KEY `db_date_idx` (`db_date`) ) PARTITION BY RANGE (calendar_id)( PARTITION p20091024 VALUES LE...

PostgreSQL: UPDATE implies move across partitions

(Note: updated with adopted answer below.) For a PostgreSQL 8.1 (or later) partitioned table, how does one define an UPDATE trigger and procedure to "move" a record from one partition to the other, if the UPDATE implies a change to the constrained field that defines the partition segregation? For example, I've a table records partitio...

Object Positioning Algorithm

I'm wondering if there is an "optimal" solution for this problem: I have a n x m (pixel) sized space with p preexisting rectangled - objects in various sizes on it. Now I want to place q (same sized) new objects in this space without any overlapping. The algorithm I came up with: Create array A[][] with the size [(n)/(size_of_object_...

Database patterns for partitioning large hierarchical datasets

Are there any best-pratices/patterns or general advice for partitioning large amounts of hierarchical data? Think of, say, a database of all the people in a given country and tracking who has worked with who. Thinking of the "person" entities in isolation, if a lot of data were to be kept about each person then a natural approach seems...

Is it possible to partition more than one way at a time in SQL Server?

I'm considering various ways to partition my data in SQL Server. One approach I'm looking at is to partition a particular huge table into 8 partitions, then within each of these partitions to partition on a different partition column. Is this even possible in SQL Server, or am I limited to definining one parition column+function+scheme...

What is table partitioning?

In which case we should use table partitioning? ...

How to insert data in partition table which is not defined in partition?

Anybody can tell me how can we insert data in partitioned table which is not satisfying partitioning condition. ...

How can I remove table partitions from an Oracle table?

I've discovered that the partitions used on a particular table are hurting query performance and would like to remove the partitions from the table. Is there an easy way to do this? The table in question has 64 partitions. Based on some initial investigation, I've come up with the following options. Is there a better way? Copy data int...

How can I use MySQL table partitioning on this table?

I have a table that essentially looks like this: CREATE TABLE myTable ( id INT auto_increment, field1 TINYINT, field2 CHAR(2), field3 INT, theDate DATE, otherStuff VARCHAR(20) PRIMARY KEY (id) UNIQUE KEY (field1, field2, field3) ) I'd like to partition the table based on the month and year of theDate, h...