Solved

ALLOW REVERSE SCAN

Posted on 2004-08-28
1
4,724 Views
Last Modified: 2012-06-27
What does allow reverse scan exactly mean in DB2.

1) It assists queries with columns only in order by clause.

2) It assists if original order of columns is reversed in order by

3) It assists in the sense the column order is same but when asc becomes desc and desc becomes asc

4) Lets say we perform the search
Query
 ( FolderId LIKE '1%200246' )
DB2 matches from left to right on the search statements. what I was wondering can DB2 create an index that has a reverse byte order .
so instead of searching '1*' it rather searches 642002*

Please clear my confusion.
0
Comment
Question by:k_murli_krishna
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 13

Accepted Solution

by:
ghp7000 earned 50 total points
ID: 11926470
all it means is that if the execution plan for an sql statement uses the index, the database manager can scan the index from the last position the cursor is positioned on, for example, if the cursor is positioned on row 10,000 of the index and an sql statement requires the index, the index can be scanned either starting from row 10,000 and going forward or starting from row 10,000 and going backwards, if the index is defined without 'allow reverse scan', the cursor is positioned at row 1 and scanning proceeds from top to bottom. For this feature to be useful, your runstats must be up to date and detailed stats must be collected, in addition your index must be large enough so that scanning the entire index is less time consuming for the optimzer as opposed to figuring out if it should scan backwards or forwards.
You can test the usefulness of this feature by looking at the timerons for execution plans with and without allow reverse scans.
0

Featured Post

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…
Suggested Courses

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question