Oracle Table:
- Fields: 55
- Roughly 10 are Used for Querying
- Single Record Length: 512 bytes
- Records: 3-5 million records per day
- Period of Time: 13 months to be available, > 13 months to be purged daily or monthly.
Questions:
- What is the best mechanism for us to maintain this large volume of data and be able to query with good performance ( partitions ? Monthly tables ? )
- Tests on indexing the data have not yielded good results, any suggestions on creating one or more indexes ?
- Date format for these records is YYYYMMDDHHMMSSCSS
example:20190710093054579
- Current coding s forcing a SUBSTR or Like to be used in the query
example:SELECT * FROM TABLE WHERE SUBSTR(SYSDATE,1,8)='20190710'
How can we optimize the performance of querying perhaps for a whole day with other fields in the criteria ?
Is it possible to index this properly ? Or should the date and time be separated into 2 fields ?
We have observed positive results by using between 20190710000000000 and 2019071999999999 but performance is still problematic.