SQL & PL/SQL

Hi,

After reading Databases: Global temporary tables or collections I see collections are fast but what about serving colection over table() with globally defined type ?

Previously thought that if collection is fast it must be also fast served over table() but this is not true like i see.

Can you tell me where is all performance lost ? Is it context switching ?

My test:

First you need this:

GRANT SELECT ON SYS.v_$sesstat TO your_user
/
GRANT SELECT ON SYS.v_$statname TO your_user
/
GRANT SELECT ON SYS.v_$session TO your_user
/

Next declare test objects and tables:

/* persistent table */

DROP TABLE pt_id_name
/
CREATE TABLE pt_id_name (
ID NUMBER,
name VARCHAR2(100))
/
CREATE UNIQUE INDEX pt_id_name_index1 ON pt_id_name (ID)
/
CREATE UNIQUE INDEX pt_id_name_index2 ON pt_id_name (name)
/
/* temporary table */
DROP TABLE gtt_id_name
/
CREATE GLOBAL TEMPORARY TABLE gtt_id_name (
ID NUMBER,
name VARCHAR2(100))
/
CREATE UNIQUE INDEX gtt_id_name_index1 ON gtt_id_name (ID)
/
CREATE UNIQUE INDEX gtt_id_name_index2 ON gtt_id_name (name)
/
/* type for table collection */

DROP TYPE ctt_id_name_table_t
/
DROP TYPE ctt_id_name_record_t
/
CREATE TYPE ctt_id_name_record_t AS OBJECT
(
ID NUMBER,
name VARCHAR2(100))
/
CREATE TYPE ctt_id_name_table_t AS TABLE OF ctt_id_name_record_t
/

Now run this is anonymous block for test:

DECLARE
  c_size PLS_INTEGER := 2000;

  ctt_collection ctt_id_name_table_t := ctt_id_name_table_t();
  --
  l_record pt_id_name%ROWTYPE;
  --
  l_start PLS_INTEGER;
  l_count PLS_INTEGER;
  l_index pt_id_name.name%TYPE;

  PROCEDURE show_memory
  
   IS
    l_memory VARCHAR2(100);
  BEGIN
    SELECT nm.name || ': ' || TO_CHAR(st.VALUE, '999,999,999,999')
      INTO l_memory
      FROM SYS.v_$sesstat st, SYS.v_$statname nm
     WHERE st.statistic# = nm.statistic#
       AND st.SID = SYS_CONTEXT('USERENV', 'SID')
       AND nm.name = 'session pga memory';
  
    DBMS_OUTPUT.put_line(l_memory);
  END show_memory;

  PROCEDURE show_elapsed(NAME_IN IN VARCHAR2) IS
  BEGIN
    DBMS_OUTPUT.put_line('"' || NAME_IN || '": ' ||
                         TO_CHAR((DBMS_UTILITY.get_cpu_time - l_start) / 100) ||
                         ' seconds');
  END show_elapsed;
BEGIN
  DBMS_SESSION.free_unused_user_memory;
  DBMS_OUTPUT.put_line('Compare persistent table, global temporary table and collection for ' ||
                       c_size || ' rows of data');
  show_memory;
  --
  -- POPULATE STRUCTURES
  --
  l_start := DBMS_UTILITY.get_cpu_time;

  INSERT INTO gtt_id_name
    select rownum, 'WOW' || rownum from dual connect by rownum <= c_size;

  show_elapsed('Insert into persistent table');
  show_memory;
  --
  l_start := DBMS_UTILITY.get_cpu_time;

  INSERT INTO pt_id_name
    select rownum, 'WOW' || rownum from dual connect by rownum <= c_size;

  show_elapsed('Insert into temporary table');
  show_memory;
  --
  l_start := DBMS_UTILITY.get_cpu_time;

  select ctt_id_name_record_t(rownum, 'WOW' || rownum) bulk collect
    into ctt_collection
    from dual
  connect by rownum <= c_size;

  show_elapsed('Insert into collection');
  show_memory;
  --
  -- COUNT OF ROWS
  --
  DBMS_OUTPUT.put_line('');
  l_start := DBMS_UTILITY.get_cpu_time;

  SELECT COUNT(*) INTO l_count FROM pt_id_name;

  show_elapsed('Get count of rows in persistent table');
  l_start := DBMS_UTILITY.get_cpu_time;

  SELECT COUNT(*) INTO l_count FROM gtt_id_name;

  show_elapsed('Get count of rows in temporary table');
  l_start := DBMS_UTILITY.get_cpu_time;

  SELECT COUNT(*) INTO l_count FROM table(ctt_collection);

  show_elapsed('Get count of rows in table(collection)');
  --
  -- LOOKUP ROW BY INDEX
  --
  DBMS_OUTPUT.put_line('');
  l_start := DBMS_UTILITY.get_cpu_time;

  FOR indx IN 1 .. c_size LOOP
    SELECT * INTO l_record FROM pt_id_name WHERE ID = indx;
  END LOOP;

  show_elapsed('Read each row in persistent table');
  --
  l_start := DBMS_UTILITY.get_cpu_time;

  FOR indx IN 1 .. c_size LOOP
    SELECT * INTO l_record FROM gtt_id_name WHERE ID = indx;
  END LOOP;

  show_elapsed('Read each row in temporary table');
  --
  l_start := DBMS_UTILITY.get_cpu_time;

  FOR indx IN 1 .. c_size LOOP
    SELECT * INTO l_record FROM table(ctt_collection) WHERE ID = indx;
  END LOOP;

  show_elapsed('Read each row in table(collection)');
  --
  -- LOOKUP ROW BY NAME
  --
  DBMS_OUTPUT.put_line('');
  l_start := DBMS_UTILITY.get_cpu_time;

  FOR indx IN 1 .. c_size LOOP
    SELECT * INTO l_record FROM pt_id_name WHERE name = 'WOW' || indx;
  END LOOP;

  show_elapsed('Lookup row in persistent table by name (unique index)');
  --
  l_start := DBMS_UTILITY.get_cpu_time;

  FOR indx IN 1 .. c_size LOOP
    SELECT * INTO l_record FROM gtt_id_name WHERE name = 'WOW' || indx;
  END LOOP;

  show_elapsed('Lookup row in temporary table by name (unique index)');
  --
  l_start := DBMS_UTILITY.get_cpu_time;

  FOR indx IN 1 .. c_size LOOP
    SELECT *
      INTO l_record
      FROM table(ctt_collection)
     WHERE name = 'WOW' || indx;
  END LOOP;

  show_elapsed('Lookup row in collection by name (from table())');
  --
  ROLLBACK;
END;

Thanks