Solved

ALLOW REVERSE SCAN

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

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