18. März 2014

Parallele Ausführung von Table Functions

Parallel Execution of Table Functions
Heute möchte ich etwas zum Thema Table Functions und deren parallele Ausführung schreiben. Im Zusammenspiel mit einem Kunden habe ich hier eine interessante Erfahrung gemacht - und die möchte ich gerne teilen.
Als Beispiel für eine Table Function nehme ich den String Tokenizer für PL/SQL, den ich vor einiger Zeit auf diesem Blog veröffentlicht habe. Nun braucht es eine Beispieltabelle, auf der wir arbeiten können: Das Schema SH enthält die Tabelle SUPPLEMENTARY_DEMOGRAPHICS, welche in der Spalte COMMENTS einige kleine Fließtexte enthält, die man sehr schön mit dem String Tokenizer zerlegen kann. Diese Tabelle duplizieren wir nun ein wenig ...
create table my_texte as select comments from SUPPLEMENTARY_DEMOGRAPHICS
/

insert into my_texte (select * from my_texte);
insert into my_texte (select * from my_texte);
insert into my_texte (select * from my_texte);
insert into my_texte (select * from my_texte);

select count(*) from my_texte
/

  COUNT(*)
----------
     72000
Darauf lässt sich nun arbeiten. Also nun der erste Test: Wieviele Tokens sind es insgesamt ...?
select count(*) from (
  select
    tk.token_text
  from 
    my_texte t,
    table(sh.string_tokenizer.all_tokens(comments, ' ')) tk
)
/

  COUNT(*)
----------
   1499824

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:07.61
Es sind knapp 1,5 Mio. Tokens und zum Zählen braucht (diese) Datenbank 7,6 Sekunden. Nun ist das ja eine Aufgabe, die sich eigentlich sehr gut parallelisieren lassen sollte (diese Maschine hat auch die nötige Anzahl CPUs) - probieren wir es ...
select count(*) from (
  select /*+ PARALLEL(4) */
    tk.token_text
  from 
    my_texte t,
    table(sh.string_tokenizer.all_tokens(comments, ' ')) tk
)
/

  COUNT(*)
----------
   1499824

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:07.86
Das war wohl nix. Aber moment: Table Functions haben noch das Syntaxelement PARALLEL_ENABLE, welches im PL/SQL Code gesetzt sein sollte - im vorliegenden Quellcode ist das nicht der Fall. Also ändern wir im Type STRING_TOKENIZER (vorerst nur dort) die Funktionsdeklaration von ALL_TOKENS wie folgt ...
create type string_tokenizer as object(
  
  :
  :

  static function all_tokens (p_string in varchar2, p_delim in varchar2) 
  return token_list pipelined parallel_enable
);
/
sho err

create or replace type body string_tokenizer is

  :
  :

  static function all_tokens (p_string in varchar2, p_delim in varchar2) 
  return token_list pipelined parallel_enable
 is
    l_st string_tokenizer := string_tokenizer(p_string, p_delim);
    l_startpos number;
    l_token    varchar2(4000);
  begin
    while l_st.has_more_tokens = 1 loop
      l_startpos := l_st.parser_current_pos;
      l_token := l_st.next_token();
      pipe row (token_t(l_token, l_startpos, nvl(length(l_token),0)));
    end loop;
    return;
  end;
end;
/
sho err
Erneuter Test ...
select count(*) from (
  select /*+ PARALLEL(4) */
    tk.token_text
  from 
    my_texte t,
    table(sh.string_tokenizer.all_tokens(comments, ' ')) tk
)
/

  COUNT(*)
