New answers tagged partitioning
0
Another option would be a DDL trigger, which can capture index operations.
USE your_database;
GO
CREATE TRIGGER [IndexEventAudit]
ON DATABASE
FOR ALTER_INDEX, CREATE_INDEX, DROP_INDEX
AS
BEGIN
SET NOCOUNT ON;
DECLARE @e XML = EVENTDATA();
DECLARE @sql NVARCHAR(MAX)
= @e.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(MAX)');
...
0
The status of the bug is labeled as Won't fix and I can understand why in this context.
The right way would have been to run ALTER TABLE tbl_name DROP PARTITION; because the MySQL Documentation on ALTER TABLE Partition Operations says:
DROP PARTITION can be used to drop one or more RANGE or LIST partitions. This statement cannot be used with HASH or KEY ...
0
Yes you can use those filegroups for other table partitioning requirements (as well as other filegroup-related activity).
2
Technically you can assign any existing/new filegroup to any new partition scheme (for other table).
However, you need to understand the existing partition function and scheme design, and more importantly the purpose of the existing partition. It could be partitioned for improved scalability and performance reason by having filegroup on different disks. Or ...
0
I am not sure there is a 'smart' online space reclaim capability. I believe you will need to take a mysqldump of your database, and other items like procedures, then delete the database, stop Mysql, delete the data and logfiles for your database, start the database, and then restore from dump.
0
Can partitioning the table help reduce the completion time?
Yes - if you have sufficient CPU and IO resources to achieve query parallelism and partition the table on a key that will result in a set number of partitions having a roughly equal number of rows in them at all times. This approach uses a principle of spreading the workload across the partitions ...
0
You might improve performance with partitioning. You will need to experiment on a development server and see if it is something that can benefit you.
You may improve query performance, based on the types of queries you frequently run and on your hardware configuration. For example, the query optimizer can process equi-join queries between two or more ...
5
Can partitioning the table help reduce the completion time?
No. Partitioning is not a performance feature, is used for other purposes. If the table has 120 million rows unpartitioned, it will also have 120 million rows after partitioning. Read How To Decide if You Should Use Table Partitioning.
If you want to improve performance you need to identify ...
2
You can use opensource tool like sql-dbdiff or OpenDBDiff. Both are commandline, so can be used in automating scripts.
Also, if you want 3rd party licensed tool then Redgate's SQL Compare (if u want for data compare -- there is data compare as well) is very useful and I have used it extensively for automation.
Out of curiosity, why do you need Indexes on ...
1
What concerns me fact that fieldC is not the lead column in the PRIMARY KEY.
What would be preferable is to reverse the order to the primary key columns
CREATE TABLE `my_table` (
`id` int(10) unsigned NOT NULL,
`fieldA` char(40) NOT NULL,
`fieldB` char(40) NOT NULL,
`fieldC` char(32) DEFAULT NULL,
-- some other fields
PRIMARY KEY (`fieldC`,`id`)
-- ...
6
Here are two really efficient solutions to this, since you're only moving data. These are efficient because they don't actually move data at all: they simply manipulate the metadata to present the data in the desired location. This means not only will they be fast, but the amount of logging required will be minimal.
If you're using Enterprise edition, take ...
0
you can use the BCP and BULK INSERT command and then truncate your source table
0
In SQL Server you can do it this way:
SELECT *
INTO output
FROM source;
TRUNCATE TABLE source;
1
Only rows in 7 partitions qualify the predicate(s) so there is no need to ever lookup rows in the other 3 partitions.
1
Use the first query from the answer you linked and add a simple WHERE clause to get the partitions of a single table:
SELECT
nmsp_parent.nspname AS parent_schema,
parent.relname AS parent,
nmsp_child.nspname AS child,
child.relname AS child_schema
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = ...
Top 50 recent answers are included
