How do Logical Files Work

[SDyke]

I have a question about Logical Files and indexes.

My Phyisical file with 200,000 records and has 70+ fields one of the fields is numeric.

I create a SQL statement to view all the fields of the records where the numeric field is equal to 0.

The SQL takes a while.

I need to know how to speed this up.

I think I am supposed to create a Logical File with the numeric field as key. My question is, does the Logical File need to include all of the fields or only the numeric field to cause the SQL to optimize.

 

[mikrom]

You can create an index file which index is the numerical field you need.
Or you can create logical file which contains only some fields (from the 70+) you need and which has an key - your numerical field.

You have not specified on which platform you are. What I said works on iSeries (aka AS/400).
The logical file is similar to SQL view, so it doesn't need to include all fields from the physical file.

 

[SDyke]

I am working on iSeries(As400).

Again though so I understand. If I have a LF with one field in it and set it as key. Remember it is based on a PF with 70+ fields.

If I run a SQL statement on the PF(all 70 fields) and the where portion of this SQL is the field used in the LF(the only field it contains) will my SQL run optimized?

 

[mikrom]

Logical file with one field? As I said logical file is like an SQL view.
So, the purpose of logical file is to get some fields from physical file and get these field in sorted order according to the keys you defined.
Logical files on iSeries are coded in legacy DDS (Data Description Specification), not in SQL.

Example: Given is a physical file with N-fields

CREATE TABLE mylib/mypfile (                               
FIELD01    DEC ( 7, 0) NOT NULL WITH DEFAULT,              
FIELD02    DEC ( 2, 0) NOT NULL WITH DEFAULT,              
FIELD03    DEC ( 3, 0) NOT NULL WITH DEFAULT,              
....
...
FIELD0N    CHAR (8)    NOT NULL WITH DEFAULT FOR SBCS DATA)

To get only view with FIELD01 and FIELD0N sorted according to the FIELD03 you can create logical file (coded in DDS):

                R mylfile                   PFILE(mypfile)
                  FIELD01
                  FIELD03
                  FIELD0N
                K FIELD03

If you want only speed up your SQL select sorted according to FIELD03, then define simply an index file with your key field such as

CREATE UNIQUE INDEX                    
mylib/myidx ON                     
mylib/mypfile                        
(FIELD03)