Professional Web Applications Themes

ALLOW REVERSE SCANS - IBM DB2

In DB2 8.1.2 for Windows, the CREATE INDEX command has an option, "ALLOW REVERSE SCANS." Why would one ever not want to allow reverse scans? Stan...

  1. #1

    Default ALLOW REVERSE SCANS

    In DB2 8.1.2 for Windows, the CREATE INDEX command has an option,
    "ALLOW REVERSE SCANS."

    Why would one ever not want to allow reverse scans?

    Stan
    Stanley Guest

  2. #2

    Default Re: ALLOW REVERSE SCANS

    Apparently there's an increased chance of deadlock if you allow a scan
    in both directions.

    Stanley Sinclair wrote:
     

    Blair Guest

  3. #3

    Default Re: ALLOW REVERSE SCANS

    Blair,

    The above implies that the index scan goes simultanously in both ASC
    and DESC -- ie, that there are two scans simultaneously. Certainly
    sound more efficient for a large table. Does the Optimizer put its
    $0.02 in?

    Stan
    Stanley Guest

  4. #4

    Default Re: ALLOW REVERSE SCANS

    No, the scan for one query won't go in both directions at the same time
    - the extra chance for deadlocks could arise from two queries sorting on
    the same column in opposite directions.

    Stanley Sinclair wrote:
     

    Blair Guest

  5. #5

    Default Re: ALLOW REVERSE SCANS

    "Blair Adamache" <> wrote in message
    news:bmaggg$n6$torolab.ibm.com... 
    That's nasty. Sounds like a train wreck.


    Mark Guest

  6. #6

    Default Re: ALLOW REVERSE SCANS

    > deadlocks could arise from two queries sorting on 

    How can sorting create any deadlock ever?

    Regards Rolf
    Rolf Guest

  7. #7

    Default Re: ALLOW REVERSE SCANS

    Rolf,

    Instead of "sorting on," read "searching the index on." Right, Blair?

    You are reading "sorting" as in the SQL command "SORT BY."

    Stan
    Stanley Guest

  8. #8

    Default Re: ALLOW REVERSE SCANS

    Here's how the desginer who built it described it (Type 2 indexes were
    added in v8):

    "There is a small additional chance of a deadlock between 2 scanners.
    This was particularly the case before type 2 indexes, and much less so
    now. Before type 2 indexes, you could get deadlock doing a forward scan
    and reverse scan on the same range, with at least one X locking."

    Stanley Sinclair wrote:
     

    Blair Guest

Similar Threads

  1. Creating searchable pdfs from scans
    By Lindsay_Gravley@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 1
    Last Post: April 6th, 06:56 PM
  2. Blotchy Color Scans
    By Ray Woodcock in forum Adobe Acrobat Windows
    Replies: 0
    Last Post: April 2nd, 09:11 PM
  3. which sql statement causes sequential scans?
    By Thomas in forum Informix
    Replies: 2
    Last Post: September 30th, 09:29 AM
  4. hi res scans
    By Rockie Moch in forum Adobe Photoshop Elements
    Replies: 3
    Last Post: July 16th, 09:53 PM
  5. Profiler Question (Scans)
    By Amos Soma in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 14th, 12:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139