How many times does my table function execute?

A left correlation join occurs when you pass as an argument to your table function a column value from a table or view referenced to the left in the table clause. This technique is used with XMLTABLE and JSON_TABLE built-in functions, but also applies to your own table functions.

Here's the thing to remember:

The table function will be called for each row in the table/view that is providing the column to the function. 

Clearly, this could cause some performance issues, so be sure that is what you want and need to do.

The following code demonstrates this behavior, for both pipelined and non-pipelined functions.

CREATE TABLE things
(
   thing_id     NUMBER,
   thing_name   VARCHAR2 (100)
)
/

BEGIN
   INSERT INTO things VALUES (1, 'Thing 1');
   INSERT INTO things VALUES (2, 'Thing 2');
   COMMIT;
END;
/

CREATE OR REPLACE TYPE numbers_t IS TABLE OF NUMBER
/

CREATE OR REPLACE FUNCTION more_numbers (id_in IN NUMBER)
   RETURN numbers_t
IS
   l_numbers   numbers_t := numbers_t ();
BEGIN
   l_numbers.EXTEND (id_in * 5);

   FOR indx IN 1 .. id_in * 5
   LOOP
      l_numbers (indx) := indx;
   END LOOP;

   DBMS_OUTPUT.put_line ('more numbers');
   RETURN l_numbers;
END;
/

BEGIN
   FOR rec IN (SELECT th.thing_name, t.COLUMN_VALUE thing_number
                 FROM things th, TABLE (more_numbers (th.thing_id)) t)
   LOOP
      DBMS_OUTPUT.put_line ('more numbers ' || rec.thing_number);
   END LOOP;
END;
/

more numbers
more numbers
more numbers 1
more numbers 2
more numbers 3
more numbers 4
more numbers 5
more numbers 1
more numbers 2
more numbers 3
more numbers 4
more numbers 5
more numbers 6
more numbers 7
more numbers 8
more numbers 9
more numbers 10

CREATE OR REPLACE FUNCTION more_numbers (id_in IN NUMBER)
   RETURN numbers_t PIPELINED
IS
BEGIN
   DBMS_OUTPUT.put_line ('more numbers');
   FOR indx IN 1 .. id_in * 5
   LOOP
      PIPE ROW (indx);
   END LOOP;
   RETURN;
END;
/

BEGIN
   FOR rec IN (SELECT th.thing_name, t.COLUMN_VALUE thing_number
                 FROM things th, TABLE (more_numbers (th.thing_id)) t)
   LOOP
      DBMS_OUTPUT.put_line ('more numbers ' || rec.thing_number);
   END LOOP;
END;
/

more numbers
more numbers
more numbers 1
more numbers 2
more numbers 3
more numbers 4
more numbers 5
more numbers 1
more numbers 2
more numbers 3
more numbers 4
more numbers 5
more numbers 6
more numbers 7
more numbers 8
more numbers 9
more numbers 10