Performance Tuning a Oracle Query

Question

I have the below query that through profiling is taking a bit more time that expected and I would like to tweak it some. The purpose of the query is to get the last 3 hours of data from the Messages table for a given device_id and order the results by last message receive (time desc). There currently is a composite index on the database that doesn't seem to really be ideal. Any index or query syntax changes recommendations to speed up this query?

Messages Table Structure:

Columns:

id (auto incremented PK NUMBER(10))
device_id
model_id 
state
creation_date (DATE when row was inserted)
time (unix time message was transmitted)
//a bunch of other columns omitted

Indexes:

id
device_id, model_id, state (composite index)

Query:

select * from messages where device_id='0-12345678' and creation_date > sysdate-3/24 order by time desc

What is the query plan? What is the composite index on? Is it a composite index on `device_id, creation_date`? How many rows does this query return? How quickly does it run? How quickly do you need it to run? — Justin Cave, May 16 '13 at 17:54
How many rows are there in the `messages` table? How unique is `device_id`? You pick data by columns that are not indexed at all (`device_id`, `creation_date`), no wonder the query is slow. — npe, May 16 '13 at 17:55
the messages table has 6,354,837 rows. Device_id can have multiple messages associated so within the table its not unique. I didn't create the indexes and this is why I asked. I believe the index should change — c12, May 16 '13 at 18:01

score 3 · Accepted Answer · edited May 23 '17 at 12:11

You haven't posted the explain plan but it looks like you'd be doing either a full table scan or an index range scan down DEVICE_ID and then accessing each row individually by rowid as your entire select is not in the index and DEVICE_ID is not unique.

There isn't a hard and fast rule for index creation but very generally you should index, in order of cardinality (how many distinct values in the column), the values in the WHERE clause. As you're selecting a range of dates I would recommend creating an index on DEVICE_ID, CREATION_DATE.

You're also using select *. If you don't need to select every column, don't do this. It's more bytes to be read from the disk, more bytes that have to be sent over the network etc. If you're only selecting, DEVICE_ID, CREATION_DATE and TIME I would suggest the index gets changed to these three columns, in this order, so then you don't touch the table at all, you select from and sort on the index alone.

If the business logic states that this list of columns should be unique then create a unique index as opposed to an ordinary one. Lastly, if you don't absolutely need the ORDER BY then remove it. You then remove a sort that will take extra time.

I had a typo in the composite index in my original question, device_id is part or the composite index. Does that change your answer? — c12, May 16 '13 at 18:09
Not if you want to speed it up @c12... I state 3 distinct things though, I'd remove the order by if you don't need it and reduce the number of selected columns _before_ creating a new index as you have to maintain the index and it takes up disk space (if either of these things matter). It all depends on how "slow" it is and how "quick" you want to make it. — Ben, May 16 '13 at 18:11

Performance Tuning a Oracle Query

1 Answers1