1.视图添加索引
/* Formatted on 2020/1/6 下午 04:46:37 (QP5 v5.163.1008.3004) */ SELECT /*+index(VIEW_NAME.TABLE01_NAME,INDEX01_NAME) index(VIEW_NAME.TABLE01_NAME,INDEX01_NAME) index(VIEW_NAME.TABLE02_NAME,INDEX01_NAME) index(VIEW_NAME.TABLE03_NAME,INDEX01_NAME) index(VIEW_NAME.TABLE04_NAME,INDEX01_NAME) */ * FROM VIEW_OWNER.VIEW WHERE ADD_DATE > SYSDATE - 1;
2.多个索引添加hint
/* Formatted on 2020/1/6 下午 04:49:06 (QP5 v5.163.1008.3004) */ SELECT /*+index(a INDEX_01) index(a INDEX02)*/ COUNT (*) FROM TABLE_OWNER.TABLE_NAME a WHERE CONDITION01 AND CONDITION02;
3.添加cursor_sharing hint
SELECT /*+ CURSOR_SHARING_EXACT */ * FROM TABLE_OWNER.TABLE_NAME A WHERE DECODE (TO_NUMBER (PROPERTY_01), 0, 0) = 0;
某些数据库因为开发为绑定变量所以把cursor_sharing设置为force,这样有时候一个值不想被oacle自动变成变量的话,可以用上述语句。