Q:

Performance Tuning a Oracle Query

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

A:

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.

