Performance Recommendations for Large Volume of Data

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 a. 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.