如下SQL,在表pt_pk_em4_41的isometric (varchar2)字段上创建了索引INDEX_ISOM,如何才能让这个索引起用呢?SELECT pt_pk_em4_41.isometric, pt_pk_em4_41.spool, /*+index(pt_pk_em4_41,INDEX_ISOM) */
MAX (pt_pk_em4_41.spool_date) AS spool_date,
MAX (pt_pk_em4_41.tfd_date) AS tfd_date,
MAX (pt_pk_em4_41.emr_date) AS emr_date
FROM (SELECT pt_pk_em4_41.*
FROM ims.pt_pk_em4_41, ims.pt_pk_em4_4
WHERE pt_pk_em4_41.parent_uuid = pt_pk_em4_4.uuid
AND ( pt_pk_em4_4.TYPE = '1'
OR pt_pk_em4_4.TYPE = '4'
)) pt_pk_em4_41
GROUP BY (pt_pk_em4_41.isometric, pt_pk_em4_41.spool)
MAX (pt_pk_em4_41.spool_date) AS spool_date,
MAX (pt_pk_em4_41.tfd_date) AS tfd_date,
MAX (pt_pk_em4_41.emr_date) AS emr_date
FROM (SELECT pt_pk_em4_41.*
FROM ims.pt_pk_em4_41, ims.pt_pk_em4_4
WHERE pt_pk_em4_41.parent_uuid = pt_pk_em4_4.uuid
AND ( pt_pk_em4_4.TYPE = '1'
OR pt_pk_em4_4.TYPE = '4'
)) pt_pk_em4_41
GROUP BY (pt_pk_em4_41.isometric, pt_pk_em4_41.spool)
索引建立完毕,就会有了,在你执行select的时候,用不用索引是oracle系统分析之后决定的。在你update、delete、insert的时候,都会及时更新索引的。
FROM ims.pt_pk_em4_41, ims.pt_pk_em4_4
WHERE pt_pk_em4_41.parent_uuid = pt_pk_em4_4.uuid
AND ( pt_pk_em4_4.TYPE = '1'
OR pt_pk_em4_4.TYPE = '4'
)
中的where条件并没有用到isometric字段,启用该索引没有意义。
MAX (pt_pk_em4_41.spool_date) AS spool_date,
MAX (pt_pk_em4_41.tfd_date) AS tfd_date,
MAX (pt_pk_em4_41.emr_date) AS emr_date
FROM (SELECT pt_pk_em4_41.*
FROM ims.pt_pk_em4_41, ims.pt_pk_em4_4
WHERE pt_pk_em4_41.parent_uuid = pt_pk_em4_4.uuid
AND ( pt_pk_em4_4.TYPE = '1'
OR pt_pk_em4_4.TYPE = '4'
)) pt_pk_em4_41 where pt_pk_em4_41.isometric>'' GROUP BY (pt_pk_em4_41.isometric, pt_pk_em4_41.spool)
我可以这样起用索引吗?(红色部分),因为我不是查询isometric字段的某个值,所以isometric的值不确定的.