ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

including RRN in basic SQL select

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • including RRN in basic SQL select

    example...

    Will...

    select column1, rrn(a)
    from file1 a
    where column1 = test
    order by rrn(a)

    be more resource intensive than...

    select column1
    from file1 a
    where column1 = test
    order by rrn(a)

  • #2
    Why do you expect there could be a difference?
    It is exactly the same query except you return the relative record no in a 2nd column.
    In either way you should have an index over Column1 and an additional index over the RRN may help also, for getting fast access to the data.

    Birgitta

    Comment


    • #3
      Seems I recall that using the rrn in the query will force a full table scan. What is rrn is used in the where?

      Comment


      • #4
        Originally posted by jayvaughn View Post
        Seems I recall that using the rrn in the query will force a full table scan. What is rrn is used in the where?
        1. You use the relative record no in both queries (so it doesn't matter whether you specify it as separate column or not. Table Scan was performed in the CQE and in the SQE before 7.1.
        2. With release 7.1 even if you select several relative record no, the record no are first searched in an Values List. With the found information (address of the record) the data will be accessed.
        3. Since Release 7.1 TR 5 Indexes can be created over the relative record no, which helps selecting the data
        4. A table scan will still be performed if you select more than 15-20% of the data of a table
        5. Use visual explain (graphical representation of the access plan of a query) to check what really happens.

        Birgitta

        Comment

        Working...
        X