Due to the company's business, the big table partitioning scheme, I created the combination partition, partition is partitioned according to the day, is now want to 1 month of data, also is the need to across 30 partition and data volume probably at around 20 million, partition fields become partitioning index, the total number of statistics, if only use partitioning index, fast, but add some other fields especially slow, new added two data field only has 1, 0, so not suitable for index, tried partitioning index of local and global index, effect is not beautiful, labeled as SQL, which way the great god help,

Select count (1)
The from send_new2 t
Where 1=1
And t.s end_status='6'
And t.s ys_time between
04:02:07 to_date (' 2017-02-02 ', '- dd yyyy - mm hh24: mi: ss') and
03:02:07 to_date (' 2017-03-20 ', '- dd yyyy - mm hh24: mi: ss')
And t.I S_ORIGINAL_SMS=0 - "this is not a is_original_sms partition field, the only two forms, 0, 1 send_status partitioning key, the father sys_time is partitioning key, only use the partitioning index, more than 20 million data statistics in 2-3 seconds, combined with the field 200 seconds look not to come out,

CodePudding user response:

Two data fields only 1, 0, can create bitmap index,

CodePudding user response:

reference 1st floor qq646748739 response:

field only 1, 0 two kinds of data, you can create bitmap index,

The field is only 1, 0 I have an example, because it is a big table, the developers don't know what will be used in practical development fields, once used field outside of the partitioning index will have this kind of query is slow, I want to have a better solution,

CodePudding user response:

You the amount of data is not large, we produce the historical data of the billions of records,
The key is to have built index, the fields for best results, use a Sequences records in the table,
You this is a single table not associated multi-table query this won't be very slow, you put your SQL in
PL/SQL Developer in any walk under press F5 to see index,
Another proposal to build an index table space, and send_status field use digital type, do not use character
To build a send_status sys_time, IS_ORIGINAL_SMS index of the combination of the three fields will be index on the index table space,
Don't put into data table space

CodePudding user response:

A few questions or you need to provide information:
1, you soon, have a send_status dictionary? Or only sys_time?
2, a non empty fields sys_time?
3, what is your first layer partition key field? Why the time field will be the second partition key instead of the first floor?
4, "partition fields become partitioning index", the mean field is created on the partitioning index? On the field that is at least partition sys_time have single column? Or is it with the first layer of the partitioning key index created on the group? The order? If so, then the composite index of field cannot all is empty?
5, monthly according to the total amount is 2000 w, then add the other fields after filtering, the data volume is controlled? Such as reduced to 200000? Or not controllable, the likelihood is 200, also may still be 2 million?
6, given at the end of the execution of the plan,

CodePudding user response:

reference 4 floor minsic78 response:

a few questions or need to provide information:
1, you soon, have a send_status dictionary? Or only sys_time?
2, a non empty fields sys_time?
3, what is your first layer partition key field? Why the time field will be the second partition key instead of the first floor?
4, "partition fields become partitioning index", the mean field is created on the partitioning index? On the field that is at least partition sys_time have single column? Or is it with the first layer of the partitioning key index created on the group? The order? If so, then the composite index of field cannot all is empty?
5, monthly according to the total amount is 2000 w, then add the other fields after filtering, the data volume is controlled? Such as reduced to 200000? Or not controllable, the likelihood is 200, also may still be 2 million?
6, given at the end of the execution plan,

1. When using the two fields, soon send_status, sys_time
2. Not a empty fields, but the data is not empty,
3. The first layer with send_status because business needs, there are other projects at the same time, use the same table send_status fields, but there is no use sys_time fields, consider if use time may need to rewrite a part of SQL, because the use of project is more, considering the deployment problem, use send_status as the first layer,
4 is a composite index, send_status sys_time order, will not be all empty,
5. It is not controllable, because the query words may not of much time span, and there are some other fields cannot be built index, query the amount is not fixed,
6. Implementation plan following the partitioning index is fast:
Execution Plan
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Plan the hash value: 1600035421

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

| | Id Operation | Name | Rows | Bytes |
The CPU Cost (%) | Time | Pstart | Pstop |

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

| | 0 SELECT STATEMENT | | 1 | | 11
7864 (1) | 00:01:35 | | |

| | 1 SORT AGGREGATE | | 1 | | 11
| | | |

| 2 | PARTITION LIST SINGLE | | 2557 k 26 m | |
7864 (1) | 00:01:35 | KEY | KEY |

2557 k | 3 | PARTITION RANGE ITERATOR | | | | 26 m
7864 (1) | 00:01:35 34 | | 80 |

| | * 4 INDEX RANGE SCAN | IDX_SEND_PAR_NEW_20150501 26 m | | | 2557 k
7864 (1) | 00:01:35 | | |

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --


The Predicate Information (identified by operation id) :
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

4 - access (" T ". "SEND_STATUS=6" AND "T". "SYS_TIME & gt;"=TO_DATE (' 2017-02-02 HKT
: 7 ', 'syyyy - mm - dd hh24: mi: ss')

AND "T". "SYS_TIME" & lt;=TO_DATE (' 03:02:07 2017-03-20 ', 'syyyy - mm - dd h
H24: mi: ss '))


This is slow execution plan:

Execution Plan
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Plan the hash value: 3591182293

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

| | Id Operation | Name | Rows
| | Bytes | Cost % (CPU) Time | Pstart | Pstop |

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

| | 0 SELECT STATEMENT | | 1
14 (1) | | 1847 | 00:00:23 | | |

nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull