Hello All,
There is a table around 3TB in size partitioned on date column on month basis like 20151001, 20151101, 20151201 etc.
I would need to get the active counts for each year.Is there a better way of writing the query based on partitions ?
There are many other columns but required are these two only. Date and active columns are defined as number type.
Here is the sample data.
| 10365016 | 20000131 |
| 10365016 | 20100131 |
| 10365019 | 20100131 |
| 10365056 | 20100131 |
| 10365018 | 20130131 |
| 10365056 | 20130131 |
| 10365020 | 20150130 |
| 10365019 | 20150130 |
| 10365019 | 20150131 |
| 10365019 | 20150131 |
First column being active, second column is date.
The output should be like below
I wrote the query as below, but the explain plan is pretty bad as it is doing full table scan and the query is running since last 2days.
Can any one provide some suggestions please, this is bit urgent. Please kindly help, let me know if more details are needed.
select count(distinct active),
substr(date,1,4)
from table
group by substr(date,1,4)