----------
   1499824

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:07.97
Schaut man sich den Ausführungsplan genau an, so steht in den "Notes" zwar drin, dass er parallelisiert, tatsächlich merkt man aber nichts davon. Nun konnte ich aber nicht glauben, dass die Datenbank eine solche Abfrage nicht parallelisieren kann. Table Functions wurden mit Oracle9i eingeführt - und deren ursprüngliche Anwendungsgebiete waren Ladeprozesse ins Data Warehouse. Hier wurden oft Table Functions eingesetzt, die einen CURSOR als Parameter entgegennehmen - und genau das wenden wir nun mal auch für den STRING_TOKENIZER an. Der PL/SQL-Code wird erweitert, so dass der Type eine neue Member-Funktion ALL_TOKENS_CUR bekommt.
create type string_tokenizer as object(

  :
  :

  static function all_tokens_cur (p_cursor in sys_refcursor, p_delim in varchar2) 
  return token_list pipelined parallel_enable (partition p_cursor by any)
);
/
sho err

create or replace type body string_tokenizer is

  :
  :

  static function all_tokens_cur (
    p_cursor in sys_refcursor, p_delim in varchar2
  ) return token_list pipelined parallel_enable (partition p_cursor by any) is
    l_st       string_tokenizer;
    l_string   varchar2(4000);
    l_startpos number;
    l_token    varchar2(4000);
  begin
    loop
      fetch p_cursor into l_string;  
      exit when p_cursor%notfound;
     
      l_st := string_tokenizer(l_string, p_delim);
      while l_st.has_more_tokens = 1 loop
        l_startpos := l_st.parser_current_pos;
        l_token := l_st.next_token();
        pipe row (token_t(l_token, l_startpos, nvl(length(l_token),0)));
      end loop;
    end loop;
    return;
  end;
end;
/
sho err
Die neue Funktion ALL_TOKENS_CUR arbeitet im Prinzip genauso wie die vorhandene Funktion ALL_TOKENS - allerdings bekommt diese einen Cursor übergeben, der in der Funktion ausgelesen (fetch) wird. Neben dem PARALLEL_ENABLE-Schlüsselwort sollte bei einem Input-Parameter vom Typ Cursor nun auch ein PARTITION BY angegeben werden. Mit dieser Klausel sagt man dem Optimizer, wie er die Ergebnismenge des Cursors auf die Parallel Query Slaves aufteilen soll. Mit ANY lässt man den Optimizer quasi selbst entscheiden. Da die neue Funktion eine andere Schnittstelle hat, müssen wir auch die SQL Query umschreiben ...
select count(*) from (
  select /*+ PARALLEL(4)*/
    tk.token_text
  from 
    table(sh.string_tokenizer.all_tokens_cur(cursor(select comments from my_texte), ' ')) tk
)
/

  COUNT(*)
----------
   1499824

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:02.04
Na, und das sieht doch ganz anders aus .. schon an der Ausführungszeit erkennt man, dass hier parallelisiert wurde. Nochmal den Test mit einem PARALLEL(8) ...
select count(*) from (
  select /*+ PARALLEL(8)*/
    tk.token_text
  from 
    table(sh.string_tokenizer.all_tokens_cur(cursor(select comments from my_texte), ' ')) tk
)
/

  COUNT(*)
----------
   1499824

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:01.05
Es kann also durchaus eine gute Idee sein, eine Table-Funktion so zu schreiben, dass sie einen CURSOR als Parameter entgegennimmt. Der Vollständigkeit halber findet Ihr hier nochmal den kompletten Code des PL/SQL String Tokenizers inklusive der neuen CURSOR-Funktionen.
drop type clob_tokenizer
/

drop type string_tokenizer
/

drop type token_list
/

drop type token_t
/

create type token_t as object(
  token_text varchar2(4000),
  start_pos  number,
  length     number
)
/

create type token_list as table of token_t
/

create type clob_tokenizer as object(
  value_string       clob,
  delimiter          varchar2(10),
  parser_current_pos number,
  last_token         varchar2(4000),
  constructor function clob_tokenizer (p_string in clob, p_delim in varchar2) return self as result,
  member function has_more_tokens return number,
  member function next_token(self in out nocopy clob_tokenizer) return varchar2,
  static function all_tokens (p_string in clob, p_delim in varchar2) return token_list pipelined parallel_enable,
  static function all_tokens_cur (p_cursor in sys_refcursor, p_delim in varchar2) return token_list pipelined parallel_enable (partition p_cursor by any)
);
/
sho err

