Oracle - Full table scan even I have index for the query

ghz 8months ago ⋅ 59 views

Consider:

Enter image description here

SELECT  *
FROM
             ( SELECT
    key1,
    JSON_VALUE(json_col1, '$.jsonElement2'),
    JSON_VALUE(json_col1, '$.date1') ,
    JSON_VALUE(json_col1, '$.date2'),
    json_col1,
    created
FROM
    table_name
WHERE NOT JSON_EXISTS ( "json_col1" FORMAT JSON, '$?( (@.jsonElement1 in ("NAME")) )'
                 
)
AND JSON_EXISTS ( "json_col1" FORMAT JSON, '$?( (@.jsonElement2 in ("SAMPLE"))
&& (@.jsonElement3 in ("CORE")) )' )
AND JSON_EXISTS ( "json_col2" FORMAT JSON, '$?(  (
(@.json_element5 == "" || (!exists(@.json_element6))) ))'
)
AND CAST((JSON_VALUE(json_col1, '$.id')) AS INTEGER) > 1
ORDER BY
    CAST((JSON_VALUE(json_col1, '$.id')) AS INTEGER)
)
WHERE rownum <= 10

My index is:

CREATE INDEX "SCHEMA"."INDEX_ONE" ON "SCHEMA"."TABLE_NAME" 
(
JSON_VALUE("json_col1" FORMAT JSON , '$.jsonElement1' RETURNING VARCHAR2(300) ERROR ON ERROR NULL ON EMPTY), 
JSON_VALUE("json_col1" FORMAT JSON , '$.jsonElement2' RETURNING VARCHAR2(300) ERROR ON ERROR NULL ON EMPTY), 
JSON_VALUE("json_col1" FORMAT JSON , '$.jsonElement3' RETURNING VARCHAR2(300) ERROR ON ERROR NULL ON EMPTY),
JSON_VALUE("json_col2" FORMAT JSON , '$.json_element5' RETURNING VARCHAR2(300) ERROR ON ERROR NULL ON EMPTY), 
JSON_VALUE("json_col2" FORMAT JSON , '$.json_element6' RETURNING VARCHAR2(300) ERROR ON ERROR NULL ON EMPTY),
JSON_VALUE("json_col1" FORMAT JSON , '$.id' RETURNING VARCHAR2(300) ERROR ON ERROR NULL ON EMPTY)
)

Mentioned, the query & index that I am using. Why was my created index not picked by Oracle Database when I checked for the explain plan?

Answers

It's possible that your index was not picked by Oracle Database due to several reasons. Here are a few possibilities to consider:

  1. Index Visibility: Ensure that the index is visible to the optimizer. Sometimes, newly created or modified indexes may not be immediately visible to the optimizer. You can try running ANALYZE INDEX on your index to update the optimizer's statistics.

  2. Column Cardinality: Check the cardinality of the indexed columns. If the cardinality is low (i.e., many rows have the same value), Oracle may determine that using the index is not efficient for the query. In such cases, Oracle might opt for a full table scan instead.

  3. Predicate Selectivity: Review the predicates in your query and how they match with the indexed columns. If the predicates are not selective enough (i.e., they match a large portion of the table), Oracle may decide that using the index would not significantly reduce the number of rows to be retrieved.

  4. Cost-Based Optimization: Oracle's optimizer considers various factors such as the cost of different access paths (index scan vs. table scan), available indexes, and statistics about the data distribution. Sometimes, the optimizer's choice may not align with your expectations due to the complexity of these factors.

  5. Query Complexity: The complexity of your query, including multiple JSON operations and conditions, may affect the optimizer's decision. Oracle's optimizer strives to find the most efficient execution plan, but complex queries can sometimes lead to suboptimal choices.

To troubleshoot further, you can try the following:

  • Use the EXPLAIN PLAN statement to analyze the execution plan chosen by Oracle for your query. This will help you understand why Oracle did not use your index.
  • Experiment with different combinations of indexes or index columns to see if you can improve the query's performance.
  • Consider using hints to guide the optimizer's decision-making process. However, be cautious when using hints, as they can override the optimizer's choices and may not be necessary in all cases.

By analyzing the execution plan and considering the factors mentioned above, you can optimize your query and index usage for better performance.

#sql