Oracle SQL - Performance Issues with Partitioned Table Queries and Dynamic SQL
I'm having trouble with I'm experiencing significant performance issues when querying a partitioned table using dynamic SQL in Oracle 19c... The queries are taking much longer to execute than expected, especially when filtering based on a date range. For instance, when I execute the following dynamic SQL to fetch data from a partitioned table "sales_data", it seems to ignore the partition pruning, leading to full table scans: ```sql DECLARE v_sql VARCHAR2(4000); BEGIN v_sql := 'SELECT * FROM sales_data WHERE sale_date BETWEEN :start_date AND :end_date'; EXECUTE IMMEDIATE v_sql USING TO_DATE('2023-01-01', 'YYYY-MM-DD'), TO_DATE('2023-12-31', 'YYYY-MM-DD'); END; ``` I've verified that the `sale_date` column is the partition key, and the table is partitioned by range on `sale_date`. When I run a static query like the following, the performance is significantly better: ```sql SELECT * FROM sales_data WHERE sale_date BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD') AND TO_DATE('2023-12-31', 'YYYY-MM-DD'); ``` In this case, Oracle applies partition pruning as expected. I've also tried using bind variables directly in the dynamic SQL without success, and I checked the execution plan with `EXPLAIN PLAN` for both queries. The dynamic SQL plan shows a full scan, while the static query correctly shows partitioning. Is there something specific about dynamic SQL in Oracle that prevents partition pruning? Are there any best practices or configuration settings I should be aware of that could help optimize this? Any insights would be greatly appreciated. Could this be a known issue?