• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

partition

if we partition the table ..

shall it affect the performance the database..
0
tomvv
Asked:
tomvv
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
aadi369Commented:
No.....do it worry free :)


Remember, All partitions of a single index or table must reside in the same database.
0
 
sdstuberCommented:
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
0
 
tomvvAuthor Commented:
can u please use a simple language..I mean make it more simple way..
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
sdstuberCommented:
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
0
 
johnsoneSenior Oracle DBACommented:
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.
0
 
tomvvAuthor Commented:
can you please say in simple
0
 
sdstuberCommented:
yes partitioning will probably affect performance

it might be good or bad, you will have to test
0
 
johnsoneSenior Oracle DBACommented:
2 situations where I do it.

Partition key is in the where clause.

Rolling data windows where you only keep x months of data and have to remove it.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now