create or replace type body clob_tokenizer is
  constructor function clob_tokenizer (p_string in clob, p_delim in varchar2) return self as result as
  begin
    self.value_string := p_string;
    self.delimiter := p_delim;
    self.parser_current_pos := 1;
    self.last_token := null;
    return ;
  end;
    
  member function has_more_tokens return number as
  begin
    if self.parser_current_pos <= dbms_lob.getlength(value_string) then 
      return 1;
    else 
      return 0;
    end if;
  end;

  member function next_token(self in out nocopy clob_tokenizer) return varchar2 is
    l_next_delim_pos   number;
    l_token            varchar2(4000);
  begin
    if self.has_more_tokens() = 1 then 
      l_next_delim_pos := dbms_lob.instr(self.value_string, self.delimiter, self.parser_current_pos);
      if l_next_delim_pos = 0 then
        l_token := dbms_lob.substr(
          lob_loc => self.value_string, 
          amount  => (dbms_lob.getlength(self.value_string) - self.parser_current_pos) + 1,  
          offset  => self.parser_current_pos
        );
        parser_current_pos := dbms_lob.getlength(self.value_string) + 1; 
      else 
        l_token := dbms_lob.substr(
          lob_loc => self.value_string, 
          amount  => l_next_delim_pos  - self.parser_current_pos, 
          offset  => self.parser_current_pos
        );
        parser_current_pos := l_next_delim_pos + length(self.delimiter);
      end if;
    else 
      l_token := null;
    end if;
    self.last_token := l_token;
    return l_token;
  end;

  static function all_tokens (p_string in clob, p_delim in varchar2) return token_list pipelined parallel_enable is
    l_st clob_tokenizer := clob_tokenizer(p_string, p_delim);
    l_startpos number;
    l_token    varchar2(4000);
  begin
    while l_st.has_more_tokens = 1 loop
      l_startpos := l_st.parser_current_pos;
      l_token := l_st.next_token();
      pipe row (token_t(l_token, l_startpos, nvl(length(l_token),0)));
    end loop;
    return;
  end;

  static function all_tokens_cur (p_cursor in sys_refcursor, p_delim in varchar2) return token_list pipelined parallel_enable (partition p_cursor by any) is
    l_st       clob_tokenizer;
    l_string   clob;
    l_startpos number;
    l_token    varchar2(4000);
  begin
    loop
      fetch p_cursor into l_string;  
      exit when p_cursor%notfound;
     
      l_st := clob_tokenizer(l_string, p_delim);
      while l_st.has_more_tokens = 1 loop
        l_startpos := l_st.parser_current_pos;
        l_token := l_st.next_token();
        pipe row (token_t(l_token, l_startpos, nvl(length(l_token),0)));
      end loop;
    end loop;
    return;
  end;

end;
/
sho err

create type string_tokenizer as object(
  value_string       varchar2(4000),
  delimiter          varchar2(10),
  parser_current_pos number,
  last_token         varchar2(4000),
  constructor function string_tokenizer (p_string in varchar2, p_delim in varchar2) return self as result,
  member function has_more_tokens(self in out nocopy string_tokenizer) return number,
  member function next_token(self in out nocopy string_tokenizer) return varchar2,
  static function all_tokens (p_string in varchar2, p_delim in varchar2) return token_list pipelined parallel_enable,
  static function all_tokens_cur (p_cursor in sys_refcursor, p_delim in varchar2) return token_list pipelined parallel_enable (partition p_cursor by any)
);
/
sho err

