Performance Recommendations for Large Volume of Data

Question

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.

Answer 1

It's important to know what sort of queries you are running. Are they mostly based on the time of the record, or something other field? That will inform what sort of approach you should take here. Maybe your indexes should actually be multi-indexes for example.

What's your EXPLAIN statement say for your queries? That's really one of the best ways to see where exactly things are slowing down and attack it.

Edit: Sorry, I just saw the SUBSTR query, I believe you can make indexes based on substrings of a column. Have you done that? That IS different from just having an index on the column itself vs. having a index on the substring(1,8) of said column.