 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / DB2 Topics / March 2006Query regarding Index
Suppose i have a table which holds thousands of records with the following structure CREATE TABLE "test "."T_CNTRY" ( "CNTRY_CDE" CHAR(2) NOT NULL , "CNTRY_NAME" VARCHAR(50) ) and i have Created an index like below :: CREATE UNIQUE INDEX "testI "."Uindex1" ON "test "."T_CNTRY" ("CNTRY_CDE" ASC); 1.) Do i have to create an Index like this to make queries which make use of this run faster CREATE INDEX "testI "."Iindex1" ON "test "."T_CNTRY" ("CNTRY_CDE" ASC); OR 2.) Creating the unique index/primary key index will serve the purpose of making queries run faster thanks in advance > Suppose i have a table which holds thousands of records with the > following structure [quoted text clipped - 18 lines] > 2.) Creating the unique index/primary key index will serve the purpose > of making queries run faster A UNIQUE index is an INDEX + UNIQUEness. DB2 knows that and will tell you: db2 => create table t(c1 int not null); DB20000I The SQL command completed successfully. db2 => create unique index i1 on t(c1); DB20000I The SQL command completed successfully. db2 => create index i2 on t(c1); SQL0605W The index was not created because an index "SRIELAU.I1" with a matching definition already exists. SQLSTATE=01550 As you see, DB2 figured that the second index is not needed. Cheers Serge  SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab hhmmmm... 1.)this means i need not create a primary key when I have already defined a UNIQUE + INDEX like what i have tested before. CREATE UNIQUE INDEX "test"."IAWD_TYP01" ON "DB2GRA "."T_TYP" ("AWD_TYP_CDE" ASC) CLUSTER DB20000I The SQL command completed successfully. ALTER TABLE "test"."T__TYP" ADD CONSTRAINT "PK_AWD_TYP" PRIMARY KEY ("AWD_TYP_CDE") SQL0598W Existing index "DB2GRA.IAWD_TYP01" is used as the index for the primary key or a unique key. SQLSTATE=01550 2.) Does that mean that I can ask the Data Modeler not to make unique index togther with a primary key coz cerating unique indexes serves the purpose of both. ~ Thanks for the information A primary key is a unique index + NOT NULL constraints on the key column(s) ... ie , a unique key columns can have null values but primary key columns cannot... The primary key is also needed when you want to define a refrential constraint ... >From the data model perspecitive, defining a primary key is a good practise ... Other unique keys are defined as alternate keys ... therefor, why not define a primary key and avoid defining Unique index on the PK columns ? Going back to your original question, make sure you do RUNSTATS on the tables and indexes ... Otherwise, the optimizer might not use the index HTH Sathyaram > A primary key is a unique index + NOT NULL constraints on the key > column(s) ... ie , a unique key columns can have null values but [quoted text clipped - 10 lines] > Going back to your original question, make sure you do RUNSTATS on the > tables and indexes ... Otherwise, the optimizer might not use the index Further: By defining the unique index explicitly before the primary key two things are achieved: 1. You are in control of the index name 2. You can INCLUDE additional columns with the index. Example for 2: CREATE TABLE T(pk INT NOT NULL, c1 INT); CREATE UNIQUE INDEX I_FOR_PK ON T(pk) INCLUDE(c1); ALTER TABLE T ADD CONSTRAINTS PK PRIMARY KEY (pk); DB2 will pick I_FOR_PK to enforce the primary key, but now SELECT c1 FROM T WHERE pk = ? will be able to use an index only access. In short I think it's a good thing to separate out the steps. Don't mind the warnings. Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab >By defining the unique index explicitly before the primary key two things are achieved: >1. You are in control of the index name >2. You can INCLUDE additional columns with the index Can we add: 3. You can specify CLUSTER. I am actually not familiar enough with CLUSTER to see if it makes sense on the PRIMARY KEY. B. "Brian Tkatch" <Maxwell_Smart@ThePentagon.com> wrote in message > Can we add: 3. You can specify CLUSTER. > I am actually not familiar enough with CLUSTER to see if it makes sense > on the PRIMARY KEY. > > B. If your primary key has more than one column, and one or more of the columns is a primary key on another parent table (there is a foreign key relationship), then the primary key on the dependent table is a often a good candidate for a clustering index. Example: ORDER table: Order Number Integer PK Customer Number (clustering index) (There PK is a single column and not the clustering index) ORDER DETAIL Table: Order Number Integer PK (FK to ORDER Table) Order Item Smallint PK (the above PK with 2 columns should be the clustering index) OK, i see. Does CLUSTERing help BETWEENs? That is, if the PK is a part number, part numbers are somewhat sequential, and usually parts are grabbed with a BETWEEN, would CLUSTERing put the part in order, helping a range scan quickly grab the range from disk too? B. Yes. Cluster option directs DB2 to do the inserts using the index with cluster option to determine the page in which the insert should go. The idea is to keep inserted data clustered in the proper page. When you use range delimiting predicates (between, <=,>=, like xx%, ..) on the column(s) of the index then DB2 will likely use the index to grab the pages with the rows you want. After creating the index with cluster, if the data exists in the table, do an offline reorg and db2 will sequence the rows following the index. Inserts will try to keep the sequencing . It would also be advisable to alter the table (or create it) with the PCTFREE parm. This will give a percentage of free space in each page and DB2 would likely find room in the proper page to place the insert. db2 create table foo (col1 int,col2 char(xx),col3 .....) PCTFREE 20 db2 create unique index partno on foo (col1) cluster db2 alter table foo primary key (col1) constraint pkcol1 Populate the table using a sorted file in col1 sequence or reorg aft er the import/insert/load. HTH, Pierre. SignaturePierre Saint-Jacques SES Consultants Inc. 514-737-4515 > OK, i see. > [quoted text clipped - 6 lines] > > B. Hi, Fraser McArthur, a consultant at the IBM Toronto Lab had wrote in an article and it contained · When queries are completing in a reasonable time, avoid adding indexes as they can slow down update operations and consume extra space. It is sometimes possible to have one larger index that will cover several queries. · Avoid using more than five columns in an index due to management overhead. · For multi-column indexes, place the column which is referenced most in queries first in the definition. · Avoid adding an index which is similar to a preexisting index. It creates more work for the optimizer and will slow down update operations. Instead, alter the preexisting index to contain additional columns. For example, there is an existing index i1 on (c1,c2) of a table. You notice that your query using "where c2=?", so you create an additional index i2 on (c2). This similar index adds nothing, as it is redundant to i1 and is now additional overhead. The data model which we make use of has tables which have the similar pattern. My question is if we already have a Unique index(unique+index) and a primary key Do we really need a separate index on prod_typ_cde (because it is redundant and is it an additional overhead) ?? CREATE TABLE "DB2TAR"."TKTG" ( "PROD_TYP_CDE" CHAR(6) NOT NULL , "REC_LOCTR_NUM" CHAR(7) NOT NULL , "DEP_ALLW_IND" CHAR(1) NOT NULL WITH DEFAULT 'N' , "BKNG_RNG_MIN_DAYS" SMALLINT NOT NULL , "BKNG_RNG_MAX_DAYS" SMALLINT NOT NULL , "AFT_BKNG_DAYS" SMALLINT , "BEF_DPTR_DAYS" SMALLINT , "TKTG_GRC_DAYS" SMALLINT , "GARP_RVW_ADVN_DAYS" SMALLINT , "LST_UPDT_USID" CHAR(6) NOT NULL , "LST_UPDT_TMS" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP ) IN "SGRA005" ; CREATE UNIQUE INDEX "DB2TAR"."ITKTG_OVRD_RULE01" ON "DB2GRA"."TKTG" ("PROD_TYP_CDE" ASC, "REC_LOCTR_NUM" ASC, "DEP_ALLW_IND" ASC, "BKNG_RNG_MIN_DAYS" ASC, "BKNG_RNG_MAX_DAYS" ASC) CLUSTER ; CREATE INDEX "DB2TAR "."ITKTG_OVRD_RULE02" ON "DB2GRA"."TKTG" ("PROD_TYP_CDE" ASC); ALTER TABLE "DB2TAR "."TKTG" ADD CONSTRAINT "PK_TKTG_OVRD_RULE" PRIMARY KEY ("PROD_TYP_CDE", "REC_LOCTR_NUM", "DEP_ALLW_IND", "BKNG_RNG_MIN_DAYS", "BKNG_RNG_MAX_DAYS"); Please post with text only on this newsgroup. Given the following index (either explicitly created, or created for you via a PK definition): CREATE UNIQUE INDEX "DB2TAR"."ITKTG_OVRD_RULE01" ON "DB2GRA"."TKTG" ("PROD_TYP_CDE" ASC, "REC_LOCTR_NUM" ASC, "DEP_ALLW_IND" ASC, "BKNG_RNG_MIN_DAYS" ASC, "BKNG_RNG_MAX_DAYS" ASC) CLUSTER ; Then the following index is redundant since it is the same as the first column of the index above: CREATE INDEX "DB2TAR "."ITKTG_OVRD_RULE02" ON "DB2GRA"."TKTG" ("PROD_TYP_CDE" ASC); However, even with the unique index above, the following index is not redundant if you have SQL statements with REC_LOCTR_NUM as the only predicate. CREATE INDEX "DB2TAR "."ITKTG_OVRD_RULE03" ON "DB2GRA"."TKTG" ("REC_LOCTR_NUM" ASC); I didnt understand how this ones redundant CREATE INDEX "DB2TAR "."ITKTG_OVRD_RULE02" ON "DB2GRA"."TKTG" ("PROD_TYP_CDE" ASC); and this ones not redundant CREATE INDEX "DB2TAR "."ITKTG_OVRD_RULE03" ON "DB2GRA"."TKTG" ("REC_LOCTR_NUM" ASC); Does that mean that creating an index on any other column is not redundant ? It would be great if you could put some more light to this. cause i am really confused now. Because "PROD_TYP_CDE" ASC, is the first column of the index: CREATE UNIQUE INDEX "DB2TAR"."ITKTG_OVRD_RULE01" ON "DB2GRA"."TKTG" ("PROD_TYP_CDE" ASC, "REC_LOCTR_NUM" ASC, "DEP_ALLW_IND" ASC, "BKNG_RNG_MIN_DAYS" ASC, "BKNG_RNG_MAX_DAYS" ASC) CLUSTER ; and is clustered, Then your second index: CREATE INDEX "DB2TAR "."ITKTG_OVRD_RULE02" ON "DB2GRA"."TKTG" ("PROD_TYP_CDE" ASC); is redundant. Because of clustering on the first column, it is highly unlikely that the second column will also be clustered; therefore the index: CREATE INDEX "DB2TAR "."ITKTG_OVRD_RULE03" ON "DB2GRA"."TKTG" ("REC_LOCTR_NUM" ASC); would have a bad clusterratio and would not likely be used by DB2 and then be redundant. Since the keys would not be in order over the table, sorts would have to happen, or using inequality predicates would probably cause table scans anyway and the index would still not be used. If the query has a where clause on the first column, then having a where on the second column will cause the first index to be used but never the second one. So, redundant. There must be a reason (sme SQL statements) that cause you to think of the two indexes. Validate this by running the statements agains the Design Advisor. It has an option to Evaluate indexes. I highly suspect that the advisor will identify the second index as not used. HTH, Pierre. SignaturePierre Saint-Jacques SES Consultants Inc. 514-737-4515 >I didnt understand how this ones redundant > [quoted text clipped - 11 lines] > It would be great if you could put some more light to this. cause i am > really confused now. >I didnt understand how this ones redundant > [quoted text clipped - 11 lines] > It would be great if you could put some more light to this. cause i am > really confused now. There are two ways that indexes are accessed. The most efficient is via the b-tree, where DB2 traverses the non-leaf pages to find the leaf page and the matching index row which contains the table RID in the least amount of page fetches (indexes row are stored on pages like table rows). The other way is where an index is read in its entirety, the leaf pages are accessed sequentially top to bottom, sort of like a table scan, but on an index. Each index row is examined by DB2 to see if it qualifies. This is usually not the most efficient way to find the qualifying index row and the associated RID pointing to the table. In order fir DB2 to use the b-tree of an index to find the qualifying rows, the predicate must contain the left most columns (1-n) of an index. If the predicate only contains the second column of the index, then the best DB2 can do is to scan the entire index and not be able to use the b-tree. Thanks every one!! Finally know how to create indexes and what to watch out for.. The discussion was excellent thanks again!!!! Yeah, thanx for the info. B. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.