create or replace type body string_tokenizer is
  constructor function string_tokenizer (p_string in varchar2, p_delim in varchar2) return self as result as
  begin
    self.value_string := p_string;
    self.delimiter := p_delim;
    self.parser_current_pos := 1;
    self.last_token := null;
    return ;
  end;
    
  member function has_more_tokens(self in out nocopy string_tokenizer) return number as
  begin
    if self.parser_current_pos <= length(value_string) then 
      return 1;
    else 
      return 0;
    end if;
  end;

  member function next_token(self in out nocopy string_tokenizer) return varchar2 as
    l_next_delim_pos   number;
    l_next_enclose_pos number;
    l_token            varchar2(4000);
  begin
    if self.has_more_tokens() = 1 then 
      l_next_delim_pos := instr(self.value_string, self.delimiter, self.parser_current_pos);
      if l_next_delim_pos = 0 then
        l_token := substr(value_string, self.parser_current_pos);
        parser_current_pos := length(self.value_string) + 1; 
      else 
        l_token := substr(self.value_string, self.parser_current_pos, l_next_delim_pos  - self.parser_current_pos);
        parser_current_pos := l_next_delim_pos + length(self.delimiter);
      end if;
    else 
      l_token := null;
    end if;
    self.last_token := l_token;
    return l_token;
  end;

  static function all_tokens (p_string in varchar2, p_delim in varchar2) return token_list pipelined parallel_enable is
    l_st string_tokenizer := string_tokenizer(p_string, p_delim);
    l_startpos number;
    l_token    varchar2(4000);
  begin
    while l_st.has_more_tokens = 1 loop
      l_startpos := l_st.parser_current_pos;
      l_token := l_st.next_token();
      pipe row (token_t(l_token, l_startpos, nvl(length(l_token),0)));
    end loop;
    return;
  end;

  static function all_tokens_cur (p_cursor in sys_refcursor, p_delim in varchar2) return token_list pipelined parallel_enable (partition p_cursor by any) is
    l_st       string_tokenizer;
    l_string   varchar2(4000);
    l_startpos number;
    l_token    varchar2(4000);
  begin
    loop
      fetch p_cursor into l_string;  
      exit when p_cursor%notfound;
     
      l_st := string_tokenizer(l_string, p_delim);
      while l_st.has_more_tokens = 1 loop
        l_startpos := l_st.parser_current_pos;
        l_token := l_st.next_token();
        pipe row (token_t(l_token, l_startpos, nvl(length(l_token),0)));
      end loop;
    end loop;
    return;
  end;
end;
/
sho err
Viel Spaß beim Ausprobieren.
Today I'd like to write something on Table Functions and their parallel execution. Together with a customer I had a very interesting experience, which I'd like to share.
We'll take the String Tokenizer for PL/SQL, which I published on this blog some time ago, as an example for a table function. Next, we need some data: The sample schema SH contains the table SUPPLEMENTARY_DEMOGRAPHICS and its column COMMENTS contains some nice texts which we'll shred with the String Tokenizer. This is a task which should be very easy to parallelize. In order to really see an effect, we'll duplicate the table rows a few times ...
create table my_texte as select comments from SUPPLEMENTARY_DEMOGRAPHICS
/

insert into my_texte (select * from my_texte);
insert into my_texte (select * from my_texte);
insert into my_texte (select * from my_texte);
insert into my_texte (select * from my_texte);

select count(*) from my_texte
/

  COUNT(*)
----------
     72000
Ok ... lets's start then. First, we want to see, how man tokens there are and how long it takes.
select count(*) from (
  select
    tk.token_text
  from 
    my_texte t,
    table(sh.string_tokenizer.all_tokens(comments, ' ')) tk
)
/

  COUNT(*)
----------
   1499824

1 row selected.

Elapsed: 00:00:07.61
So, we habe about 1.500.000 tokens and a single thread needs (on this machine) about 7.5 seconds to count them. Now, since this kind of task should parallelize very easily, let's try it (assumed, that the database machine has the needed amount of CPUs).
select count(*) from (
  select /*+ PARALLEL(4) */
    tk.token_text
  from 
    my_texte t,
    table(sh.string_tokenizer.all_tokens(comments, ' ')) tk
)
/

  COUNT(*)
----------
   1499824

1 row selected.

Elapsed: 00:00:07.86
No real effect. But one moment: Looking into the PL/SQL documentation for table functions, we can see that there is the PARALLEL_ENABLE keyword which we need to add to the function declaration. So - we adjust the PL/SQL code of the STRING_TOKENIZER type (at the moment only this one) as follows ...
create type string_tokenizer as object(
  
  :
  :

  static function all_tokens (p_string in varchar2, p_delim in varchar2) 
  return token_list pipelined parallel_enable
);
/
sho err

