SQL Index Problem - IBM AS400

This is a discussion on SQL Index Problem - IBM AS400 ; I have a physical file with 3 records in it. I am creating this SQL code against it and then running runsqlstm, it creates the index. CREATE INDEX TESTPFT ON TESTPF (ZIP DESC) The records are this: 78985 68191 98989 ...

+ Reply to Thread
Results 1 to 15 of 15

Thread: SQL Index Problem

  1. SQL Index Problem

    I have a physical file with 3 records in it.

    I am creating this SQL code against it and then running runsqlstm, it
    creates the index.

    CREATE INDEX TESTPFT
    ON TESTPF (ZIP DESC)

    The records are this:

    78985
    68191
    98989

    When I query TESTPFT they are not in the order of 98989 then 78985
    then 68191, what am I doing wrong.


  2. Re: SQL Index Problem

    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

  3. Re: SQL Index Problem

    il 14/10/2008 19.26, Scrive iseriesflorida 43542568:
    > I have a physical file with 3 records in it.
    >
    > I am creating this SQL code against it and then running runsqlstm, it
    > creates the index.
    >
    > CREATE INDEX TESTPFT
    > ON TESTPF (ZIP DESC)
    >
    > The records are this:
    >
    > 78985
    > 68191
    > 98989
    >
    > When I query TESTPFT they are not in the order of 98989 then 78985
    > then 68191, what am I doing wrong.
    >

    An index doesn't necessarily sort records, it's different than putting K
    fields on DDS, then if you use RUNQRY it will always use the order it
    found in the physical file, as sql SELECT does unless you specify ORDER
    BY clause. You can reorganize physical file records by ZIP and DESC keys
    using:
    RGZPFM FILE(TESTPF) KEYFILE(TESTPFT)


    --
    Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAñe joAlcoolInside
    Spaccamaroni andate a cagare/Spammers not welcome/Spammers vão à merda
    Spamers iros a la mierda/Spamers allez vous faire foutre/Spammers loop
    schijten/Spammers macht Euch vom Acker/Spamerzy wypierdalac'

  4. Re: SQL Index Problem

    On Oct 15, 8:37*am, "Dr.UgoGagliardelli"
    wrote:
    > il 14/10/2008 19.26, Scrive iseriesflorida 43542568:
    >
    >
    >
    > > I have a physical file with 3 records in it.

    >
    > > I am creating this SQL code against it and then running runsqlstm, it
    > > creates the index.

    >
    > > *CREATE INDEX TESTPFT
    > > * *ON TESTPF (ZIP DESC)

    >
    > > The records are this:

    >
    > > *78985
    > > *68191
    > > *98989

    >
    > > When I query TESTPFT they are not in the order of 98989 then 78985
    > > then 68191, what am I doing wrong.

    >
    > An index doesn't necessarily sort records, it's different than putting K
    > fields on DDS, then if you use RUNQRY it will always use the order it
    > found in the physical file, as sql SELECT does unless you specify ORDER
    > BY clause. You can reorganize physical file records by ZIP and DESC keys
    > using:
    > RGZPFM FILE(TESTPF) KEYFILE(TESTPFT)
    >
    > --
    > Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAñe joAlcoolInside
    > Spaccamaroni andate a cagare/Spammers not welcome/Spammers vão à merda
    > Spamers iros a la mierda/Spamers allez vous faire foutre/Spammers loop
    > schijten/Spammers macht Euch vom Acker/Spamerzy wypierdalac'- Hide quotedtext -
    >
    > - Show quoted text -


    Ugo, can you elaborate then on what an index buys you since you can
    still sort on a field using SQL.

  5. Re: SQL Index Problem

    il 15/10/2008 14.51, Scrive iseriesflorida 43542568:
    > On Oct 15, 8:37 am, "Dr.UgoGagliardelli"
    > wrote:
    >> il 14/10/2008 19.26, Scrive iseriesflorida 43542568:
    >>
    >>
    >>
    >>> I have a physical file with 3 records in it.
    >>> I am creating this SQL code against it and then running runsqlstm, it
    >>> creates the index.
    >>> CREATE INDEX TESTPFT
    >>> ON TESTPF (ZIP DESC)
    >>> The records are this:
    >>> 78985
    >>> 68191
    >>> 98989
    >>> When I query TESTPFT they are not in the order of 98989 then 78985
    >>> then 68191, what am I doing wrong.

    >> An index doesn't necessarily sort records, it's different than putting K
    >> fields on DDS, then if you use RUNQRY it will always use the order it
    >> found in the physical file, as sql SELECT does unless you specify ORDER
    >> BY clause. You can reorganize physical file records by ZIP and DESC keys
    >> using:
    >> RGZPFM FILE(TESTPF) KEYFILE(TESTPFT)
    >>
    >> --
    >> Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAñe joAlcoolInside
    >> Spaccamaroni andate a cagare/Spammers not welcome/Spammers vão à merda
    >> Spamers iros a la mierda/Spamers allez vous faire foutre/Spammers loop
    >> schijten/Spammers macht Euch vom Acker/Spamerzy wypierdalac'- Hide quoted text -
    >>
    >> - Show quoted text -

    >
    > Ugo, can you elaborate then on what an index buys you since you can
    > still sort on a field using SQL.

    Sorry, I'm not english, so I cannot recognize your statement neither as
    a question nor an assertion.
    Can you put some punctuation somewhere? Maybe it will help!
    :-))

    --
    Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAñe joAlcoolInside
    Spaccamaroni andate a cagare/Spammers not welcome/Spammers vão à merda
    Spamers iros a la mierda/Spamers allez vous faire foutre/Spammers loop
    schijten/Spammers macht Euch vom Acker/Spamerzy wypierdalac'

  6. Re: SQL Index Problem

    > Ugo, can you elaborate then on what an index buys you since you can
    > still sort on a field using SQL.


    The index sorts the file in advance, otherwise ORDER BY causes a sort
    to happen while your SELECT stament is executing, which could cause
    your SELECT to run for a long time if large numbers of records are
    involved.

  7. Re: SQL Index Problem

    Dr.UgoGagliardelli wrote:
    > il 14/10/2008 19.26, Scrive iseriesflorida 43542568:
    >> I have a physical file with 3 records in it.
    >>
    >> I am creating this SQL code against it and then running runsqlstm, it
    >> creates the index.
    >>
    >> CREATE INDEX TESTPFT
    >> ON TESTPF (ZIP DESC)
    >>
    >> The records are this:
    >>
    >> 78985
    >> 68191
    >> 98989
    >>
    >> When I query TESTPFT they are not in the order of 98989 then 78985
    >> then 68191, what am I doing wrong.
    >>

    > An index doesn't necessarily sort records, it's different than putting K
    > fields on DDS, then if you use RUNQRY it will always use the order it
    > found in the physical file, as sql SELECT does unless you specify ORDER
    > BY clause. You can reorganize physical file records by ZIP and DESC keys
    > using:
    > RGZPFM FILE(TESTPF) KEYFILE(TESTPFT)
    >
    >


    I've seen ORDER BY discussed quite often on usenet, and believe that
    when ORDER BY is not present, the RDB manager is not obligated to return
    results in any predictable order. I don't have a SQL cite offhand, but
    here is an excerpt from Wikipedia:

    http://en.wikipedia.org/wiki/Order_by

    "ORDER BY is the only way to sort the rows in the result set.
    Without this clause, the relational database system may return
    the rows in any order."

    It may be that, depending on how some DB2 for i queries are implemented,
    results are returned in row order of a physical file, but applications
    should not depend on that. As Birgitta said, if you need results in a
    predefined sequence/order, use the ORDER BY clause.

    --
    Karl Hanson

  8. Re: SQL Index Problem

    On Oct 15, 10:29*am, "walker.l2" wrote:
    > > Ugo, can you elaborate then on what an index buys you since you can
    > > still sort on a field using SQL.

    >
    > The index sorts the file in advance, otherwise ORDER BY causes a sort
    > to happen while your SELECT stament is executing, which could cause
    > your SELECT to run for a long time if large numbers of records are
    > involved.


    Just trying to gain a better understanding, if I had a physical with
    70,000,000 million records and an index built over it how could I see
    how long the eventual SQL would run, can I do this and if so would I
    use the index analzyer or ??

  9. Re: SQL Index Problem

    il 15/10/2008 16.37, Scrive Karl Hanson 43542568:
    > Dr.UgoGagliardelli wrote:
    >> il 14/10/2008 19.26, Scrive iseriesflorida 43542568:
    >>> I have a physical file with 3 records in it.
    >>>
    >>> I am creating this SQL code against it and then running runsqlstm, it
    >>> creates the index.
    >>>
    >>> CREATE INDEX TESTPFT
    >>> ON TESTPF (ZIP DESC)
    >>>
    >>> The records are this:
    >>>
    >>> 78985
    >>> 68191
    >>> 98989
    >>>
    >>> When I query TESTPFT they are not in the order of 98989 then 78985
    >>> then 68191, what am I doing wrong.
    >>>

    >> An index doesn't necessarily sort records, it's different than putting
    >> K fields on DDS, then if you use RUNQRY it will always use the order
    >> it found in the physical file, as sql SELECT does unless you specify
    >> ORDER BY clause. You can reorganize physical file records by ZIP and
    >> DESC keys using:
    >> RGZPFM FILE(TESTPF) KEYFILE(TESTPFT)
    >>
    >>

    >
    > I've seen ORDER BY discussed quite often on usenet, and believe that
    > when ORDER BY is not present, the RDB manager is not obligated to return
    > results in any predictable order. I don't have a SQL cite offhand, but
    > here is an excerpt from Wikipedia:
    >
    > http://en.wikipedia.org/wiki/Order_by
    >
    > "ORDER BY is the only way to sort the rows in the result set.
    > Without this clause, the relational database system may return
    > the rows in any order."
    >
    > It may be that, depending on how some DB2 for i queries are implemented,
    > results are returned in row order of a physical file, but applications
    > should not depend on that. As Birgitta said, if you need results in a
    > predefined sequence/order, use the ORDER BY clause.
    >
    > --
    > Karl Hanson

    I assume that without ORDER BY records will be retrieved by arrival
    sequence, that should be the cheapest method that RDB may use.
    RGZPFM with KEYFILE will sort the arrival sequence by the key specified,
    so the arrival sequence will return sorted rows (for a short while, at
    least until new records will be appended). The side effect can be that
    "order by" the same key, will give a better performance.

    --
    Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAñe joAlcoolInside
    Spaccamaroni andate a cagare/Spammers not welcome/Spammers vão à merda
    Spamers iros a la mierda/Spamers allez vous faire foutre/Spammers loop
    schijten/Spammers macht Euch vom Acker/Spamerzy wypierdalac'

  10. Re: SQL Index Problem

    > I assume that without ORDER BY records will be retrieved by arrival
    > sequence, that should be the cheapest method that RDB may use.


    I had to debug a problem recently in a program that relied on this.
    The presence of a WHERE condition caused the query to use an index
    which happened to sort the records in a different sequence (not
    arrival sequence).

  11. Re: SQL Index Problem

    On Oct 15, 12:05*pm, "walker.l2" wrote:
    > > I assume that without ORDER BY records will be retrieved by arrival
    > > sequence, that should be the cheapest method that RDB may use.

    >
    > I had to debug a problem recently in a program that relied on this.
    > The presence of a WHERE condition caused the query to use an index
    > which happened to sort the records in a different sequence (not
    > arrival sequence).


    If your not RGZPFM on files that use indexing would this cause more
    search time, if you RGZPFM the file does the index get rebuilt on the
    fly same as a LF??

  12. Re: SQL Index Problem

    il 15/10/2008 20.05, Scrive iseriesflorida 43170816:
    > On Oct 15, 12:05 pm, "walker.l2" wrote:
    >>> I assume that without ORDER BY records will be retrieved by arrival
    >>> sequence, that should be the cheapest method that RDB may use.

    >> I had to debug a problem recently in a program that relied on this.
    >> The presence of a WHERE condition caused the query to use an index
    >> which happened to sort the records in a different sequence (not
    >> arrival sequence).

    >
    > If your not RGZPFM on files that use indexing would this cause more
    > search time, if you RGZPFM the file does the index get rebuilt on the
    > fly same as a LF??

    No, RGZPFM only reorganize the physical data member, eliminating deleted
    records and rebuil the arrival sequence basing on the key lf if you
    supply one.


    --
    Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAñe joAlcoolInside
    Spaccamaroni andate a cagare/Spammers not welcome/Spammers vão à merda
    Spamers iros a la mierda/Spamers allez vous faire foutre/Spammers loop
    schijten/Spammers macht Euch vom Acker/Spamerzy wypierdalac'

  13. Re: SQL Index Problem

    Dr.UgoGagliardelli wrote:
    > il 15/10/2008 16.37, Scrive Karl Hanson 43542568:
    >> Dr.UgoGagliardelli wrote:
    >>> il 14/10/2008 19.26, Scrive iseriesflorida 43542568:
    >>>> I have a physical file with 3 records in it.
    >>>>
    >>>> I am creating this SQL code against it and then running runsqlstm, it
    >>>> creates the index.
    >>>>
    >>>> CREATE INDEX TESTPFT
    >>>> ON TESTPF (ZIP DESC)
    >>>>
    >>>> The records are this:
    >>>>
    >>>> 78985
    >>>> 68191
    >>>> 98989
    >>>>
    >>>> When I query TESTPFT they are not in the order of 98989 then 78985
    >>>> then 68191, what am I doing wrong.
    >>>>
    >>> An index doesn't necessarily sort records, it's different than
    >>> putting K fields on DDS, then if you use RUNQRY it will always use
    >>> the order it found in the physical file, as sql SELECT does unless
    >>> you specify ORDER BY clause. You can reorganize physical file records
    >>> by ZIP and DESC keys using:
    >>> RGZPFM FILE(TESTPF) KEYFILE(TESTPFT)
    >>>
    >>>

    >>
    >> I've seen ORDER BY discussed quite often on usenet, and believe that
    >> when ORDER BY is not present, the RDB manager is not obligated to
    >> return results in any predictable order. I don't have a SQL cite
    >> offhand, but here is an excerpt from Wikipedia:
    >>
    >> http://en.wikipedia.org/wiki/Order_by
    >>
    >> "ORDER BY is the only way to sort the rows in the result set.
    >> Without this clause, the relational database system may return
    >> the rows in any order."
    >>
    >> It may be that, depending on how some DB2 for i queries are
    >> implemented, results are returned in row order of a physical file, but
    >> applications should not depend on that. As Birgitta said, if you need
    >> results in a predefined sequence/order, use the ORDER BY clause.
    >>
    >> --
    >> Karl Hanson

    > I assume that without ORDER BY records will be retrieved by arrival
    > sequence, that should be the cheapest method that RDB may use.
    > RGZPFM with KEYFILE will sort the arrival sequence by the key specified,
    > so the arrival sequence will return sorted rows (for a short while, at
    > least until new records will be appended). The side effect can be that
    > "order by" the same key, will give a better performance.
    >


    Here is information from a DB2 for i Query Optimizer expert:

    If the select statement doesn't designate ordering via ORDER BY, the
    optimizer is free to return the results in any order it
    chooses...regardless of RGZPFM or even existing indexes.

    RGZPFM is not something that makes sense for trying to get ordering...
    (at best) it would be fleeting and again, still no guarantee on a select
    that the optimizer would use arrival sequence.

    --
    Karl Hanson

  14. Re: SQL Index Problem

    il 15/10/2008 21.26, Scrive Karl Hanson 43711712:
    > Dr.UgoGagliardelli wrote:
    >> il 15/10/2008 16.37, Scrive Karl Hanson 43542568:
    >>> Dr.UgoGagliardelli wrote:
    >>>> il 14/10/2008 19.26, Scrive iseriesflorida 43542568:
    >>>>> I have a physical file with 3 records in it.
    >>>>>
    >>>>> I am creating this SQL code against it and then running runsqlstm, it
    >>>>> creates the index.
    >>>>>
    >>>>> CREATE INDEX TESTPFT
    >>>>> ON TESTPF (ZIP DESC)
    >>>>>
    >>>>> The records are this:
    >>>>>
    >>>>> 78985
    >>>>> 68191
    >>>>> 98989
    >>>>>
    >>>>> When I query TESTPFT they are not in the order of 98989 then 78985
    >>>>> then 68191, what am I doing wrong.
    >>>>>
    >>>> An index doesn't necessarily sort records, it's different than
    >>>> putting K fields on DDS, then if you use RUNQRY it will always use
    >>>> the order it found in the physical file, as sql SELECT does unless
    >>>> you specify ORDER BY clause. You can reorganize physical file
    >>>> records by ZIP and DESC keys using:
    >>>> RGZPFM FILE(TESTPF) KEYFILE(TESTPFT)
    >>>>
    >>>>
    >>>
    >>> I've seen ORDER BY discussed quite often on usenet, and believe that
    >>> when ORDER BY is not present, the RDB manager is not obligated to
    >>> return results in any predictable order. I don't have a SQL cite
    >>> offhand, but here is an excerpt from Wikipedia:
    >>>
    >>> http://en.wikipedia.org/wiki/Order_by
    >>>
    >>> "ORDER BY is the only way to sort the rows in the result set.
    >>> Without this clause, the relational database system may return
    >>> the rows in any order."
    >>>
    >>> It may be that, depending on how some DB2 for i queries are
    >>> implemented, results are returned in row order of a physical file,
    >>> but applications should not depend on that. As Birgitta said, if you
    >>> need results in a predefined sequence/order, use the ORDER BY clause.
    >>>
    >>> --
    >>> Karl Hanson

    >> I assume that without ORDER BY records will be retrieved by arrival
    >> sequence, that should be the cheapest method that RDB may use.
    >> RGZPFM with KEYFILE will sort the arrival sequence by the key
    >> specified, so the arrival sequence will return sorted rows (for a
    >> short while, at least until new records will be appended). The side
    >> effect can be that "order by" the same key, will give a better
    >> performance.
    >>

    >
    > Here is information from a DB2 for i Query Optimizer expert:
    >
    > If the select statement doesn't designate ordering via ORDER BY, the
    > optimizer is free to return the results in any order it
    > chooses...regardless of RGZPFM or even existing indexes.
    >
    > RGZPFM is not something that makes sense for trying to get ordering...
    > (at best) it would be fleeting and again, still no guarantee on a select
    > that the optimizer would use arrival sequence.

    I agree, even if the job of Query Optimizer is *free* to serve rows
    chosing the best cost available, so having it find the cheapest one, it
    *should* chose it. I remark *should*, meaning that there is a reasonable
    *hope*.
    --
    Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAñe joAlcoolInside
    Spaccamaroni andate a cagare/Spammers not welcome/Spammers vão à merda
    Spamers iros a la mierda/Spamers allez vous faire foutre/Spammers loop
    schijten/Spammers macht Euch vom Acker/Spamerzy wypierdalac'

  15. Re: SQL Index Problem

    If no order by clause is specified, the optimizer uses the sequence of
    the index it decided to use.
    If a table scan is used instead, the rows may be returned in arrival
    sequence.

    The index the optimizer uses may not contain the rows in the expected
    sequence.
    The optimizer always takes the index that allows it to access the
    desired data in the fastest way.

    If you need the result in a predefined sequence, you need an order
    by.
    If an order by clause is specified and an index with a different
    sequence is used, a temporary object will be built and the selected
    data sorted after.

    Building those temporary objects and doing an extra sorting, may also
    cost performance.
    In this way, only use an order by if you really need it.

    Birgitta

+ Reply to Thread