有以下一段代码:
insert into TMP_USR_card_QTY(User_Id,qty)
SELECT/*+ index(mt_cards idx_mt_card_crtdt) */
user_id,COUNT(0) qty
FROM MCARD.mt_cards c
where c.card_createtime between 1371571200 and 1371657600
group by user_id
执行计划如下:
而代码:
SELECT
/*+ index(mt_cards idx_mt_card_crtdt) */
user_id,COUNT(card_id) qty
FROM MCARD.mt_cards
where card_createtime >= 1371571200
group by user_id
的执行计划是:
经反复分析,原因是在HINT 中,使用了表别名,经修改代码如下后,得到了所要的结果。
insert into TMP_USR_card_QTY(User_Id,qty)
SELECT
/*+ index(c idx_mt_card_crtdt) */
user_id,COUNT(card_id) qty
FROM MCARD.mt_cards c
where c.card_createtime between 1371571200 and 1371657600
group by user_id