Geek Answers Handbook

Improving oracle query performance without indexing

What can I do to improve the performance of oracle queries without creating indexes?

Here is a query that I am trying to execute 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 table contains millions of records. Needless to say, it takes more than 3 minutes to complete the request. This is a third-party database in a production environment, and I am not allowed to create any indexes, so any performance improvements should be made in the request itself.

Thanks!

+4
source share
16 answers

First, I would rewrite the query as an 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 happening. It also helps you not make mistakes (i.e., Cross joining), which can cause big problems. Then I will get an explanation plan to see what the DBMS is doing with this request. Is he trying to use some indexes? Does it connect to tables correctly?

Then I looked at the tables that I work with to find out if there are any indexes that already exist that I could use to make my query faster. Finally, like everyone else, I decided to remove the Order By clause and just do it in the code.

+6
source

Ask a third party to index the join columns, as it should have been done in the first place! Without indexes, Oracle has nothing but brute force.

+7
source

You can try to create a materialized view for any of these tables. Then you can create an index on the materialized view, which will help speed up the query (which will then query the materialized view instead of the raw table).

Of course, if your base table is updated, your overview and indexes need to be updated.

+6
source

First, consider the execution plan. Does it accurately reflect the number of rows that should be received at each stage of the query? How selective is the predicate "a.ItemType IN (112,115,189,241)"? Does the execution plan plan to use temporary disk space for joins or sorts?

Actually, perhaps you can change the issue by including an implementation plan.

Also, make sure that you do not have hash disconnections, which sometimes happens on OLTP-configured systems, as they are the most efficient way to evenly distribute bulk data in Oracle. They should appear in the execution plan.

+3
source

You can try filtering by item type before joining your tables, as shown here.

If you work with Oracle prior to 9i, this sometimes provides amazing benefits.

 select c.claimnumber, a.itemdate, c.dtn, b.filepath from ( select itemdate from items it where it.itemtype in(112,115,189,241) ) a itempages b, keygroupdata c where a.itemnum = b.itemnum and b.itemnum = c.itemnum

You can also try adding the hints / + RULE / or / + ORDERED / to see what happens ... again, especially with older versions, this sometimes gave amazing results.

 SELECT /*+RULE*/ 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
+3
source

If the query inputs are constant or predictable ( itemType IN (...) ), an alternative would be to run the query once or twice a day and save the results in a local table with the indices where necessary.

Then you can make a costly query “offline” and get faster / better results for an interactive query.

+1
source

Is this query frequently executed? It seems that it is in the interest of the database owner to create the indices necessary to speed up this query. The 3.5 minutes you spend on fulfilling the request should have a certain impact on their production environment!

In addition, did they perform statistics on table updates? This can improve performance because the join order is calculated based on table statistics.

By the way, what are you allowed to do? Just to read? If you can create temporary tables and put indexes on them, I can consider creating temporary copies of the table, indexing them and then merging them with temporary copies using the index.

+1
source

I know that this stream is very old, but for search engines I still wanted to offer an alternative solution that will work on the oracle, and depending on the data can be much faster.

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

You can also try the hint /*+ MATERIALIZE */ in the WITH clause.

Actually I find the syntax of the old oracle syntax is much simpler than ansi sql ^^

+1
source

Without indexing, this query will only worsen as the size of the table increases. With this in mind, try removing the order by clause and doing so on the client side.

0
source

Are statistics collected on these tables? If not, collecting statistics may change the execution plan, although this will not necessarily be for the better.

Also, see the execution plan. You can see that it joins tables in non-optimal order (for example, it can join with b and c before joining with a filter condition).

You can use hints to try to influence access paths, join order, or connection method.

Refresh . The response to the commentary led me to this presentation, which may be useful or at least interesting.

0
source

Sometimes you can see an advantage by adding additional paths for the optimizer to choose, adding what seems like an excess element to the where clause.

For example, you have A.ItemNum = B.ItemNum AND B.ItemNum = C.ItemNum. Try adding A.ItemNum = C.ItemNum. I'm sure, however, that the optimizer is smart enough to figure it out on its own - it's worth a try.

0
source

Depending on the data type of the ItemType column, you can perform the following actions faster, if it is varchar, Oracle will do implicit conversions.

 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
0
source

If you say that there are no indexes, does that also mean that primary or foreign keys are not defined? Obviously, analyzing tables and collecting statistics is important, but if metadata, such as determining how tables should be joined, does not exist, then Oracle may choose a bad execution path.

In this case, using a hint such as / * + ORDERED * / might be the only option for the optimizer to reliably choose a good execution path. It may also be useful to add foreign keys and primary keys, but define them as DISABLE and VALIDATE.

I suggest that the usefulness of this comment depends on how far the aversion to indexes goes so YMMV.

0
source

First create a view for this query, and then create a table from this view. Also create an index by date, complete the task, and assign it at midnight when the system is in standby mode.

0
source

Well, since you cannot create indexes, I would make sure the statistics were updated, I would rewrite the query this way:

with a as (select /*+ MATERIALIZE */ ItemType, ItemNum, DateStored, ItemDate from items where ItemType in (112,115,189,241)) SELECT c.ClaimNumber, a.ItemDate, c.DTN, b.FilePath FROM a, itempages b, keygroupdata c WHERE a.ItemNum = b.ItemNum AND b.ItemNum = c.ItemNum ORDER BY a.DateStored DESC

0
source

Remove ORDER BY

sort after you return the rows to your application.

-1
source

More articles:


All Articles