Unconfigured Ad Widget

Collapse

Announcement

Collapse
No announcement yet.

SQL Index

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

  • SQL Index

    An EVI helps the SQL query engine quickly find records when you specify a WHERE clause such as: SELECT * FROM filename Where field1 between 'A' and 'I' ORDER BY field1 I think in your case, you want to use just "CREATE INDEX" instead. Chris

  • #2
    SQL Index

    As a side note, actually I don't prefer to use an INDEX. I prefer a logical file. Both SQL and RPG (and COBOL) can use a logical file. RPG can't use an INDEX. Chris

    Comment


    • #3
      SQL Index

      For some reason, I also thought that RPG couldn't use an SQL Index. The same question came up in another forum. I was incorrect. You can use an index in an RPG program. I just tried it using the code below. We're at V5R2.
      Code

      Comment


      • #4
        SQL Index

        RPG can't use an EVI. It can use an SQL view or index. To sumarize, I think Chris's answer was correct.

        Comment


        • #5
          SQL Index

          My understanding is that by creating an index, you can save a lot of time processing SQL statements because the key structure is already defined. Hence, if I enter the following statement: CREATE ENCODED VECTOR INDEX x_filename ON filename (field1 ASC) WITH 256 DISTINCT VALUES , and I know for a fact that there are far fewer than 256 distinct values in field1, I would expect this request to run pretty zippy over my million-record file: SELECT * FROM filename ORDER BY field1. Instead I get, "Query running. Building access path for file filename in libraryname." Can anyone point out the error in my process? Thanks, everyone.

          Comment


          • #6
            SQL Index

            This is not that straightforward an answer. The SQL engine will try and select the most appropriate index when using SQL and sometimes this is not obvious. I have had to add/change various indexes to get the engine to avoid unneccessary path building when querying some complex views. As far as logical versus index, there the samething as far as db2/400 - both flagged as LF's when created, at least they are when creating a view. I wouldn't expect any difference between the two.

            Comment

            Working...
            X