How many times during testing of a new service or web interface, you get angry because low performance with large amount of data? Stop the panic, revise your checklist and in particular,
ask to this question, are you 100% sure all the queries are the "where" and "order by" clauses using columns that have a defined index? When if you are not sure, first check it.
On DB2 you can run a simple command:
db2 connect to MYDB
db2 SELECT INDNAME,COLNAMES FROM SYSCAT.INDEXES WHERE TABNAME='mytable' AND TABSCHEMA='myschema'
Check the results on the COLNAMES looking for yours. If any column is not present, then evaluate to add a new index. But before do this make sure your application does not perform very large UPDATE statements on that table, otherwise you will have slower UPDATE statements because there is this additional index to maintain up-to-date. So maintaining a new index has a CPU cost, but also a footprint cost. On OLTP systems you should have less indexes respect to a data warehouse system.
To create a simple index on a single column for example run:
db2 "CREATE INDEX myschema.myindex ON myschema.mytable (mycolname DESC) COLLECT STATISTICS COMPRESS NO ALLOW REVERSE SCANS"
To get the benefit of this new index you need to update the statistics with the new index:
db2 "RUNSTATS ON TABLE myschema.mytable WITH DISTRIBUTION AND DETAILED INDEX myschema.myindex"
Now verify if your queries and the entire system got an improvement by this new index.
An administrative activity is sometime necessary to reorganize physically the pages of the table and of its indexes to not-fragmented and continuous pages:
db2 "REORG TABLE myschema.mytable"
db2 "REORG INDEXES ALL FOR TABLE myschema.mytable"
or for the single index:
db2 "REORG INDEX myschema.myindex FOR TABLE myschema.mytable"
In this post I've not talked of indexes defined for multiple column. More details in the DB2 reference. Look also at this IBM article for suggestions when creating indexes:
http://www.ibm.com/developerworks/data/library/dmmag/DMMag_2010_Issue4/DataArchitect/
DB2 references below.
CREATE INDEX command:
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.dbobj.doc/doc/t0020190.html
RUNSTATS command:
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001980.html?cp=SSEPGG_9.7.0%2F3-6-2-4-108&lang=en
REORG command:
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001966.html?cp=SSEPGG_9.7.0%2F3-6-2-4-96
Nessun commento:
Posta un commento