时间戳

首页 > 解决方案 > Oracle中动态查询的索引

sql - Oracle中动态查询的索引

问题描述

我有一个程序，根据我建立动态条件的参数。如果任何参数为空，我们将忽略以在 where 条件下检查此列。谁能建议我索引表以获得最佳性能的最佳方法是什么？

另一个问题是假设我有一个 12 列的表。我有两个查询，其中一个在 where 条件中有三列，另一个查询在 where 条件中有八列。在这种情况下，我应该创建两个不同的索引以获得更好的性能吗？

V_sql VARCHAR2(10000):='SELECT
    SV_ACC_REG.ACC_REG_ID            AS ACC_REG_ID           ,
    SV_ACC_REG.PRODUCT_ID            AS PRODUCT_ID           ,
    GEN_PRODUCT.FULL_NAME            AS PRODUCT_NAME         ,
    SV_ACC_REG.STATUS                AS STATUS               ,
    SV_ACC_REG.OPENING_DATE          AS OPENING_DATE         ,
    SV_ACC_REG.CURRENT_BALANCE       AS CURRENT_BALANCE      ,
    SV_ACC_REG.CLOSING_DATE          AS CLOSING_DATE         ,
    SV_ACC_REG.REG_NO                AS REG_NO               ,
    SV_ACC_REG.IS_WITHDRAW_BY_SINGLE AS IS_WITHDRAW_BY_SINGLE,
    SV_ACC_REG.IS_SINGLE             AS IS_SINGLE            ,
    SV_ACC_REG.IS_EXTENDABLE         AS IS_EXTENDABLE        ,
    SV_ACC_REG.REMARKS               AS REMARKS              ,
    SV_ACC_REG.PR_NO                 AS PR_NO                ,
    SV_ACC_REG.CREATED_ON            AS CREATED_ON           ,
    SV_ACC_REG.CREATED_BY            AS CREATED_BY           ,
    SV_ACC_REG.UPDATED_ON            AS UPDATED_ON           ,
    SV_ACC_REG.UPDATED_BY            AS UPDATED_BY           ,
    SV_ACC_REG.IS_DELETED            AS IS_DELETED           ,
    SV_ACC_REG.DELETED_ON            AS DELETED_ON           ,
    SV_ACC_REG.DELETED_BY            AS DELETED_BY           ,
    SV_ACC_REG.CLIENT_TYPE           AS CLIENT_TYPE          ,
    SV_ACC_REG.IS_TRANSFER           AS IS_TRANSFER          ,
    SV_ACC_REG.WITHDRAW_TYPE         AS WITHDRAW_TYPE        ,
    SV_ACC_REG.DEATH_DATE            AS DEATH_DATE           ,
    SV_ACC_REG.IS_MIGRATE            AS IS_MIGRATE           ,
    SV_ACC_REG.MIGRATE_COMMENTS      AS MIGRATE_COMMENTS     ,
    SV_ACC_REG.CHEQUE_HONOR_DATE     AS CHEQUE_HONOR_DATE    ,
    SV_ACC_REG.SO_NO                 AS SO_NO                ,
    SV_ACC_REG.IS_MINOR              AS IS_MINOR             ,
    SV_ACC_REG.NAME                  AS NAME                 ,
    SV_ACC_REG.IS_OLD                AS IS_OLD               ,
    SV_ACC_REG.IS_NO_PROFIT_CALC     AS IS_NO_PROFIT_CALC    ,
    SV_ACC_REG.IS_SIX_M_PROFIT_CALC  AS IS_SIX_M_PROFIT_CALC ,
    SV_ACC_REG.IS_SEND_DPMG                                  ,
    SV_CUSTOMER_INFO.CUSTOMER_NAME AS CUSTOMER_NAME
    FROM SV_ACC_REG
    LEFT JOIN GEN_PRODUCT ON SV_ACC_REG.PRODUCT_ID=GEN_PRODUCT.PRODUCT_NO
    LEFT JOIN SV_CUSTOMER_INFO ON SV_ACC_REG.ACC_REG_ID = SV_CUSTOMER_INFO.ACC_REG_ID';
