Link to home
Start Free TrialLog in
Avatar of bassman592
bassman592Flag 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:

User generated image

Without new index:

User generated image

Avatar of Sean Stuber
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.
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!
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial