Oracle SQL查询运行缓慢

Question

我有以下用于搜索功能的Oracle SQL查询，并包裹在存储过程中。

它运行非常慢。

打开p_cursor for 选择 tablea。*，tb_s.somecolumn，tb_s.somecolumn ..

    FROM
        customer_site TableA
        LEFT JOIN TableB TB on TB.some_id = TableA.some_id
        LEFT JOIN TableC TB_S on TB_S.Bla_ID = TB.Bla_ID
        LEFT JOIN TableC TB_CS on TB_CS.Bla2_ID = TB.Bla2_ID
        LEFT JOIN TableC TB_1 on TB_1.Bla3_ID = TB.Bla3_ID
        LEFT JOIN TableC TB_2 on TB_DC.Bla4_ID = TB.Bla4_ID
        LEFT JOIN TableD SP on SP.SP_ID = TableA.SP_ID
        LEFT JOIN TableC TB_3 on TB_3.Bla5_ID = TB.Bla5_ID
        LEFT JOIN TableC TB_4 on TB_4.Bla6_ID = TB.Bla6_ID
        LEFT JOIN TableC TB_5 on TB_5.Bla7_ID = TB.Bla7_ID
    WHERE 
        (p_nmi IS NULL OR TableA.someid LIKE p_nmi)
        AND (p_last_name IS NULL OR TableA.last_name LIKE p_last_name)
        AND (p_full_address IS NULL OR
            UPPER(assemble_address(flat_number, street_number, street_name,
                street_suffix, apartment_number, building_name, suburb, state, postcode))
            LIKE p_full_address)
        AND (p_param1 IS NULL OR TB.owner = p_param1)
        AND (p_param2 IS NULL OR TB.status = p_param2)
        AND (p_param3 IS NULL OR TB.contact_stage = p_param3)
        AND (p_param4 IS NULL OR TB.no_access_code = p_param4)
        AND (p_param5 IS NULL OR TB.defect_code = p_param5)
        AND (p_param6 IS NULL OR TB.REFUSAL_RESOLUTION = p_param6)
        AND (p_param7 IS NULL OR TB.DEFECT_LEVEL = p_param7)
        AND (p_param8 IS NULL OR TB.AMI_CTR_STATUS = p_param8)
        AND (p_param9 IS NULL OR TableA.meter_route LIKE p_param9)
        AND (p_param10 IS NULL OR TableA.sp_id = p_param10)
        AND (p_inTBdent_date_from IS NULL OR TB.inTBdent_date >= p_inTBdent_date_from)
        AND (p_inTBdent_date_to IS NULL OR TB.inTBdent_date <= p_inTBdent_date_to)
        AND rownum < 1001
    ORDER BY
        TB.inTBdent_date;

有人可以帮助我调整上述查询吗？

谢谢。

Answer 1

@annjawn的建议，将(p_param IS NULL or col = p_param)的所有实例更改为 col = nvl(p_param, col)。正如乔纳森·刘易斯（Jonathan Lewis）文章所解释的那样，甲骨文可以优化这些条件。这可能启用INDEX RANGE SCAN而不是TABLE ACCESS FULL。但是，这种更改仅在列是NOT NULL时逻辑上等效的，因为null = null不会返回true。（就我个人而言，我更喜欢您编写条件的方式，但Oracle似乎讨厌ORS。）

如果这无济于事，请发布解释计划以识别实际问题。首先，修改过程并添加提示SELECT /*+ gather_plan_statistics */ TableA.*, ...。运行该过程，然后使用诸如select * from v$sql where lower(sql_fulltext) like '%gather_plan_statistics%';之类的查询找到相关的SQL_ID。最后，发布结果 select * from table(dbms_xplan.display_cursor(sql_id => '<sql_id from previous step>', format => 'allstats last'));。这将告诉我们执行计划，并可能是优化器正在做出的不当决定。