1

First, I am a complete novice when it comes to databases, but have been given the task to speed up queries on a large set of data. (Hundreds of millions of records) The current implementation is a very simple data warehouse that was created long ago in Oracle. The existing table has no primary key but each record is unique. The table is indexed by the first two columns listed below.

The data itself is fairly simple:

  1. device – there are multiple devices with a unique number indicator
  2. data generation time – each device generates a set of data multiple times a day at different, random times. Each data set covers multiple days. Sample times for individual data points in the set can be up to every second. For times prior to the data generation time, these are measured results. For times after the data generation time, these are the device’s prediction of what the data will be. Queries are often pulled for a full day that compare measured verses predicted data for a given device (e.g. how well did the device predict future needs)
  3. Date/Time of the data points
  4. Data point 1
  5. Data point 2

. . . Data point 23

The major types of queries are:

  1. Give me the latest data generated by a device
  2. Give me all the data for a device for a given day (as previously described above.)
  3. Give me the data generation times for a device on a given day

My idea to speed up queries would be to split the table up into two tables as follows:

MetaData Table (each of the first 3 will be indexed)

  1. device
  2. data generation time
  3. day – This would be a new, indexed data point
  4. Primary Key – a number with the device number, data generation time (141230073205 – for 2014 Dec 30 07:32:05), and day (150102 – for 2015 Jan 02)

Data Points Table (There will be 10s of thousands of these for each entry in the MetaData table above)

  1. Foreign Key – that points to the Primary Key in the above MetaData table for which this particular point is valid
  2. Date/Time of the data points
  3. Data point 1
  4. Data point 2 . . . Data point 23

So, long story short (too late!):

  1. Is this a valid approach to speeding up queries?
  2. Is there a better way to organize the data?
  3. What other things can I do to cut down on the query times?
  4. Any sqlplus coding tips would also be greatly appreciated.
  • 1
    So your idea is to pull out of the table the day for the data in question, essentially? So that you can query on the day before you jump to the actual data you need? If this is the case, why not just create an additional index on the original table that is for the day? (index day, device) If the "data points" are truly unstructured (so that you can't separate out individual point to different tables), I'm not sure you approach above will improve performance beyond just the creation of an additional index. Also - if there is a unique key and no primary key - why not have the uk be the pk? – Joishi Bodio Dec 30 '14 at 19:44
  • The idea is that, in order to find the data for one day, I have to parse 100s of millions of records. If I split the tables up, then I have only have to parse about 10,000 records and then index the DataPoints records I want. What I’m not sure about is whether that would be faster? Since I’m a complete noobie, I don’t know if keeping all the data in one table and indexing is faster than breaking it up along the lines of my normal queries. Also, to duplicate the MetaData table in the DataPoints table seems to be a waste of resources. – mlbrink Dec 30 '14 at 22:10
  • Also, the data points (1 to 23) are structured. (all data point 1 are the same type) I get all 23 data points at every time period. The time sample rate is the same for an individual device, but may be different from other devices. Like devices have the same time sample rate. – mlbrink Dec 30 '14 at 22:20
  • 1
    I do not think that splitting out the current data generation time into a separate table as a date will be faster than just creating an index on that column that's for the date portion.. CREATE INDEX SEARCH_ON_DATE ON TABLE (TRUNC(DP_DATETIME), DEVICE); – Joishi Bodio Dec 30 '14 at 22:56
  • 4
    Due to the nature of the data, I'm not sure there's really much of an "improved design" you can achieve. You could look into partitioning the table... But still - is there some reason you DON'T want to index on date? – Joishi Bodio Dec 31 '14 at 15:52
0

I was able to split the table up as described above. Since I did not know the data generation time a priori, the approach above did give a 6 to 10 times speed up on queries.

0

Your question is too broad for any answer here to fully address, so I'll just address one point. For the major query type of "Give me the latest data generated by a device", you could create a new column in the table that gets the same data as the "data generation time" column and then have a periodic procedure that nulls older values in that column for each device keeping only the last N. A function based index could then be created that returns the device column where data exists in that row and null when that column is null. The query could then use the same function as that index and would be able to lookup a small set of data in the index for the device and retrieve it quickly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.