V_WHERE VARCHAR2(500):=' WHERE ';
BEGIN
BEGIN

  V_WHERE:=' WHERE ';
    IF p_ACC_REG_ID IS NOT NULL THEN
      V_WHERE := V_WHERE || ' SV_ACC_REG.ACC_REG_ID = '||p_ACC_REG_ID||' AND';
   END IF; 

    IF p_PRODUCT_ID IS NOT NULL THEN
      V_WHERE := V_WHERE || ' SV_ACC_REG.PRODUCT_ID = '||p_PRODUCT_ID||' AND';
    END IF; 

    IF p_STATUS IS NOT NULL THEN
        V_WHERE := V_WHERE || ' SV_ACC_REG.STATUS = '||p_STATUS||' AND';
    END IF; 

    IF p_IS_TRANSFER IS NOT NULL THEN
        V_WHERE := V_WHERE || ' SV_ACC_REG.IS_TRANSFER = '||p_IS_TRANSFER||' AND';
    END IF; 

    IF p_SO_NO IS NOT NULL THEN
        V_WHERE := V_WHERE || ' SV_ACC_REG.SO_NO = '||p_SO_NO||' AND';
    END IF; 

    IF p_IS_OLD IS NOT NULL THEN
        V_WHERE := V_WHERE || ' SV_ACC_REG.IS_OLD = '||p_IS_OLD||' AND';
    END IF; 

    IF p_IS_SEND_DPMG IS NOT NULL THEN
        V_WHERE := V_WHERE || ' SV_ACC_REG.IS_SEND_DPMG = '||p_IS_SEND_DPMG||' AND';
    END IF; 

    IF p_IS_SIX_M_PROFIT_CALC IS NOT NULL THEN
        V_WHERE := V_WHERE || ' IS_SIX_M_PROFIT_CALC= '||p_IS_SEND_DPMG||' AND';
    END IF;  

    IF  LENGTH(' WHERE ') =7 THEN
        V_sql :=V_sql ||'  ORDER BY SV_ACC_REG.ACC_REG_ID ASC';
    ELSE
         V_sql :=V_sql || SUBSTR(V_WHERE, 1, LENGTH(V_WHERE) - 3) ||'  ORDER BY SV_ACC_REG.ACC_REG_ID ASC';
    END IF; 
    --V_sql :=SUBSTR(V_sql, 1, LENGTH(V_sql) - 3);

   --OPEN cur_OUT FOR V_sql USING p_ACC_REG_ID, p_PRODUCT_ID,p_STATUS,p_IS_TRANSFER,p_SO_NO,p_IS_OLD,p_IS_SEND_DPMG,p_IS_SIX_M_PROFIT_CALC;
    OPEN cur_OUT FOR V_sql ;
END;
END;

标签： sqloracleindexing

解决方案

谁能建议我索引表以获得最佳性能的最佳方法是什么？

好吧，这个查询有 10 个可选参数，可能的组合数量（null/not null）是 2^10 = 1024，所以你可以得到这个查询的 1000 个变体，每个变体可能需要一组不同的索引. 在这里给出一个明智的暗示是相当不可能的。

在你的情况下我会怎么做：

  1. 将应用程序部署到生产环境
  2. 允许用户使用应用程序几天/一周
  3. 登录数据库并运行以下查询（您必须具有 DBA 授予的适当权限）
  4. 选择最有问题的查询，调整它们，然后在几天/几周/几个月后一次又一次地重复。

此查询将提取有关哪些查询最常用以及哪些查询消耗最多资源的基本统计信息。那里有许多统计信息，例如 EXECUTIONS、总 ELAPSED_TIME、BUFFER_GETS 等，它们可以让您大致了解应用程序的操作、用户行为等，并允许选择最差的查询进行进一步分析。

您可以进一步查询v$sql_plan以获得 rdbms 使用的执行计划（使用 sql_id 和 plan_hash_value 列）以分析它们。

select 
        SQL_TEXT
        , SQL_FULLTEXT
        , SQL_ID
        , FETCHES
        , EXECUTIONS
        , FIRST_LOAD_TIME
        , PARSE_CALLS
        , DISK_READS
        , BUFFER_GETS
        , USER_IO_WAIT_TIME
        , ROWS_PROCESSED
        , OPTIMIZER_MODE
        , OPTIMIZER_COST
        , HASH_VALUE
        , PLAN_HASH_VALUE
        , CHILD_NUMBER
        , CPU_TIME
        , ELAPSED_TIME
        , IO_INTERCONNECT_BYTES
        , PHYSICAL_READ_REQUESTS
        , PHYSICAL_READ_BYTES
    from v$sql t
    where upper(sql_text) like upper('%FROM SV_ACC_REG%LEFT JOIN GEN_PRODUCT ON SV_ACC_REG.PRODUCT_ID=GEN_PRODUCT.PRODUCT_NO%')
    order by executions desc

推荐阅读