CODEWITHSUNDEEP
oracle-databasestored-proceduresindexing
ADTC
ADTC

Reputation: 10121

Stored Procedure does not use available index on table (Oracle)

A SELECT query in my stored procedure takes 3 seconds to execute when the table queried has no indexes. This is true in both when executing the query in Toad Editor and when calling the stored procedure. The Explain Plan shows that a full table scan is done.

When an index is added, the same query in Toad Editor returns results instantaneously (just a few milliseconds). The Explain Plan shows that the index is used. However, even when the index is present, the query still takes 3 seconds in the stored procedure. It looks like the query uses a full table scan when executed in stored procedure despite having an index that can speed it up. Why?

I have tried with indexes on different columns with different orders. The same results persist in all cases.

In the stored procedure, the results of the query are collected using BULK COLLECT INTO. Does this make a difference? Also, The stored procedure is inside a package.

The query is a very simple SELECT statement, like this:

  SELECT MY_COL, COUNT (MY_COL)
    /* this line is only in stored proc */ BULK COLLECT INTO mycollection
    FROM MY_TABLE
   WHERE ANOTHER_COL = '123' /* or ANOTHER_COL = filterval (which is type NUMBER) */
GROUP BY MY_COL
ORDER BY MY_COL

Upvotes: 1

Views: 993

Answers (1)

Rusty
Rusty

Reputation: 2138

Without source code we can only guess...

So I suspect it's because in Toad you get just first 500 rows (500 is default buffer size in Toad) but in stored proc you fetch ALL rows into collection. So fetching probably takes most of 3 sec time. Especially if there are nested loops iny our query.

Update: It might also be implicit type conversion in where condition

Upvotes: 1

Related Questions