Solved

ALLOW REVERSE SCAN

Posted on 2004-08-28
1
4,545 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
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

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…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

832 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