Hi,
First I think you need to learn some basics about the SQL objects and query
optimization:
1. A view is a logical file without an order by clause (i.e. without any
key) that describes what data must be selected and returned. An view can be
used in SQL statements like any physical file or SQL Table. An SQL view can
be used in composition with native I/O but since it never has a key the
usage is only restricted
2. An index contains the access path, that can be used by the query
optimizer to get (fast) access to the data that is requested in the SELECT
statement (using either physical files or SQL tables or views). An SQL index
cannot be specified within a SELECT statement, but can be used with native
I/O like any keyed DDS described logical file.
3. An access path is also included in DDS described logical files with a key
and primary and unique key constraints.
4. The query optimizer checks all access paths (located in either SQL
indexes or DDS described logical files or primary or unique key constraints)
and decides for the best one(s). Without (any) access path the query
optimizer will perform a table scan, i.e. read all records within a table
(or DDS described physical file). Reading all records from a table/physical
file with 500 records will not be any problem, but reading all records from
a table/physical file with 500 million of records will result in a bad
performance.
5. Even though DDS described logical files can be specified in a SQL
statement it should be avoided. Before release 7.1 all (SQL) queries
containing DDS described logical files were executed by the old query engine
(CQE). If a DDS described logical file is specified within a SQL statement.
The SQL statement gets rewritten by the query optimizer based on the
underlying physical files/SQL tables. The query optimizer will first analyze
the DDS description, and takes the field selection, join clauses and
SELECT/OMIT clauses (but NOT the key information) and rewrites the SQL
statement based on this information. After having rewritten the Query the
optimization starts.
Run your query through the IBM i Navigator's Run and SQL Script tool with
Visual Explain.
Visual Explain includes an index advice and also allows the advices indexes
to be generated.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Hoteltravelfundotcom
Gesendet: Thursday, 26.12 2013 23:50
An: Midrange Systems Technical Discussion
Betreff: Creating index
Hi Does one create an index via VIEW? I am not seeing how to do this.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
