• 如何让oracle的select强制走索引


    大多数情况下,oracle数据库内置的查询优化策略还是很成功的,但偶尔也有犯2的时候,即使有索引,也会做全表扫描,可以参考以下语句的写法,强制让select语句使用索引

     1 CREATE OR REPLACE VIEW V_RES_CBA AS
     2 SELECT /*+INDEX(SEG IDX_T_RES_ALLOSEG_ALLOID)*/
     3    ALLO.ALLOID AS RESID,
     4    NULL AS AWB, 
     5    ALLO.ALLOTMENT AS ALLO_ID, 
     6    DAYS.FDATE + NVL(SEG.DAYSDISP, 0) AS FDATE, 
     7    ALLO.SPECULD AS SPECULD 
     8     FROM T_RES_ALLO ALLO, T_RES_ALLOSEG SEG, V_FDATE DAYS
     9    WHERE ALLO.ALLOID = SEG.ALLOID
    10      AND ((ALLO.ALLOIND = 'A' AND ALLO.ALLO_DATE = DAYS.FDATE) OR
    11          (ALLO.ALLOIND = 'S' AND
    12          NVL(ALLO.SDATE, ALLO.ALLO_DATE) = DAYS.FDATE)) 
    13   UNION
    14   SELECT /*+INDEX(SEG IDX_T_RES_ALLOSEG_ALLOID)*/
    15    ALLO.ALLOID AS RESID,
    16    NULL AS AWB, 
    17    ALLO.ALLOTMENT AS ALLO_ID, 
    18    DAYS.FDATE + NVL(SEG.DAYSDISP, 0) AS FDATE, 
    19    ALLO.SPECULD AS SPECULD 
    20     FROM T_RES_ALLO ALLO, T_RES_ALLOSEG SEG, V_FDATE DAYS
    21    WHERE ALLO.ALLOID = SEG.ALLOID
    22      AND ALLO.ALLO_DATE IS NULL
    23      AND ALLO.ALLOIND = 'A'
    24      AND (DAYS.FDATE >= ALLO.SDATE AND DAYS.FDATE <= ALLO.EDATE AND
    25          INSTR(ALLO.WEEKDAY, DAYS.WEEKDAY) > 0) 
    26      AND NOT EXISTS (SELECT subQuery.ALLOID
    27             from T_RES_ALLO subQuery
    28            where subQuery.ALLO_DATE = DAYS.FDATE
    29              and subQuery.ALLOTMENT = ALLO.ALLOTMENT)
    30 ;

    要点:

    1、/*+INDEX(SEG IDX_T_RES_ALLOSEG_ALLOID)*/ 这里的/*...*/中间不要有空格

    2、表名要用别名,即:以上面的sql语句为例,要使用SEG,而不是T_RES_ALLOSEG

  • 相关阅读:
    模块
    迭代器,生成器
    内置函数
    匿名函数,三元表达式,列表推导式,字典生成式
    递归
    闭包函数,装饰器
    名称空间与作用域
    《数据结构与算法之6 扑克牌洗牌算法》
    《java学习笔记》
    Building Machine Learning Systems with Python 2
  • 原文地址:https://www.cnblogs.com/yjmyzz/p/compel-SELECT-statement-to-use-INDEX-in-Oracle-database.html
Copyright © 2020-2023  润新知