CODEWITHSUNDEEP
sqloracle-databaseplsql
Markus
Markus

Reputation: 1213

Join much slower using table() function

I am attempting to use a table() function on an object in order to do a join within a PL/SQL function. When using this function, a query may take up to 20 minutes to complete; when I enter the data directly into a table instead, it takes less than 5 seconds. I have not been able to figure out why there is such a significant difference, but my best hunch is that the index on the column from the joining table is not being used. The column definition for the tables and for the objects is the same.

Here is some example code:

create or replace type VARCHAR20_TYPE is OBJECT
(
  val varchar2(20 byte);
);

create or replace type VARCHAR20_TABLE is table of VARCHAR20_TYPE;


create or replace FUNCTION test_function( 
    in_project_ids VARCHAR20_TABLE
  ) RETURN INTEGER
  IS
    l_result INTEGER;
  BEGIN

    SELECT count(*) into l_result FROM project p JOIN TABLE(in_project_ids) t ON p.project_id = t.val;      
    RETURN l_result;

  END;

If I were to replace in_project_ids in the above example with a join to a real table with the same column definition, it significantly improves the performance of the function.

Upvotes: 1

Views: 1944

Answers (1)

DazzaL
DazzaL

Reputation: 21993

this is to be expected. when dealing with in memory arrays like this Oracle will assume 8k rows will be in that table.

try this to help it: 

SELECT /*+ cardinality(t, 20) */ count(*) into l_result FROM project p JOIN TABLE(in_project_ids) t ON p.project_id = t.val;

where 20 should be a rough guess on the actual number of entries. this is one of the edge cases where hinting is "ok" (and required to help the optimizer).

edit

eg:

SQL> explain plan for SELECT /*+ cardinality(t, 1) */ * FROM project p JOIN TABLE(VARCHAR20_TABLE()) t ON p.project_id = t.val;

Explained.

SQL> select * From table(dbms_xplan.display);

Plan hash value: 858605789

--------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |              |     1 |    27 |    30   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                           |              |       |       |            |          |
|   2 |   NESTED LOOPS                          |              |     1 |    27 |    30   (0)| 00:00:01 |
|   3 |    COLLECTION ITERATOR CONSTRUCTOR FETCH|              |     1 |     2 |    29   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN                    | SYS_C0011177 |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID           | PROJECT      |     1 |    25 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("P"."PROJECT_ID"=TO_NUMBER(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)))

Note
-----
   - dynamic sampling used for this statement (level=2)

21 rows selected.

SQL> explain plan for SELECT * FROM project p JOIN TABLE(VARCHAR20_TABLE()) t ON p.project_id = t.val;

Explained.

SQL> select * From table(dbms_xplan.display);

Plan hash value: 583089723

--------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |         |  8168 |   215K|    33   (4)| 00:00:01 |
|*  1 |  HASH JOIN                             |         |  8168 |   215K|    33   (4)| 00:00:01 |
|   2 |   TABLE ACCESS FULL                    | PROJECT |  2000 | 50000 |     3   (0)| 00:00:01 |
|   3 |   COLLECTION ITERATOR CONSTRUCTOR FETCH|         |  8168 | 16336 |    29   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------


   1 - access("P"."PROJECT_ID"=TO_NUMBER(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)))

Note
-----
   - dynamic sampling used for this statement (level=2)

19 rows selected.

a trivial example but note the "Rows" on the collection fetch = 8168 without the hint and the change in plan as a result. check the explain plan with the real table vs the collection vs the hinted collection and helpfully, with a reasonable cardinality hint number your plan and performance should improve.

Upvotes: 5

Related Questions