create or replace type body string_tokenizer is

  :
  :

  static function all_tokens (p_string in varchar2, p_delim in varchar2) 
  return token_list pipelined parallel_enable
 is
    l_st string_tokenizer := string_tokenizer(p_string, p_delim);
    l_startpos number;
    l_token    varchar2(4000);
  begin
    while l_st.has_more_tokens = 1 loop
      l_startpos := l_st.parser_current_pos;
      l_token := l_st.next_token();
      pipe row (token_t(l_token, l_startpos, nvl(length(l_token),0)));
    end loop;
    return;
  end;
end;
/
sho err
Testing ...
select count(*) from (
  select /*+ PARALLEL(4) */
    tk.token_text
  from 
    my_texte t,
    table(sh.string_tokenizer.all_tokens(comments, ' ')) tk
)
/

  COUNT(*)
----------
   1499824

1 row selected.

Elapsed: 00:00:07.97
The execution plan tells us (in the Notes Section), that there was a parallel degree - but looking at the execution times, we don't see a real effect. I did not want to accept that the database is unable to parallelize this kind of task - so I investigated a bit of time into rewriting the table functions. Table Functions were introducted in Oracle9i - and its primary focus area was the Loading of Data Warehouses. In these scenarions, Table Functions often take a cursor as the input parameter - so I tried this approach also for the String Tokenizer. We'll add a new MEMBER function to the type declaration which does basically the same as the ALL_TOKENS method, but taking a SYS_REFCURSOR as input.
create type string_tokenizer as object(

  :
  :

  static function all_tokens_cur (p_cursor in sys_refcursor, p_delim in varchar2) 
  return token_list pipelined parallel_enable (partition p_cursor by any)
);
/
sho err

create or replace type body string_tokenizer is

  :
  :

  static function all_tokens_cur (
    p_cursor in sys_refcursor, p_delim in varchar2
  ) return token_list pipelined parallel_enable (partition p_cursor by any) is
    l_st       string_tokenizer;
    l_string   varchar2(4000);
    l_startpos number;
    l_token    varchar2(4000);
  begin
    loop
      fetch p_cursor into l_string;  
      exit when p_cursor%notfound;
     
      l_st := string_tokenizer(l_string, p_delim);
      while l_st.has_more_tokens = 1 loop
        l_startpos := l_st.parser_current_pos;
        l_token := l_st.next_token();
        pipe row (token_t(l_token, l_startpos, nvl(length(l_token),0)));
      end loop;
    end loop;
    return;
  end;
end;
/
sho err
In addition to the PARALLEL_ENABLE keyword we can also add the PARTITION BY clause, in which we can determine how the optimizer should distribute the cursor result set to the parallel query slaves. We choose ANY, which basically lets the optimizer decide. Since this function has a different signature, we also need to rewrite our SQL query.
select count(*) from (
  select /*+ PARALLEL(4)*/
    tk.token_text
  from 
    table(sh.string_tokenizer.all_tokens_cur(cursor(select comments from my_texte), ' ')) tk
)
/

  COUNT(*)
----------
   1499824

1 row selected.

Elapsed: 00:00:02.04
Ok .. this looks good. Seems that the parallelization now works. Let's do the second test with PARALLEL(8).
select count(*) from (
  select /*+ PARALLEL(8)*/
    tk.token_text
  from 
    table(sh.string_tokenizer.all_tokens_cur(cursor(select comments from my_texte), ' ')) tk
)
/

  COUNT(*)
----------
   1499824

1 row selected.

Elapsed: 00:00:01.05
Note that this will work only, if you really have the necessary CPU resources on your system. If there is only one CPU available, you will see differenz numbers. But we can see, that it can be a very good idea to rewrite a table function to use Cursors as input arguments. The parallelization behaviour for large amounts of data is much better. For the records: Here is the complete STRING_TOKENIZER and CLOB_TOKENIZER PL/SQL code including the new ALL_TOKENS_CUR functions.
drop type clob_tokenizer
/

