Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Nov 2007
    Posts
    8

    Question Unanswered: How to eliminate the Sorting when order by class used.

    I have created unidex on column with allow revers scans.even though it is sorting . i come to know this by show execution plan command.

    Anyone have idea how to eliminate sort while using order by clause...

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Have an index that covers the order by clause. But this may not make the overall query any faster.

    Andy

  3. #3
    Join Date
    Nov 2007
    Posts
    8
    sorry i forget to mention that i'm using ordey column desc.
    i want to fetch last 10 rows.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Does not matter. If you have an index that covers the ORDER BY clause then the sort maybe skipped. Otherwise there will need to be a sort.

    Andy

  5. #5
    Join Date
    Nov 2007
    Posts
    8
    I have created Unique index on that column(column is primary key and identity type).

    ran the runstats command on table and index all.

    even though it is sorting.

    Please suggest me if i missed anything here.

    Thanks.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You will need to supply the DDL for the table and index. You will also need to supply the query and the access plan.

    Andy

  7. #7
    Join Date
    Nov 2007
    Posts
    8
    Intailly table is created like this
    ---------------------------------------------

    CREATE TABLE SAM.SECURITY_AUDITS (
    SECURITY_AUDIT_ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 20) NOT NULL,
    USER_ID INTEGER,
    ACTIVITY_TYPE_ID INTEGER NOT NULL,
    TIMESTAMP DATE DEFAULT NULL,
    SECURITY_STATUS_ID INTEGER NOT NULL,
    DESCRIPTION VARCHAR(128) DEFAULT NULL,
    PERFORMED_BY VARCHAR(20) NOT NULL,
    AUDIT_TIME TIMESTAMP NOT NULL,
    APPLICATION_ID INTEGER NOT NULL DEFAULT 0,
    ENTITY VARCHAR(80),
    EVENT_ID INTEGER,
    CONTAINER_ID INTEGER,
    PRIMARY KEY(SECURITY_AUDIT_ID)
    )
    GO
    ALTER TABLE SAM.SECURITY_AUDITS
    ADD CONSTRAINT SQL050527110010740
    FOREIGN KEY(USER_ID)
    REFERENCES SAM.USERS(USER_ID)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    GO
    ALTER TABLE SAM.SECURITY_AUDITS
    ADD CONSTRAINT FK_SEC_ADT_CONT_ID
    FOREIGN KEY(CONTAINER_ID)
    REFERENCES SAM.SECURITY_CONTAINER(CONTAINER_ID)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    GO
    ALTER TABLE SAM.SECURITY_AUDITS
    ADD CONSTRAINT FK_EVENT
    FOREIGN KEY(EVENT_ID)
    REFERENCES SAM.EVENT(EVENT_ID)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    GO
    CREATE INDEX SAM.SEC_AUD_EVT_ID_IDX
    ON SAM.SECURITY_AUDITS(EVENT_ID)
    ALLOW REVERSE SCANS
    GO

    --------------------------------------------

    After that i have removed primary key constarint and added Unique index.

    ALTER TABLE SAM.SECURITY_AUDITS DROP CONSTRAINT SQL080314160000070

    CREATE UNIQUE INDEX VNM.TEST_SEC_IDX
    ON SAM.SECURITY_AUDITS(SECURITY_AUDIT_ID)
    PCTFREE 10
    ALLOW REVERSE SCANS

    --------------------------------------------------------------------
    After that i ran runstats command..

    I took long time for completion. can you please tell me reason..if you have idea why it took long time(more than 2hours)?

    ----------------------------------------------------
    The query i'm executing is :

    select *
    from SAM.SECURITY_AUDITS securityau0_
    order by SECURITY_AUDIT_ID desc
    fetch first 10 rows only

    ----------------------------------

    In the execution explain it is showing as tablescan->sort->tablescan.


    Thanks.

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Yep, for that query, that is the access plan I would expect. The fetch fist 10 rows will not be evaluated until the end, and with no predicate, you will get a tablescan. The order by will cause the sort, then the fetch first will do the last tablescan. Unless you rewrite the query, you are not going to get anything better.

    Andy

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Sunil_kondaveeti1
    After that i have removed primary key constarint and added Unique index.

    ALTER TABLE SAM.SECURITY_AUDITS DROP CONSTRAINT SQL080314160000070

    CREATE UNIQUE INDEX VNM.TEST_SEC_IDX
    ON SAM.SECURITY_AUDITS(SECURITY_AUDIT_ID)
    PCTFREE 10
    ALLOW REVERSE SCANS
    Why did you do that? A unique constraint (a PK is just that) is already implemented by a unique index. So this seems to be work for no good reason. If it is intended to get the PCTFREE and the ALLOW REVERSE SCANS option in there, don't create the PK on the table in the CREATE TABLE statement. Create the table, then the index, then add the PK constraint.

    I took long time for completion. can you please tell me reason..if you have idea why it took long time(more than 2hours)?
    How should we know? But here is a wild guess: You have lots of data? And you collected lots of detailed information?

    The query i'm executing is :

    select *
    from SAM.SECURITY_AUDITS securityau0_
    order by SECURITY_AUDIT_ID desc
    fetch first 10 rows only

    ----------------------------------

    In the execution explain it is showing as tablescan->sort->tablescan.
    You care to join with us which version of DB2 you are using on which platform? Also, what's the exact query plan (if you are using DB2 LUW, then you can get a textual version with the "db2expln" tool)?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Nov 2007
    Posts
    8
    please let me know any tweaks to query would elimante the sort..

    Why did you do that? A unique constraint (a PK is just that) is already implemented by a unique index. So this seems to be work for no good reason. If it is intended to get the PCTFREE and the ALLOW REVERSE SCANS option in there, don't create the PK on the table in the CREATE TABLE statement. Create the table, then the index, then add the PK constraint.
    I thought if allow reverse scans used , index will used even when we specify oredr by desc.

    Please correct me if i'm wrong.
    I took long time for completion. can you please tell me reason..if you have idea why it took long time(more than 2hours)?
    I have run below command

    db2 runstats on table SNM.SECURITY_AUDITS and indexes all

    it has 19K rows
    You care to join with us which version of DB2 you are using on which platform? Also, what's the exact query plan (if you are using DB2 LUW, then you can get a textual version with the "db2expln" tool)?
    DB2 - 8.2 running on Sun solaris 5.8

    Query plan is
    -------------
    bash-2.03$ db2expln -d ONMDB -t -q "select * from SNM.SECURITY_AUDITS securityau0_ order by SECURITY_AUDIT_ID desc fetch first 10 rows only"

    DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
    Licensed Material - Program Property of IBM
    IBM DB2 Universal Database SQL Explain Tool

    DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
    Licensed Material - Program Property of IBM
    IBM DB2 Universal Database SQL Explain Tool

    ******************** DYNAMIC ***************************************

    ==================== STATEMENT ==========================================

    Isolation Level = Cursor Stability
    Blocking = Block Unambiguous Cursors
    Query Optimization Class = 5

    Partition Parallel = No
    Intra-Partition Parallel = No

    SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "DB2INST1"


    SQL Statement:

    select *
    from SNM.SECURITY_AUDITS securityau0_
    order by SECURITY_AUDIT_ID desc
    fetch first 10 rows only


    Section Code Page = 819

    Estimated Cost = 1915.921143
    Estimated Cardinality = 10.000000

    Access Table Name = SNM.SECURITY_AUDITS ID = 4,1037
    | #Columns = 12
    | Relation Scan
    | | Prefetch: Eligible
    | Lock Intents
    | | Table: Intent Share
    | | Row : Next Key Share
    | Sargable Predicate(s)
    | | Insert Into Sorted Temp Table ID = t1
    | | | #Columns = 12
    | | | #Sort Key Columns = 1
    | | | | Key 1: SECURITY_AUDIT_ID (Descending)
    | | | Sortheap Allocation Parameters:
    | | | | #Rows = 10
    | | | | Row Width = 100
    | | | | Sort Limited To Estimated Row Count
    | | | Piped
    Sorted Temp Table Completion ID = t1
    Access Temp Table ID = t1
    | #Columns = 12
    | Relation Scan
    | | Prefetch: Eligible
    Return Data to Application
    | #Columns = 12

    End of section

  11. #11
    Join Date
    Nov 2007
    Posts
    8
    Andy,

    Please let me know if you have any suggestions to tweak the query

  12. #12
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Sunil_kondaveeti1
    please let me know any tweaks to query would elimante the sort..
    I really don't know why you bother about this...

    You have a really small table - I did some math here: 19K rows and each row has about 200 bytes on average, right? That means, all the data fits on 1000 pages. Going from there, DB2 has to read 100 pages + index pages to find the first 10 rows. Depending on the actual statistics, the ratio between data pages and index pages gets worse.

    Also, sorting 19k rows is not really worth thinking about. It is a really fast operation. If you run db2expln with the -g and -i options (I'm not 100% sure on the -i) to get the graph and the costs on each operator, you should see that the sort does not add a significant amount to the costs.

    Have you actually measured the execution times of the query and determined if it takes too long?

    I thought if allow reverse scans used , index will used even when we specify oredr by desc.
    Right. But why create the PK in the CREATE TABLE if you drop it afterwards again? That's just wasted effort.

    Anyway, the DB2 optimizer is a cost-based optimizer. It generates many different plans and estimates the costs for each. In the end, DB2 chooses the plan with the least cost. So DB2 will also cost the plan with index-based access, and it found this to be more expensive than a straight-forward table scan.

    You could try to verify that DB2 would choose an index-based or even index-only access by creating an index that includes all you really need in the application.

    Doing a "SELECT *" is usually only showing that you are doing something wrong because you don't know what you want to do with the data. Explicitly list only those columns in the SELECT list that you really need.

    If you want to start tuning, you should run the DB2 Configuration Advisor first in order to get a good base line for your system. Then you can start tuning queries.

    Also, collecting statistics should not take than long on such a few rows. A few seconds would be fine (assuming that you don't have any application running that takes locks on the data in question.) You should apply the latest FixPak and then see if the problem persists contact IBM support.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  13. #13
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by Sunil_kondaveeti1
    Andy,

    Please let me know if you have any suggestions to tweak the query
    How many rows are in the table?

    Andy

  14. #14
    Join Date
    Nov 2007
    Posts
    8
    How many rows are in the table?

    Andy
    Currently it has 19k rows..but in future it will grow enormously.
    So i need to consider that..thats why i'm trying here to elimeinate sort..


    Right. But why create the PK in the CREATE TABLE if you drop it afterwards again? That's just wasted effort.
    There 2 ways to find the latest rows .one is timestamp column and other one is SECURITY_AUDIT_ID column(PK and Identity type)...

    Since it is not meaingful to create the index on timestamp column, i chosen SECURITY_AUDIT_ID column .

    Since we can't create the allow reverse scans on primary key column,i dropped the primary key constraint and creted unique index on that column..

    please let me know if i'm wrong here..

    Thanks.

  15. #15
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Sunil_kondaveeti1
    Currently it has 19k rows..but in future it will grow enormously.
    So i need to consider that..thats why i'm trying here to elimeinate sort..
    You won't expect DB2 to choose an access plan for just 19K rows and then assume that this access plan would be the best for 19 million rows, would you?

    Again, DB2 is a cost-based optimizer and tries to find the best access plan for the query and the amount of data that the query will process. For example, if you have a list of 10 words and you try to look up a single given word repeatedly, you usually will not sort those 10 words and just look through them one by one on each request. But if you have 1000 words, you will sort all words once, and at query time do a binary search to be faster. Same thing applies your your scenario...

    You should fake the statistics so that the DB2 optimizer assumes it has to read many more rows than just the few you have. That would give you a much more reasonable access plan.

    Since it is not meaingful to create the index on timestamp column, i chosen SECURITY_AUDIT_ID column .
    Why wouldn't an index on the timestamp column be "meaningful"? Could you elaborate on that? Your statement doesn't make any sense to me, unless you worry about having multiple entries with exactly the same timestamp.

    Since we can't create the allow reverse scans on primary key column,i dropped the primary key constraint and creted unique index on that column..
    You are missing my point. You do not have to create the primary key constraint when you create the table. Just create the constraint after you create the table and the explicit index:
    Code:
    $ db2 "CREATE TABLE t ( a INT NOT NULL )"
    DB20000I  The SQL command completed successfully.
    
    $ db2 "CREATE UNIQUE INDEX t_pk_idx ON t ( a ) ALLOW REVERSE SCANS"
    DB20000I  The SQL command completed successfully.
    
    $ db2 "ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY ( a )"
    SQL0598W  Existing index "STOLZE.T_PK_IDX" is used as the index for the primary key or a unique key.  SQLSTATE=01550
    The last SQL0598W is just a warning telling you that no new index was created but rather the existing one reused to enforce the primary key.

    As you can see, no index is created as part of the CREATE TABLE statement and you can still use whichever index properties you want to have.
    Last edited by stolze; 03-18-08 at 18:42.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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