db2 index

Example 1:  Create an index named UNIQUE_NAM on the PROJECT table. The purpose of the index is to ensure that there are not two entries in the table with the same value for project name (PROJNAME). The index entries are to be in ascending order.

   CREATE UNIQUE INDEX UNIQUE_NAM       ON PROJECT(PROJNAME) 

Example 2:  Create an index named JOB_BY_DPT on the EMPLOYEE table. Arrange the index entries in ascending order by job title (JOB) within each department (WORKDEPT).

   CREATE INDEX JOB_BY_DPT       ON EMPLOYEE (WORKDEPT, JOB) 

Example 3:  The nickname EMPLOYEE references a data source table called CURRENT_EMP. After this nickname was created, an index was defined on CURRENT_EMP. The columns chosen for the index key were WORKDEBT and JOB. Create an index specification that describes this index. Through this specification, the optimizer will know that the index exists and what its key is. With this information, the optimizer can improve its strategy to access the table.

   CREATE UNIQUE INDEX JOB_BY_DEPT      ON EMPLOYEE (WORKDEPT, JOB)      SPECIFICATION ONLY        

Example 4:  Create an extended index type named SPATIAL_INDEX on a structured type column location. The description in index extension GRID_EXTENSION is used to maintain SPATIAL_INDEX. The literal is given to GRID_EXTENSION to create the index grid size.

   CREATE INDEX SPATIAL_INDEX ON CUSTOMER (LOCATION)      EXTEND USING (GRID_EXTENSION (x'000100100010001000400010')) 

Example 5: Create an index named IDX1 on a table named TAB1, and collect basic index statistics on index IDX1.

   CREATE INDEX IDX1 ON TAB1 (col1) COLLECT STATISTICS 

Example 6: Create an index named IDX2 on a table named TAB1, and collect detailed index statistics on index IDX2.

   CREATE INDEX IDX2 ON TAB1 (col2) COLLECT DETAILED STATISTICS 

Example 7: Create an index named IDX3 on a table named TAB1, and collect detailed index statistics on index IDX3 using sampling.

   CREATE INDEX IDX3 ON TAB1 (col3) COLLECT SAMPLED DETAILED STATISTICS 

---

利用 Windows Live 相簿輕鬆分享相片 輕鬆分享