インデックスカバーリングの検証
http://www7b.software.ibm.com/dmdd/library/techarticle/0303kuznetsov/0303kuznetsov.html
テキスト ボックス: connect to sample; CREATE TABLE CUSTOMER_DATA( ID INT NOT NULL, FIRST_NAME VARCHAR(30), LAST_NAME VARCHAR(30), SALUTATION VARCHAR(10), ADDRESS VARCHAR(30), CITY VARCHAR(30), STATE CHAR(2), ZIP VARCHAR(10), PHONE_AREA SMALLINT, PHONE_NUMBER INTEGER, TOTAL_AMOUNT FLOAT, SOME_OTHER_DATA VARCHAR(100)); CREATE TABLE CUSTOMER_ORDER( CUSTOMER_ID INT NOT NULL, ORDER_NUMBER SMALLINT NOT NULL, ORDER_DT DATE, AMOUNT FLOAT, SOME_OTHER_DATA VARCHAR(100)); ALTER TABLE CUSTOMER_DATA ADD PRIMARY KEY(ID); ALTER TABLE CUSTOMER_ORDER ADD PRIMARY KEY(CUSTOMER_ID, ORDER_NUMBER); ALTER TABLE CUSTOMER_ORDER ADD FOREIGN KEY(CUSTOMER_ID) REFERENCES CUSTOMER_DATA(ID); CREATE INDEX CUSTOMER_NAMES ON CUSTOMER_DATA(LAST_NAME, FIRST_NAME); terminate;
LIKE句ク LIKE句ク LIKE句ク
SELECT LAST_NAME, FIRST_NAME FROM CUSTOMER_DATA WHERE LAST_NAME LIKE '%STONE'
インデックスなし LAST_NAMEだけインデックス (LAST_NAME,FIRSTNAME)のインデックス
ORDER By句ク ORDER By句ク ORDER By句ク
SELECT LAST_NAME, FIRST_NAME, PHONE_AREA, PHONE_NUMBER FROM CUSTOMER_DATA ORDER BY LAST_NAME, FIRST_NAME
(LAST_NAME, FIRST_NAME, PHONE_AREA, PHONE_NUMBER)のインデックス
インデックスなし (LAST_NAME,FIRSTNAME)のインデックス
JOIN句ク JOIN句ク
SELECT CUSTOMER_DATA.FIRST_NAME, CUSTOMER_DATA.LAST_NAME, CUSTOMER_DATA.PHONE_AREA, CUSTOMER_DATA.PHONE_NUMBER,
CUSTOMER_ORDER.ORDER_NUMBER, CUSTOMER_ORDER.ORDER_DT, CUSTOMER_ORDER.AMOUNT
FROM CUSTOMER_DATA JOIN CUSTOMER_ORDER ON ID = CUSTOMER_ID
CUSTOMER_DATA表ヒョウの(ID, FIRST_NAME, LAST_NAME, PHONE_AREA, PHONE_NUMBER)にインデックス
CUSTOMER_ORDER表ヒョウの(CUSTOMER_ID, ORDER_NUMBER, ORDER_DT, AMOUNT)にインデックス
インデックスカバリング(Index Covering)
SELECTでインデックスが使ツカわれるようにすること。
2003/08/23 ugya@lycos.com