Avatar of bassman592
Flag for United States of America asked on

Which table access method is more efficient?

The following 2 explain plans show the same query, but the top one uses a new index (IDX_SRX_TRX_MAPID, line 9) that was not available in the bottom explain plan. The bottom plan uses a table (SRX_TRANSACTIONS, line 9) and an index scan on its index (line 10). 

Can someone please offer an opinion as to whether of not the index used in the top plan represents a performance improvement over the bottom plan, just based on looking at the two plans. I know what an index range scan is, but I'm not familiar with the relative efficiency of a single "index range scan" vs a combination of a "table access by global index rowid" followed by an "index unique scan".

With new index:

Without new index:

* Oracle PL/SQL* perfomance tuningOracle Database

Avatar of undefined
Last Comment
Kent Olsen

8/22/2022 - Mon
Sean Stuber

All other things being equal - fewer steps is probably going to be more efficient.

However, if the new index is larger, there may be more "get" operations in order to do the index scans.
DrSQL - Scott Anderson

Well, since both have the same cost, it’s a little difficult to be sure. There are a lot of steps with a cost of one, so I would question the stats you have. Did you run a detailed dbms_stats on all of the objects? also, not knowing the data, I can’t tell you the relative efficiency of a range scan vs. a unique scan. if the domain of possible values is pretty small for the column being indexed, there might not BE much difference. Essentially, a unique scan is a binary tree search and a range scan searches a segment of a tree, but all values. If it’s a big index with a lot of values, then that might mean a lot of non-contiguous physical I/O. If it’s small, it’s all either going to be captured in very few I/Os or it’ll already be cached. But more info is needed by the optimizer and those reviewing this question.

Good luck!
Kent Olsen

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.