drop type string_tokenizer
/

drop type token_list
/

drop type token_t
/

create type token_t as object(
  token_text varchar2(4000),
  start_pos  number,
  length     number
)
/

create type token_list as table of token_t
/

create type clob_tokenizer as object(
  value_string       clob,
  delimiter          varchar2(10),
  parser_current_pos number,
  last_token         varchar2(4000),
  constructor function clob_tokenizer (p_string in clob, p_delim in varchar2) return self as result,
  member function has_more_tokens return number,
  member function next_token(self in out nocopy clob_tokenizer) return varchar2,
  static function all_tokens (p_string in clob, p_delim in varchar2) return token_list pipelined parallel_enable,
  static function all_tokens_cur (p_cursor in sys_refcursor, p_delim in varchar2) return token_list pipelined parallel_enable (partition p_cursor by any)
);
/
sho err

create or replace type body clob_tokenizer is
  constructor function clob_tokenizer (p_string in clob, p_delim in varchar2) return self as result as
  begin
    self.value_string := p_string;
    self.delimiter := p_delim;
    self.parser_current_pos := 1;
    self.last_token := null;
    return ;
  end;
    
  member function has_more_tokens return number as
  begin
    if self.parser_current_pos <= dbms_lob.getlength(value_string) then 
      return 1;
    else 
      return 0;
    end if;
  end;

  member function next_token(self in out nocopy clob_tokenizer) return varchar2 is
    l_next_delim_pos   number;
    l_token            varchar2(4000);
  begin
    if self.has_more_tokens() = 1 then 
      l_next_delim_pos := dbms_lob.instr(self.value_string, self.delimiter, self.parser_current_pos);
      if l_next_delim_pos = 0 then
        l_token := dbms_lob.substr(
          lob_loc => self.value_string, 
          amount  => (dbms_lob.getlength(self.value_string) - self.parser_current_pos) + 1,  
          offset  => self.parser_current_pos
        );
        parser_current_pos := dbms_lob.getlength(self.value_string) + 1; 
      else 
        l_token := dbms_lob.substr(
          lob_loc => self.value_string, 
          amount  => l_next_delim_pos  - self.parser_current_pos, 
          offset  => self.parser_current_pos
        );
        parser_current_pos := l_next_delim_pos + length(self.delimiter);
      end if;
    else 
      l_token := null;
    end if;
    self.last_token := l_token;
    return l_token;
  end;

  static function all_tokens (p_string in clob, p_delim in varchar2) return token_list pipelined parallel_enable is
    l_st clob_tokenizer := clob_tokenizer(p_string, p_delim);
    l_startpos number;
    l_token    varchar2(4000);
  begin
    while l_st.has_more_tokens = 1 loop
      l_startpos := l_st.parser_current_pos;
      l_token := l_st.next_token();
      pipe row (token_t(l_token, l_startpos, nvl(length(l_token),0)));
    end loop;
    return;
  end;

  static function all_tokens_cur (p_cursor in sys_refcursor, p_delim in varchar2) return token_list pipelined parallel_enable (partition p_cursor by any) is
    l_st       clob_tokenizer;
    l_string   clob;
    l_startpos number;
    l_token    varchar2(4000);
  begin
    loop
      fetch p_cursor into l_string;  
      exit when p_cursor%notfound;
     
      l_st := clob_tokenizer(l_string, p_delim);
      while l_st.has_more_tokens = 1 loop
        l_startpos := l_st.parser_current_pos;
        l_token := l_st.next_token();
        pipe row (token_t(l_token, l_startpos, nvl(length(l_token),0)));
      end loop;
    end loop;
    return;
  end;

end;
/
sho err

