tomvv
asked on
partition
if we partition the table ..
shall it affect the performance the database..
shall it affect the performance the database..
It can be helpful it can also be harmful.
Helpful - because you are splitting the data physically and logically. This means the optimizer can sometimes "prune" some partitions out of the search because it knows by definition of the partition that none of the data a query needs will be found in those partitions. This makes it faster. You can also have parallel queries spawn separate threads for each partition making io faster.
Harmful - parallel queries are not guaranteed to be faster, in fact, they can be significantly slower than singly threaded queries because the organization of the multiple threads consumes resources. Partitions also require more space since each is its own segment. Queries that don't involve the partitioned fields can be slower because the query needs to sift through more objects. Parallel query is supposed to help with this but as noted before, is not guaranteed to do so.
In general, the pros usually outweigh the cons but it's not a panacea
Helpful - because you are splitting the data physically and logically. This means the optimizer can sometimes "prune" some partitions out of the search because it knows by definition of the partition that none of the data a query needs will be found in those partitions. This makes it faster. You can also have parallel queries spawn separate threads for each partition making io faster.
Harmful - parallel queries are not guaranteed to be faster, in fact, they can be significantly slower than singly threaded queries because the organization of the multiple threads consumes resources. Partitions also require more space since each is its own segment. Queries that don't involve the partitioned fields can be slower because the query needs to sift through more objects. Parallel query is supposed to help with this but as noted before, is not guaranteed to do so.
In general, the pros usually outweigh the cons but it's not a panacea
ASKER
can u please use a simple language..I mean make it more simple way..
helpful - it can be helpful because you'll let the database do less work by dividing the data into pieces. That way it doesn't have to read all of the data. Only the partitions that it really needs to
harmful - it can be harmful because you're making it more complex. More pieces means the database has to think more about what it's going to do. Partitioning takes up more space on your disks too.
so - sometimes it'll be good to partition, sometimes not. The only way to be sure is to test it with your data and your applications
harmful - it can be harmful because you're making it more complex. More pieces means the database has to think more about what it's going to do. Partitioning takes up more space on your disks too.
so - sometimes it'll be good to partition, sometimes not. The only way to be sure is to test it with your data and your applications
In my opinion...
Don't partition tables just to partition tables.
If the way your application is designed will take advantage of partitioned tables by being able to prune partitions, the effort to partition and maintain partitions isn't really worth it. If you have to search through one large index or 15 small indexes on the same field to satisfy your query, the one large index is going to win. That is because of the way the indexes are structured, once you get to the node you need, the nodes are linked. Only one time through the search tree not 15.
Another instance where partitioning is very useful is if you have rolling windows of data and/or need to archive data after a certain period of time. Having partitions makes these types of maintenance very easy.
Don't partition tables just to partition tables.
If the way your application is designed will take advantage of partitioned tables by being able to prune partitions, the effort to partition and maintain partitions isn't really worth it. If you have to search through one large index or 15 small indexes on the same field to satisfy your query, the one large index is going to win. That is because of the way the indexes are structured, once you get to the node you need, the nodes are linked. Only one time through the search tree not 15.
Another instance where partitioning is very useful is if you have rolling windows of data and/or need to archive data after a certain period of time. Having partitions makes these types of maintenance very easy.
ASKER
can you please say in simple
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Remember, All partitions of a single index or table must reside in the same database.