Re: SQL Index Problem
- From: "Hauser@xxxxxxxxxxxxxxx" <Hauser@xxxxxxxxxxxxxxx>
- Date: Tue, 14 Oct 2008 10:44:37 -0700 (PDT)
How are you querying an index?
An SQL-index cannot be specified in an SQL statement.
In this way assume you created a DDS described logical file and
specify it in the SQL statement.
The query optimizer does not care about any access paths stored in an
DDS described logical file (in the first step of optimization). In the
first step the query optimizer analyses the specified files and
rewrites the SQL statement based on the physical files (or SQL
tables).
After having rewritten the SQL statement all access paths built over
the physical files or SQL tables are searched and extimated. The
optimizer decides which access path will be used or if a table scan is
the fastest way to return the requested data.
The Optimizer uses a table scan because this is the fastest way to
access the data.
In this way:
If you want to get the data in a predefined sequence add an order by
clause to your query.
If the sequence of the returned data does not matter, do not add an
order by clause.
With an order by clause sometimes temporary objects (such as Hash
Tables) will be built and filled with the returned data. After the
temporary stored data will be sorted. This additional (unnecessary)
sort will waste some query time.
Also all queries, where DDS described logical files are specified must
be executed by the old (classic) query engine. The rerouting to the
CQE may cost between 10 and 15 % of performance.
And all enhancements introduced since release V5R3 can only be used
with the newer (SQL) Query Engine SQE.
Birgitta
.
- References:
- SQL Index Problem
- From: iseriesflorida
- SQL Index Problem
- Prev by Date: Mainframe Operations Technician-Location: Omaha
- Next by Date: fast way to copy files from system A to B??
- Previous by thread: SQL Index Problem
- Next by thread: Re: SQL Index Problem
- Index(es):
Relevant Pages
|
