备注:添加索引之后验证索引有效性:
执行1:EXPLAIN PLAN FOR select accouts, accrate, completeratio, def1, def10, def11, def12, def13, def14, def15, def16, def17, def18, def19, def2, def20, def3, def4, def5, def6, def7, def8, def9, isdeposit,
isprepayment, memo, oprnum, paymentday, pk_contr, pk_contr_pay, pk_group, pk_org, pk_org_v, pk_paymentch, pk_wbs, qual_time, rowno, sched_taxmny, taxmny, tot_sld_taxmny, pcm_contr_protocol.ts from pcm_contr_protocol where pcm_contr_protocol.pk_contr = '10013D100000000394IF' AND pcm_contr_protocol.dr = 0;
执行2:SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
如上图 索引生效;
create index INDEX_pcm_contr_protocol_1 on pcm_contr_protocol (pk_contr);
如上图索引不生效;
更新统计信息:
analyze table pcm_contr_protocol compute statistics;
ANALYZE TABLE pcm_contr_protocol COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;
analyze table pcm_contr_protocol compute statistics for table for all indexes for all indexed columns;