Re: Slow query on partitioned tables with over 200M rows
Posted by:
Hong Yang
Date: February 12, 2014 02:53PM
Thanks again, Rick. Appreciate your insight.
>Do you know how many rows it would be? With an ordinary disk drive, it would probably scan 20K rows per second.
I would expect 360 rows per device and component on a partition.
>What is the cardinality of the combination of device_key and component_key? And do you always look at both of them together? I am fishing for normalizing that pair into some sized INT UNSIGNED, thereby shrinking the table a little more.
There are 50K unique devices and each device can have 0 to a couple of hundred components.
Are you aware of performance benchmark results on table size vs query response time? If the general guideline is to keep the table size under a couple of million rows, then we may have to consider your approach of creating hourly partitions for the current day table.
Thanks,
Hong
Subject
Views
Written By
Posted
2647
February 11, 2014 05:10PM
946
February 12, 2014 01:48AM
1017
February 12, 2014 08:33AM
1077
February 12, 2014 08:47AM
979
February 12, 2014 09:59AM
1139
February 12, 2014 02:31PM
Re: Slow query on partitioned tables with over 200M rows
1099
February 12, 2014 02:53PM
1344
February 12, 2014 04:33PM
1062
February 12, 2014 06:41PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.