Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Posts
    2

    Unanswered: Query Plan Evaluation Headache

    Hey Everyone-

    I have an interesting one that popped up from a situation with an old product (and of course the people who would have the answer off the top of their head are no longer with us).

    The is in Sybase 12.5 SWR 10237 ESD#1 and happens in AIX 4.3.3, AIX 5.1, and Solaris 8

    Here are the queries I'm looking at:

    select max(change_time) from genhist where table_sys_id=117

    -and-

    select max(change_time) from genhist where table_sys_id=139


    The first query evaluates as I would expect. Here is the showplan:

    -- select max(change_time) from genhist where table_sys_id=117 --
    QUERY PLAN FOR STATEMENT 1 (at line 2).


    STEP 1
    The type of query is SELECT.
    Evaluate Ungrouped MAXIMUM AGGREGATE.

    FROM TABLE
    genhist
    Nested iteration.
    Index : genhist_tblsysid_chgtime_idx
    Forward scan.
    Positioning by key.
    Index contains all needed columns. Base table will not be read.
    Keys are:
    table_sys_id ASC
    Using I/O Size 2 Kbytes for index leaf pages.
    With LRU Buffer Replacement Strategy for index leaf pages.

    STEP 2
    The type of query is SELECT.
    ----------

    Note the index and the key. This is exactly how I would expect the plan to look.

    Now take a look at the plan for the other query:

    -- select max(change_time) from genhist where table_sys_id=139 --
    QUERY PLAN FOR STATEMENT 1 (at line 2).


    STEP 1
    The type of query is SELECT.
    Evaluate Ungrouped MAXIMUM AGGREGATE.

    FROM TABLE
    genhist
    Nested iteration.
    Index : genhist_time_chgevent_idx
    Backward scan.
    Positioning at index end.
    Scanning only up to the first qualifying row.
    Using I/O Size 2 Kbytes for index leaf pages.
    With LRU Buffer Replacement Strategy for index leaf pages.
    ----------

    Note that the index chose here is different, and no key either.

    The only difference in the queries is the value at the end (117 vs 139). Anyone have any ideas why the plans would be so different?

    dbcc checkstorage reports no errors. Also, dbcc tablealloc on that table is clean, as well as dbcc indexalloc on the four indexes on that table.


    Any thoughts would be appreciated!!

    >Dan<

  2. #2
    Join Date
    Jun 2004
    Location
    Paris, France
    Posts
    43
    how many table_sys_id have you got in your table genhist ?
    if the server option "allow backward scan" is on, and that the table_sys #139 is on a vary last page, the optimizer can choose to read the index from the end.
    (maybe ?)

    otherwise, you can try to reindex your table, or to reorganise it... it may be usefull

    err1

    ps : if someone has a clue for my yesterday's post... it should be really helpfull

  3. #3
    Join Date
    Jun 2004
    Location
    Paris, France
    Posts
    43
    how many table_sys_id have you got in your table genhist ?
    if the server option "allow backward scan" is on, and that the table_sys #139 is on a very last page, the optimizer can choose to read the index from the end.
    (maybe ?)

    otherwise, you can try to reindex your table, or to reorganise it... it may be usefull

    err1

    ps : if someone has a clue for my yesterday's post... it should be really helpfull

  4. #4
    Join Date
    Aug 2004
    Posts
    2
    err1-

    There are just under 7 million rows in that table, and just over 600 unique table_sys_ids.

    Also in the mix are that there are zero rows in this case for both ids 117 and 139.


    Thanks
    >Dan<

  5. #5
    Join Date
    Jun 2004
    Location
    Paris, France
    Posts
    43

    yep...
    with these informations, your problem becomes more... problematic

    have you tried the reorg command ? or the recreation of your indexes ?
    with 11.9.2 we had a similar problem, but it was more a bug than something else... we had to set the option "allow backward scan" at off.

  6. #6
    Join Date
    Aug 2004
    Posts
    42
    It appears from your index name that the index is (table_sys_id, change_time,...). If so, then something like

    set rowcount 1

    select change_date
    from genhist (index genhist_tblsysid_chgtime_idx)
    where table_sys_id = 139
    and change_date <= "12/31/9999"
    order by table_sys_id desc, change_date desc

    set rowcount 0

    might work. When I tried this -- on a table with just the two columns and not many rows -- I get a showplan of

    STEP 1
    The type of query is SELECT.

    FROM TABLE
    genhist
    Nested iteration.
    Using Clustered Index.
    Index : genhist_tblsysid_chgtime_idx
    Backward scan.
    Positioning by key.
    Keys are:
    table_sys_id ASC
    change_date ASC
    Using I/O Size 2 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.

    A backward scan -- but it uses both parts of the index and reads just 1 row and the minimum number of pages.

    Note: to get it to do this I had to have ALL of these "hints":

    1. Force the index
    2. Have the order by on both elements, both desc.
    3. Have the artificial constraint 'AND change_date <= "12/31/9999"'.

    A little (a lot?) wacky, but....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •