functional index - create with parallel sort

Posted in 2010

Cesar Inacio — Fri, 03 Sep 2010 01:50:37 — source: IIUG Forums & Mailing Lists

Hi, IDS 11.50 FC7 If a table have a functional Index, and the table don't have fragmentation, but the PSORT_NPROCS is set with value > 1 (or PDQ >1), the index is created by only one and single thread. Why this creation don't run with more sort threads in parallel? Once the functions are "NOT VARIANT" , I don't see why not... There is some reason , what I don't see ? Cesar

↪ replying to Cesar Inacio

Celso Cabral — Fri, 03 Sep 2010 17:46:45 — source: IIUG Forums & Mailing Lists

Cesar, Are you using PDQ in which level? How are the Parallel Database Query (PDQ) Configuration Parameters in your instance? You should use PSORT_NPROCS and PDQ >1, actually. In my environment to create indexes in parallel I usually use PDQ Priority High, but the MAX_PDQPRIORITY is set to 25, if you would like to use all the resources configured in PDQ, set MAX_PDQPRIORITY to 100 and use set pdqpriority high (or 100). If I really understood your problem, I think it can help you. Celso Cabral Coimbra Administrador de Banco de Dados ClearTech Ltda "Trust at the heart of Communications" Tel. (11) 3576-4509 -----Mensagem original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de Cesar Inacio Martins Enviada em: quinta-feira, 2 de setembro de 2010 22:51 Para: ids@iiug.org Assunto: functional index - create with parallel sort [21160] Hi, IDS 11.50 FC7 If a table have a functional Index, and the table don't have fragmentation, but the PSORT_NPROCS is set with value > 1 (or PDQ >1), the index is created by only one and single thread. Why this creation don't run with more sort threads in parallel? Once the functions are "NOT VARIANT" , I don't see why not... There is some reason , what I don't see ? Cesar ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Celso Cabral

Cesar Inacio — Sat, 04 Sep 2010 00:51:49 — source: IIUG Forums & Mailing Lists

Hi Celso, In the last case what I get this situation is over a Growth Edition , with PDQ and PSORT_NPROCS set.. But , when I says functional indexes, I means indexes based on functions, something like: create index xyz on tablexyz ( calc_day_payment(day_expires)). And this over a non-fragmented table.. (maybe this is the cause of they run serially), I don't remember if some day I run this over a fragmented table.. This kind of creation, don't run fews copies of the function in parallel with PSORT_NPROCS, or with PDQ (=100 max_pdq = 100). So, if I run this over a medium table , take too long because run serially and the overhead of the function (SPL) just make the situation worst... running on twice of the regular time.. in some cases... Cesar --- Em sex, 3/9/10, Celso Cabral Coimbra <ccoimbra@cleartech.com.br> escreveu: De: Celso Cabral Coimbra <ccoimbra@cleartech.com.br> Assunto: RES: functional index - create with parallel sort [21169] Para: ids@iiug.org Data: Sexta-feira, 3 de Setembro de 2010, 14:46 Cesar, Are you using PDQ in which level? How are the Parallel Database Query (PDQ) Configuration Parameters in your instance? You should use PSORT_NPROCS and PDQ >1, actually. In my environment to create indexes in parallel I usually use PDQ Priority High, but the MAX_PDQPRIORITY is set to 25, if you would like to use all the resources configured in PDQ, set MAX_PDQPRIORITY to 100 and use set pdqpriority high (or 100). If I really understood your problem, I think it can help you. Celso Cabral Coimbra Administrador de Banco de Dados ClearTech Ltda "Trust at the heart of Communications" Tel. (11) 3576-4509 -----Mensagem original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de Cesar Inacio Martins Enviada em: quinta-feira, 2 de setembro de 2010 22:51 Para: ids@iiug.org Assunto: functional index - create with parallel sort [21160] Hi, IDS 11.50 FC7 If a table have a functional Index, and the table don't have fragmentation, but the PSORT_NPROCS is set with value > 1 (or PDQ >1), the index is created by only one and single thread. Why this creation don't run with more sort threads in parallel? Once the functions are "NOT VARIANT" , I don't see why not... There is some reason , what I don't see ? Cesar ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Cesar Inacio

Cesar Inacio — Sun, 05 Sep 2010 13:14:12 — source: IIUG Forums & Mailing Lists

sorry.. just a correction.. ins't Growth Edition.. is Enterprise Edition.. --- Em sex, 3/9/10, Cesar Inacio Martins <cesar_inacio_martins@yahoo.com.br> escreveu: De: Cesar Inacio Martins <cesar_inacio_martins@yahoo.com.br> Assunto: Re: RES: functional index - create with parall.... [21173] Para: ids@iiug.org Data: Sexta-feira, 3 de Setembro de 2010, 21:51 Hi Celso, In the last case what I get this situation is over a Growth Edition , with PDQ and PSORT_NPROCS set.. But , when I says functional indexes, I means indexes based on functions, something like: create index xyz on tablexyz ( calc_day_payment(day_expires)). And this over a non-fragmented table.. (maybe this is the cause of they run serially), I don't remember if some day I run this over a fragmented table.. This kind of creation, don't run fews copies of the function in parallel with PSORT_NPROCS, or with PDQ (=100 max_pdq = 100). So, if I run this over a medium table , take too long because run serially and the overhead of the function (SPL) just make the situation worst... running on twice of the regular time.. in some cases... Cesar --- Em sex, 3/9/10, Celso Cabral Coimbra <ccoimbra@cleartech.com.br> escreveu: De: Celso Cabral Coimbra <ccoimbra@cleartech.com.br> Assunto: RES: functional index - create with parallel sort [21169] Para: ids@iiug.org Data: Sexta-feira, 3 de Setembro de 2010, 14:46 Cesar, Are you using PDQ in which level? How are the Parallel Database Query (PDQ) Configuration Parameters in your instance? You should use PSORT_NPROCS and PDQ >1, actually. In my environment to create indexes in parallel I usually use PDQ Priority High, but the MAX_PDQPRIORITY is set to 25, if you would like to use all the resources configured in PDQ, set MAX_PDQPRIORITY to 100 and use set pdqpriority high (or 100). If I really understood your problem, I think it can help you. Celso Cabral Coimbra Administrador de Banco de Dados ClearTech Ltda "Trust at the heart of Communications" Tel. (11) 3576-4509 -----Mensagem original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de Cesar Inacio Martins Enviada em: quinta-feira, 2 de setembro de 2010 22:51 Para: ids@iiug.org Assunto: functional index - create with parallel sort [21160] Hi, IDS 11.50 FC7 If a table have a functional Index, and the table don't have fragmentation, but the PSORT_NPROCS is set with value > 1 (or PDQ >1), the index is created by only one and single thread. Why this creation don't run with more sort threads in parallel? Once the functions are "NOT VARIANT" , I don't see why not... There is some reason , what I don't see ? Cesar ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Cesar Inacio

rroussey@comcast.net — Tue, 07 Sep 2010 02:29:03 — source: IIUG Forums & Mailing Lists

I have never seen IDS use PDQ for index builds when the index is functional; it always processes the rows sequentially. I do not know the official Informix answer but I have never seen it work. (or I didn't do it correctly!) Just my observation. May have something to do with recursion or re-entrant code under the covers when the SPL is compiled. Comment from Art? JMiller? Bob ----- Original Message ----- From: "Cesar Inacio Martins" <cesar_inacio_martins@yahoo.com.br> To: ids@iiug.org Sent: Sunday, September 5, 2010 9:14:13 AM Subject: Re: RES: functional index - create with parall.... [21175] sorry.. just a correction.. ins't Growth Edition.. is Enterprise Edition.. --- Em sex, 3/9/10, Cesar Inacio Martins <cesar_inacio_martins@yahoo.com.br> escreveu: De: Cesar Inacio Martins <cesar_inacio_martins@yahoo.com.br> Assunto: Re: RES: functional index - create with parall.... [21173] Para: ids@iiug.org Data: Sexta-feira, 3 de Setembro de 2010, 21:51 Hi Celso, In the last case what I get this situation is over a Growth Edition , with PDQ and PSORT_NPROCS set.. But , when I says functional indexes, I means indexes based on functions, something like: create index xyz on tablexyz ( calc_day_payment(day_expires)). And this over a non-fragmented table.. (maybe this is the cause of they run serially), I don't remember if some day I run this over a fragmented table.. This kind of creation, don't run fews copies of the function in parallel with PSORT_NPROCS, or with PDQ (=100 max_pdq = 100). So, if I run this over a medium table , take too long because run serially and the overhead of the function (SPL) just make the situation worst... running on twice of the regular time.. in some cases... Cesar --- Em sex, 3/9/10, Celso Cabral Coimbra <ccoimbra@cleartech.com.br> escreveu: De: Celso Cabral Coimbra <ccoimbra@cleartech.com.br> Assunto: RES: functional index - create with parallel sort [21169] Para: ids@iiug.org Data: Sexta-feira, 3 de Setembro de 2010, 14:46 Cesar, Are you using PDQ in which level? How are the Parallel Database Query (PDQ) Configuration Parameters in your instance? You should use PSORT_NPROCS and PDQ >1, actually. In my environment to create indexes in parallel I usually use PDQ Priority High, but the MAX_PDQPRIORITY is set to 25, if you would like to use all the resources configured in PDQ, set MAX_PDQPRIORITY to 100 and use set pdqpriority high (or 100). If I really understood your problem, I think it can help you. Celso Cabral Coimbra Administrador de Banco de Dados ClearTech Ltda "Trust at the heart of Communications" Tel. (11) 3576-4509 -----Mensagem original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de Cesar Inacio Martins Enviada em: quinta-feira, 2 de setembro de 2010 22:51 Para: ids@iiug.org Assunto: functional index - create with parallel sort [21160] Hi, IDS 11.50 FC7 If a table have a functional Index, and the table don't have fragmentation, but the PSORT_NPROCS is set with value > 1 (or PDQ >1), the index is created by only one and single thread. Why this creation don't run with more sort threads in parallel? Once the functions are "NOT VARIANT" , I don't see why not... There is some reason , what I don't see ? Cesar ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Glossary

fragmentation