Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2016
    Posts
    55
    Provided Answers: 2

    Unanswered: composite low card

    Hi All,

    db2 10.5 fp 8 Linux RH

    In composite index which column should be consider first, actually its based on predicates but dont it also depends up colcard
    db2 "describe table employee1 "

    Data type Column
    Column name schema Data type name Length Scale Nulls
    ------------------------------- --------- ------------------- ---------- ----- ------
    EMP_ID SYSIBM INTEGER 4 0 Yes
    DATE_OF_BIRTH SYSIBM DATE 4 0 Yes
    SALARY SYSIBM INTEGER 4 0 Yes
    EMP_NAME SYSIBM CHARACTER 10 0 Yes

    4 record(s) selected.

    db2 "select colname, colcard from syscat.columns where tabname = 'EMPLOYEE1' "

    COLNAME COLCARD
    -------------------------------------------------------------------------------------------------------------------------------- --------------------
    DATE_OF_BIRTH 17155
    EMP_ID 500000
    EMP_NAME 500000
    SALARY 90000




    CREATE INDEX "db2inst1"."EMPLOYEE1_X3" ON "db2inst1"."EMPLOYEE1"
    ("EMP_ID" DESC,
    "DATE_OF_BIRTH" DESC)

    COLLECT STATISTICS
    COMPRESS NO
    INCLUDE NULL KEYS ALLOW REVERSE SCANS;

    -- DDL Statements for Indexes on Table "db2inst1"."EMPLOYEE1"

    SET SYSIBM.NLS_STRING_UNITS = 'SYSTEM';

    CREATE INDEX "db2inst1"."EMPLOYEE1_X4" ON "db2inst1"."EMPLOYEE1"
    ("DATE_OF_BIRTH" DESC,
    "EMP_ID" DESC)

    COLLECT STATISTICS
    COMPRESS NO
    INCLUDE NULL KEYS ALLOW REVERSE SCANS;



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

    select * from employee1 where EMP_ID = 399055 and DATE_OF_BIRTH = '08/24/1984'


    db2 uses the composite index employee1_x4 in which DATE_OF_BIRTH column has low card.

    Original Statement:
    ------------------
    select
    *
    from
    employee1
    where
    EMP_ID = 399055 and
    DATE_OF_BIRTH = '08/24/1984'


    Optimized Statement:
    -------------------
    SELECT
    399055 AS "EMP_ID",
    '08/24/1984' AS "DATE_OF_BIRTH",
    Q1.SALARY AS "SALARY",
    Q1.EMP_NAME AS "EMP_NAME"
    FROM
    UDBLL008.EMPLOYEE1 AS Q1
    WHERE
    (Q1.DATE_OF_BIRTH = '08/24/1984') AND
    (Q1.EMP_ID = 399055)

    Access Plan:
    -----------
    Total Cost: 20.4276
    Query Degree: 1

    Rows
    RETURN
    ( 1)
    Cost
    I/O
    |
    1
    FETCH
    ( 2)
    20.4276
    3
    /---+----\
    1 500000
    IXSCAN TABLE: UDBLL008
    ( 3) EMPLOYEE1
    13.6205 Q1
    2
    |
    500000
    INDEX: UDBLL008
    EMPLOYEE1_X4
    Q1




    while creating composite index is there advantage of using low card column as first column ?

    regds
    Paul

  2. #2
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    only if it is the only input you will be providing for filtering. You should put columns that filter the most data first. Though that is, as always, dependent on what you are doing. I have indexes on a single column that has a cardinality of 2. It is a Y/N flag. We are always looking for FLAG = 'N' and less than .01% of the table ever has the flag as N, over 99.99% of the table is FLAG = 'Y'. p.s. the table has over 300,000,000 million rows and the query runs sub-second and is run throughout the day.

  3. #3
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Hi,

    Try an optimization profile with another index and compare the cost and run time.
    Regards,
    Mark.

  4. #4
    Join Date
    Aug 2016
    Posts
    55
    Provided Answers: 2
    was trying to create optimization profile to use index_X3 (EMP_ID, DATA_OF_BIRTH) was giving error.

    So dropped index X4 and ran the query, db2 used index X3 but there is no difference in the cost.

    Attached explain plan for both run X4 index is what db2 choose to use, and it used X3 when X4 was dropped.
    Attached Files Attached Files

Posting Permissions

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