Improve oracle query performance without indexing

Question

What are some things I can do to improve query performance of an oracle query without creating indexes?

Here is the query I'm trying to run faster:

SELECT c.ClaimNumber, a.ItemDate, c.DTN, b.FilePath
FROM items a,
itempages b,
keygroupdata c
WHERE a.ItemType IN (112,115,189,241)
AND a.ItemNum = b.ItemNum
AND b.ItemNum = c.ItemNum
ORDER BY a.DateStored DESC

None of these columns are indexed and each of the tables contains millions of records. Needless to say, it takes over 3 and half minutes for the query to execute. This is a third party database in a production environment and I'm not allowed to create any indexes so any performance improvements would have to be made to the query itself.

Thanks!

Answer 1

Ask the third party to index its join columns, as they should have done in the first place! Without indexes, Oracle has nothing to go on other than brute force.

Answer 2

First I'd rewrite the query to be ANSI standard:

SELECT c.ClaimNumber, a.ItemDate, c.DTN, b.FilePath
FROM items a
INNER JOIN itempages b ON b.ItemNum = a.ItemNum
INNER JOIN keygroupdata c ON c.ItemNum = b.ItemNum
WHERE a.ItemType IN (112,115,189,241)
ORDER BY a.DateStored DESC

This makes it easier to read and understand what is going on. It also helps you not make mistakes (i.e. Cross Joining)that might cause real big problems. Then I'd get the Explain plan to see what the DBMS is doing with that query. Is it trying to use some indexes? Is it joining the tables correctly?

Then I'd review the tables that I'm working with to see if there are any indexes that already exist that I could be using to make my query faster. Finally as everyone else has suggested I'd remove the Order By clause and just do that in code.