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...

Have an index that covers the order by clause. But this may not make the overall query any faster.

Andy

sorry i forget to mention that i'm using ordey column desc.
i want to fetch last 10 rows.

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

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.

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

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.

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

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.

How should we know? But here is a wild guess: You have lots of data? And you collected lots of detailed information?

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)?

please let me know any tweaks to query would elimante the sort..

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 have run below command

db2 runstats on table SNM.SECURITY_AUDITS and indexes all

it has 19K rows
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

Andy,

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

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?

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.

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..


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.

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.

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.

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:
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.