Renaming partitions in ORACLE.
if we use the ALTER TABLE RENAME PARTITION statement to rename the existing partitions in an Oracle table, do we need to recreate the local partitioned indexes with newly changed partition names? ...
partitioning
Partitioning and formatting vmdk virtual disk
Hi, I need to distribute directory containing about 300k files with total size of 20G. Other people should be able to easily access this directory and edit those files. Right now I'm using zip archives, but packing/unpacking takes a really long time. Recently a collegue of mine suggested using some kind of virtual disks. I'm familiar ...
Partition Exchange as publishing technique in SQL Server?
I'm familiar with the concept of using partitions in Oracle as a technique to pubish incremental additions to tables (in a DW context). (like this example) For example. a daily snapshot for a data mart fact table is loaded behind the scenes in a partition within a table. for example with date as the partition key (1 partitioned table...
What is better: to have many similar databases or one database with similar tables or one database with one table?
I need to work with several data samples, to say, N. The samples represent similar data but from different origins. For example, history of order in different shops. So the structure of all the samples is the same. To operate with the data I have several possibilities: Use N databases with identical schema, one for each sample Use one ...
Partitioning Existing Tables
Hello, How do i partition existing table in SQL Server 2005/08 ? I couldn't find a better clue on it. ...
Where can I find the range interval partition tablespaces in the data dictionary?
For a range interval partitioned table, you can specify multiple tablespaces like: CREATE TABLE range_part_interval_table(col1 NUMBER, col2 NUMBER) PARTITION BY RANGE (col1) INTERVAL (10) STORE IN (ts2, ts3, ts4) (PARTITION VALUES LESS THAN (100) TABLESPACE ts1); But I can't find where the interval tablespaces are stored in the data d...
mysql database automatic partitioning
I have a mysql database table that I want to partition by date, particularly by month & year. However, when new data is added for a new month, I don't want to need to manually update the database. When I initially create my database, I have data in Nov 09, Dec 09, Jan 10, etc. Now when February starts, I'd like a Feb 10 partition automa...
Programmatically name FILEGROUP in SQL server?
I'm trying to write a stored procedure that will create a new FILEGROUP based upon a given date parameter. What I want to see is a FILEGROUP called something like '2010_02_01'. What I get is a FILEGROUP called '@PartitionName'. ALTER PROCEDURE [dbo].[SP_CREATE_DATE_FILEGROUP] @PartitionDate DATETIME AS DECLARE @PartitionName VARCHA...
Dynamic table partitioning in Oracle
I'm in the process of building a database storage for my app consisting on a single table with a huge data volume (hundreds of millions of records). I'm planning on having an index on the date field, since I'll be doing a batch recovery of all the records in a given period of time every now and then (for example, retrieving all records f...
Efficient querying of multi-partition Postgres table
I've just restructured my database to use partitioning in Postgres 8.2. Now I have a problem with query performance: SELECT * FROM my_table WHERE time_stamp >= '2010-02-10' and time_stamp < '2010-02-11' ORDER BY id DESC LIMIT 100; There are 45 million rows in the table. Prior to partitioning, this would use a reverse index scan and s...
How to initialize a file system?
I have some partitions on /dev/sda. I want to remove them all of them programatically. One way is to list all partitions and then delete them one by one. Another way is to just execute dd if=/dev/zero of=/dev/sda The first option failed when I got some problem with my partition table due to which the listing of the current partitions ...
Oracle partition pruning with NLS_COMP = Linguistic
Oracle 10g. We have a large table partitioned by a varchar2 column (if it were up to me, it wouldn't be this column, but it is) with each partition having a single value. Ex. PARTITION "PARTION1" VALUES ('C'). We also have NLS_COMP = LINGUISTIC. Partition pruning, when indicating a value in that column, doesn't work. SELECT * from ...
How do I partition efficiently in SQL Server based on foreign keys?
I am working on SQL Server and want to create a partition on a table. I want to base it off of a foreign key which is in another table. table1 ( fk uniqueidentifier, data ) fk points to table2 table 2 ( partition element here ) I want to partition table1 base on table2's data, ie if table2 contains categories ...
Optimal database table optimization method
Hello, I have a database table that is growing too big (few hundred million rows) that needs to be optimized, but before I get into partitioning it, I thought I'd ask about suggestions. Here is the usage: 0 . Table contains about 10 columns of length about 20 bytes each. INSERTS are performed at a rate of hundreds of times per secon...
If I partition a table, can I replace the file for a partition without telling SQL Server?
I would like to be able to swap one table partition for another, just by replacing partitionN.ndf before starting up the server. The general aim is to be able to split out some sets of table rows into different files so that when the app is installed, it only goes with one set. There are some rows that are always needed, so Scenario A ...
How to import a partitioned table into a table with a different number of partitions? (Oracle 10g)
I have an existing database with tables which each have 4 partitions (there are tables using both RANGE and HASH partitioning). I need to import it into another database with a pre-created schema where the same tables will have 8 partitions. How do I do this? Does this "just work" if I do a table-level import? ...
MySQL partitioning query on timestamp
I running MySQL 5.1.30 on Solaris 10. As I have a table which I partition into 12 according to each month. The table structure as follow mysql> desc my_events; +-----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+----...
When to use which mysql partitioning model
Ok guys, just starting out with partitioning some tables in mySQL. There's a couple of different ways describing this, but what I cant find is a more practical approach. - Which type of data does each way of partitioning have the best effect on? Or doesn't it really matter? Reference: http://dev.mysql.com/tech-resources/articles/perfor...
Partitioning mySQL tables that has foreign keys?
Hi! What would be an appropriate way to do this, since mySQL obviously doesnt enjoy this. To leave either partitioning or the foreign keys out from the database design would not seem like a good idea to me. I'll guess that there is a workaround for this? Update 03/24: http://opendba.blogspot.com/2008/10/mysql-partitioned-tables-with-tr...
Foreign keys vs partitioning
Hi! Since foreign keys are not supported by partitioned mySQL databases for the moment, I would like to hear some pro's and con's for a read-heavy application that will handle around 1-400 000 rows per table. Unfortunately, I dont have enough experience yet in this area to make the conclusion by myself... Thanks a lot! References: h...