SQL优化一例：GROUP BY的语句

时间：2010-10-25 来源：zylhsy

SQL语句：
select t.type, count(t.id) as todo_count from mc_job_form t where t.state = '2' and t.customs_code = :a group by t.type

表结构:
ID VARCHAR2(24) N   id
IN_OUT_FLAG CHAR(1) Y
PROVIDER_ID NUMBER Y
APPLY_FORM_ID VARCHAR2(24) Y
DECLARE_APPLY_ID VARCHAR2(24) Y
PROPOSER_ID VARCHAR2(20) Y
MANUAL_ID CHAR(12) Y
TRADE_TYPE VARCHAR2(3) Y
COMPANY_CODE CHAR(10) Y
COMPANY_NAME NVARCHAR2(50) Y
PROVIDER_NAME NVARCHAR2(50) Y
GROSS_WEIGHT NUMBER(19,5) Y
NET_WEIGHT NUMBER(19,5) Y
AMOUNT NUMBER(19,5) Y
WRAP_TYPE VARCHAR2(32) Y
TRUNK_NO NVARCHAR2(255) Y
CREATE_TIME DATE Y
DECLARE_TIME DATE Y
IN_TIME DATE Y
CUSTOMS_CODE CHAR(4) Y
PORT CHAR(4) Y
REMARK NVARCHAR2(255) Y
STATE CHAR(1) Y
COMMENTS NVARCHAR2(200) Y
TYPE VARCHAR2(2) Y
PROPOSER NVARCHAR2(20) Y
OUT_TIME DATE Y
PROPOSER_PHONE VARCHAR2(20) Y
CONTRACT_NO VARCHAR2(32) Y
TALLY_COMMENTS NVARCHAR2(200) Y
TALLY_TIME DATE Y
TALLY_APPROVE_COMMENTS NVARCHAR2(200) Y
ASSO_JOB_FORM_ID VARCHAR2(24) Y
DATA_ORIGIN CHAR(1) Y
GUARANTY_AMOUNT NUMBER(19,5) Y
ASSO_FLAG CHAR(1) Y
AREA_TYPE CHAR(1) Y

优化前执行计划：
SELECT STATEMENT, GOAL = ALL_ROWS Cost=6 Cardinality=7 Bytes=63
HASH GROUP BY Cost=6 Cardinality=7 Bytes=63
TABLE ACCESS FULL Object owner=INMAN Object name=MC_JOB_FORM Cost=5 Cardinality=23 Bytes=207

无效的优化：（原因是索引未加入GROUPBY列）
-- Create/Recreate indexes
create index TESTGG on MC_JOB_FORM (STATE, CUSTOMS_CODE)
tablespace IN_MAN_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
    initial 64K
    minextents 1
    maxextents unlimited
);

有效的优化：
-- Create/Recreate indexes
create index TESTGG on MC_JOB_FORM (STATE, CUSTOMS_CODE, TYPE)
tablespace IN_MAN_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
    initial 64K
    minextents 1
    maxextents unlimited
);

优化后执行计划：
SELECT STATEMENT, GOAL = ALL_ROWS Cost=1 Cardinality=7 Bytes=63
SORT GROUP BY NOSORT Cost=1 Cardinality=7 Bytes=63
INDEX RANGE SCAN Object owner=INMAN Object name=TESTGG Cost=1 Cardinality=23 Bytes=207

总结：
要确保group by 的列全部在索引中，并且列的属性不能为空，优化潜力很大的，我测了小数据量的