create type string_tokenizer as object(
  value_string       varchar2(4000),
  delimiter          varchar2(10),
  parser_current_pos number,
  last_token         varchar2(4000),
  constructor function string_tokenizer (p_string in varchar2, p_delim in varchar2) return self as result,
  member function has_more_tokens(self in out nocopy string_tokenizer) return number,
  member function next_token(self in out nocopy string_tokenizer) return varchar2,
  static function all_tokens (p_string in varchar2, p_delim in varchar2) return token_list pipelined parallel_enable,
  static function all_tokens_cur (p_cursor in sys_refcursor, p_delim in varchar2) return token_list pipelined parallel_enable (partition p_cursor by any)
);
/
sho err

create or replace type body string_tokenizer is
  constructor function string_tokenizer (p_string in varchar2, p_delim in varchar2) return self as result as
  begin
    self.value_string := p_string;
    self.delimiter := p_delim;
    self.parser_current_pos := 1;
    self.last_token := null;
    return ;
  end;
    
  member function has_more_tokens(self in out nocopy string_tokenizer) return number as
  begin
    if self.parser_current_pos <= length(value_string) then 
      return 1;
    else 
      return 0;
    end if;
  end;

  member function next_token(self in out nocopy string_tokenizer) return varchar2 as
    l_next_delim_pos   number;
    l_next_enclose_pos number;
    l_token            varchar2(4000);
  begin
    if self.has_more_tokens() = 1 then 
      l_next_delim_pos := instr(self.value_string, self.delimiter, self.parser_current_pos);
      if l_next_delim_pos = 0 then
        l_token := substr(value_string, self.parser_current_pos);
        parser_current_pos := length(self.value_string) + 1; 
      else 
        l_token := substr(self.value_string, self.parser_current_pos, l_next_delim_pos  - self.parser_current_pos);
        parser_current_pos := l_next_delim_pos + length(self.delimiter);
      end if;
    else 
      l_token := null;
    end if;
    self.last_token := l_token;
    return l_token;
  end;

  static function all_tokens (p_string in varchar2, p_delim in varchar2) return token_list pipelined parallel_enable is
    l_st string_tokenizer := string_tokenizer(p_string, p_delim);
    l_startpos number;
    l_token    varchar2(4000);
  begin
    while l_st.has_more_tokens = 1 loop
      l_startpos := l_st.parser_current_pos;
      l_token := l_st.next_token();
      pipe row (token_t(l_token, l_startpos, nvl(length(l_token),0)));
    end loop;
    return;
  end;

  static function all_tokens_cur (p_cursor in sys_refcursor, p_delim in varchar2) return token_list pipelined parallel_enable (partition p_cursor by any) is
    l_st       string_tokenizer;
    l_string   varchar2(4000);
    l_startpos number;
    l_token    varchar2(4000);
  begin
    loop
      fetch p_cursor into l_string;  
      exit when p_cursor%notfound;
     
      l_st := string_tokenizer(l_string, p_delim);
      while l_st.has_more_tokens = 1 loop
        l_startpos := l_st.parser_current_pos;
        l_token := l_st.next_token();
        pipe row (token_t(l_token, l_startpos, nvl(length(l_token),0)));
      end loop;
    end loop;
    return;
  end;
end;
/
sho err
Have fun!

Kommentare:

Unknown hat gesagt…

Wenn in der table function das Schema gewechselt wird, funktioniert das mit parallel_enable, oder gibt es Seiteneffekte?

Carsten Czarski hat gesagt…

Hallo,

normalerweise sollte das keine Seiteneffekte haben. Ein Schemawechsel wirkt sich in erster Linie auf Privilegien aus - nicht auf die Ausführungspläne des Optimizers.

Aber bekanntlich steckt der Teufel oft im Detail - wie genau sieht denn der "Schemawechsel" aus ...?

Beste Grüße

-Carsten

Unknown hat gesagt…

Wir haben mehrere 1000 schemas mit identischer Struktur durch die wir mit table functions springen und Daten extrahieren.

Schemawechsel via:
alter session set current_schema....;

Ich hatte parallel enabled und sah auf einmal Daten die nicht zusammenpassten. Leider konnte ich es noch nicht reproduzieren.

Beliebte Postings