OCI Autonomous Database: Slow Query Performance Despite Proper Indexing
I'm not sure how to approach After trying multiple solutions online, I still can't figure this out... I'm currently working with Oracle Cloud Infrastructure (OCI) and using an Autonomous Database. I've noticed that certain queries that should be fast are taking significantly longer than expected, even though I've created the appropriate indexes. For instance, the following SQL query is quite slow: ```sql SELECT * FROM orders WHERE customer_id = :customerId AND order_date >= :startDate; ``` I've ensured that there is an index on `customer_id` and `order_date`, and the statistics on the database are up-to-date. However, when I run this query for a `customer_id` that has a large number of records, it takes upwards of 10 seconds to return results. To troubleshoot, I've tried using the `EXPLAIN PLAN` command: ```sql EXPLAIN PLAN FOR SELECT * FROM orders WHERE customer_id = :customerId AND order_date >= :startDate; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); ``` The output shows that the query is using the index, but it seems to still be performing a full index scan, which is not what I expected. I've also looked into partitioning the `orders` table, but I'm unsure if that would actually help with performance in this case. Could this scenario arise from how OCI handles connections or any specific configuration in the Autonomous Database? Are there any best practices for optimizing similar queries? Any advice would be greatly appreciated! This is for a REST API running on CentOS. What's the correct way to implement this?