Oracle Query Plan Performance Issue

Question

The following query is listed in PL/SQL procedure.

SELECT e.data FROM extra e WHERE e.external_id in
    (SELECT * FROM TABLE (p_external_ids)).

Type p_external_ids- create or replace type "VARCHAR2TABLE" as table of VARCHAR2(4000 CHAR).

Oracle inefficiently executes a query using a full table scan. Tips on request did not help and the necessary indexes in place. Replacing a part with SELECT *hardcoded identifiers reduces the query execution time by factor of 20when the number of rows in the table is 200,000 .

For reference, it takes 0.3 seconds to complete the sentence SELECT * FROM TABLEand about 0.015 msone hardcoded identifier.

What are the suggested efficient methods (key search) for writing a stored procedure to extract data from a table for multiple identifiers ? The provided collection type should be used to pass to the list of identifiers of the stored procedure.

Answer 1

What hints did you try? Can you post a fast and slow query plan?

One common problem with using PL / SQL collections in SQL is that CBO often incorrectly estimates the number of items in the collection and chooses the wrong plan as a result. In these cases, it is often useful to use the CARDINALITY hint, i.e.

SELECT e.data 
  FROM extra e
 WHERE e.external_id IN (
    SELECT /*+ cardinality(ids 10) */ *
      FROM TABLE( p_external_ids ) ids
  )

10 P_EXTERNAL_IDS.

PL/SQL askTom.

EXTERNAL_ID? , EXTERNAL_ID NUMBER. ?

, , , , , . CARDINALITY , , .

? SQL, , CARDINALITY (, ).

Answer 2

, , , p_external_ids .

:

200, 100000 - , 20 , 4000 ( 100000). 1000 , 200000.

Answer 3

, , , !

---

:

:

Oracle Data Cartridge ( , , ). ( ), . CBO , .

, .

1)

We will create a small function that will receive and return a collection of our generic type VARCHAR2TABLE. This function does nothing with the collection itself; it's just a wrapper over it.

SQL> CREATE FUNCTION card_varchar2(
  2                  p_collection IN varchar2table
  3                  ) RETURN varchar2table IS
  4  BEGIN
  5     RETURN p_collection;
  6  END card_varchar2;
  7  /

Function created.

2) Enter the type of interface

Secondly, we will create an interface type specification that will be associated with our simple card_varchar2 function, as shown below.

SQL> CREATE TYPE card_varchar2_ot AS OBJECT (
  2
  3     dummy_attribute NUMBER,
  4
  5     STATIC FUNCTION ODCIGetInterfaces (
  6                     p_interfaces OUT SYS.ODCIObjectList
  7                     ) RETURN NUMBER,
  8
  9     STATIC FUNCTION ODCIStatsTableFunction (
 10                     p_function   IN  SYS.ODCIFuncInfo,
 11                     p_stats      OUT SYS.ODCITabFuncStats,
 12                     p_args       IN  SYS.ODCIArgDescList,
 13                     p_collection IN varchar2table
 14                     ) RETURN NUMBER
 15
 16  );
 17  /

Type created.

and body

SQL> CREATE TYPE BODY card_varchar2_ot AS
  2
  3     STATIC FUNCTION ODCIGetInterfaces (
  4                     p_interfaces OUT SYS.ODCIObjectList
  5                     ) RETURN NUMBER IS
  6     BEGIN
  7        p_interfaces := SYS.ODCIObjectList(
  8                           SYS.ODCIObject ('SYS', 'ODCISTATS2')
  9                           );
 10        RETURN ODCIConst.success;
 11     END ODCIGetInterfaces;
 12
 13     STATIC FUNCTION ODCIStatsTableFunction (
 14                     p_function   IN  SYS.ODCIFuncInfo,
 15                     p_stats      OUT SYS.ODCITabFuncStats,
 16                     p_args       IN  SYS.ODCIArgDescList,
 17                     p_collection IN  varchar2table
 18                     ) RETURN NUMBER IS
 19     BEGIN
 20        p_stats := SYS.ODCITabFuncStats(p_collection.COUNT);
 21        RETURN ODCIConst.success;
 22     END ODCIStatsTableFunction;
 23
 24  END;
 25  /

Type body created.

3) Associate a function with an interface type as follows.

SQL> ASSOCIATE STATISTICS WITH FUNCTIONS card_varchar2 USING card_varchar2_ot;

Statistics associated.

4) Now use this function as follows:

SQL> SELECT *
  2  FROM   TABLE(card_varchar2('A','B','C'));