Solved

ALLOW REVERSE SCAN

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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 demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now