Insertion dynamique dans nested table

Version imprimable

Bonjour,

j'ai créé une table de cette manière:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE or replace TYPE BANNER_LIST AS TABLE OF NUMBER  
/
 
CREATE TABLE ADEXPR03.WEB_PLAN_MEDIA_MONTH_FRED
(
    ID_MEDIA                    NUMBER(10,0)   NOT NULL,
    ID_PRODUCT                  NUMBER(10,0)   NOT NULL,
    MONTH_MEDIA_NUM             NUMBER(6,0)    NOT NULL,
    TOTALUNITE                  NUMBER(10,0)       NULL,
    LIST_BANNERS                BANNER_LIST     
)
NESTED TABLE LIST_BANNERS STORE AS WEB_PLAN_MEDIA_MONTH_TAB
TABLESPACE DATAWEB01ADEXPR03
NOLOGGING
PCTFREE 10
PCTUSED 0
INITRANS 1
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT)
NOPARALLEL
CACHE
NOROWDEPENDENCIES
/

j'aurai quelques millions de lignes chaque jour à insérer du genre:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
insert into ADEXPR03.WEB_PLAN_MEDIA_MONTH
SELECT
DP.Id_media,
DP.Id_product,
SUBSTR(DP.Date_media_num,1,6) as M,
SUM(INSERTION) as totalinsert,
Wm_Concat(DISTINCT HASHCODE)
FROM ADEXPR03.DATA_EVALIANT DP
WHERE DP.DATE_MEDIA_NUM >= 20160101   and DP.DATE_MEDIA_NUM < 20160401
and id_media=11366
and id_product in (840239,838503,837806,835583,835570)
GROUP BY DP.Id_Media, DP.Id_product, SUBSTR(DP.Date_media_num,1,6)
order by 3,2

ID_MEDIA	ID_PRODUCT	M	TOTALINSERT	WM_CONCAT(DISTINCTHASHCODE)
11366	835570	        201601	9	        37147179
11366	835583	        201601	7	       37152794,37209502,37212347,37498709,37498831
11366	837806	        201601	1	       37085353
11366	838503	        201601	3	       36807422,36807430,36808400
11366	840239	        201601	3	       37152000,37152758
11366	837806	        201602	1	       36940535
11366	838503	        201602	6	       36807422,36808400
11366	840239	        201602	1	       37152000
11366	840239	        201603	1	       37415174

actuellement, la colonne LIST_BANNERS est juste un VARCHAR2, mais le wm_concat prend 75% du temps à l'insertion.

je voulais savoir si il était possible d'insérer dynamiquement en colonne les valeurs retournées par le wm_concat dans la table proposée plus haut.
car je peux facilement insérer de cette manière :

Code:

1
2
3
INSERT INTO ADEXPR03.WEB_PLAN_MEDIA_MONTH_FRED VALUES (11366,835570,201601,9,BANNER_LIST(37147179));
INSERT INTO ADEXPR03.WEB_PLAN_MEDIA_MONTH_FRED VALUES (11366,835583,201601,7,BANNER_LIST(37152794,37209502,37212347,37498709,37498831));
...

mais je voudrais que ce soit fait dans la requête d'insertion:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
insert into ADEXPR03.WEB_PLAN_MEDIA_MONTH_FRED
SELECT
DP.Id_media,
DP.Id_product,
SUBSTR(DP.Date_media_num,1,6) as M,
SUM(INSERTION) as totalinsert,
XXXXX(distinct HASHCODE)
FROM ADEXPR03.DATA_EVALIANT DP
WHERE DP.DATE_MEDIA_NUM >= 20160101   and DP.DATE_MEDIA_NUM < 20160401
and id_media=11366
and id_product in (840239,838503,837806,835583,835570)
GROUP BY DP.Id_Media, DP.Id_product, SUBSTR(DP.Date_media_num,1,6)
order by 3,2

je ne vois pas comment faire cette insertion dans une nested table...

Merci pour votre aide

Bonjour,

Je vois que personne n'a pour le moment répondu, donc je me permets de proposer humblement quelquechose, en espérant que cela puisse
te mettre sur une piste.
J'ai tenté un exemple, en espérant que tu pourras adapter a ton cas:

Code:

1
2
3
4
5
6
7
8
 
CREATE OR REPLACE TYPE tab_tst_nested AS TABLE OF VARCHAR2 (50 CHAR);
 
CREATE TABLE TST_NESTED
(
  ROW_IDENTIFIER    INTEGER,
  COLLECTION_VALUE  TAB_TST_NESTED
);

puis

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
INSERT INTO TST_NESTED
   WITH base_data
           AS (SELECT 1 row_identifier, 'abc' for_collection FROM DUAL
               UNION ALL
               SELECT 1 row_identifier, 'def' for_collection FROM DUAL
               UNION ALL
               SELECT 1 row_identifier, 'ghi' for_collection FROM DUAL
               UNION ALL
               SELECT 1 row_identifier, 'jkl' for_collection FROM DUAL
               UNION ALL
               SELECT 2 row_identifier, 'abc2' for_collection FROM DUAL)
     SELECT MAX (a.row_identifier) row_identifier
          , CAST (MULTISET (SELECT for_collection
                              FROM base_data
                             WHERE row_identifier = a.row_identifier) AS tab_tst_nested)
               for_collection
       FROM base_data a
      WHERE a.row_identifier = 1
   GROUP BY row_identifier;

Ce qui veut dire que dans ton cas, il te faudra très certainement réorganiser ton code, et avoir à la place de

Code:

1
2
 
XXXXX(distinct HASHCODE)

quelque chose comme

Code:

1
2
3
 
CAST (MULTISET ( SELECT HASHCODE FROM ADEXPR03.DATA_EVALIANT WHERE --..., il te faut filtrer a nouveau
            ) AS BANNER_LIST) HASHCODE

Ce n'est peut être pas la meilleure méthode.
De mon côté, je n'ai jamais eu a passer par la, puisqu'a chaque fois que je devais insérer dans une Nested Table, c'était dans un
contexte de PL/SQL.

En espérant t'aider malgré tout,
@+
;)

24/04/2017, 14h44
mnitu

Utilisez Collect mais vous avez quelques problèmes de compréhension des "nested tables" si vous utilisez wm_concat.
24/04/2017, 16h50
jio77

Citation:

Envoyé par mnitu

Utilisez Collect mais vous avez quelques problèmes de compréhension des "nested tables" si vous utilisez wm_concat.

je voudrais utiliser les nested tables au lieu de wm_concat...
et en effet, les "collect" ne sont pas mon fort, d'où la demande d'aide...
Merci.

Citation:

Envoyé par Sunchaser

Code:

1
2
3
4
5
6
7
8
 
CREATE OR REPLACE TYPE tab_tst_nested AS TABLE OF VARCHAR2 (50 CHAR);
 
CREATE TABLE TST_NESTED
(
  ROW_IDENTIFIER    INTEGER,
  COLLECTION_VALUE  TAB_TST_NESTED
);

puis

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
INSERT INTO TST_NESTED
   WITH base_data
           AS (SELECT 1 row_identifier, 'abc' for_collection FROM DUAL
               UNION ALL
               SELECT 1 row_identifier, 'def' for_collection FROM DUAL
               UNION ALL
               SELECT 1 row_identifier, 'ghi' for_collection FROM DUAL
               UNION ALL
               SELECT 1 row_identifier, 'jkl' for_collection FROM DUAL
               UNION ALL
               SELECT 2 row_identifier, 'abc2' for_collection FROM DUAL)
     SELECT MAX (a.row_identifier) row_identifier
          , CAST (MULTISET (SELECT for_collection
                              FROM base_data
                             WHERE row_identifier = a.row_identifier) AS tab_tst_nested)
               for_collection
       FROM base_data a
      WHERE a.row_identifier = 1
   GROUP BY row_identifier;

Ce qui veut dire que dans ton cas, il te faudra très certainement réorganiser ton code, et avoir à la place de

Code:

1
2
 
XXXXX(distinct HASHCODE)

quelque chose comme

Code:

1
2
3
 
CAST (MULTISET ( SELECT HASHCODE FROM ADEXPR03.DATA_EVALIANT WHERE --..., il te faut filtrer a nouveau
            ) AS BANNER_LIST) HASHCODE

Très encourageant, je continue sur cette voie.

Merci...

Citation:

Envoyé par mnitu

Utilisez Collect mais vous avez quelques problèmes de compréhension des "nested tables" si vous utilisez wm_concat.

Et oui, il a bien raison le Grand Maître mnitu ;)
Je devrais donc modifier mon code comme ceci par exemple:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
INSERT INTO TST_NESTED
WITH base_data
        AS (SELECT 1 row_identifier, 'abc' for_collection FROM DUAL
            UNION ALL
            SELECT 1 row_identifier, 'def' for_collection FROM DUAL
            UNION ALL
            SELECT 1 row_identifier, 'ghi' for_collection FROM DUAL
            UNION ALL
            SELECT 1 row_identifier, 'jkl' for_collection FROM DUAL
            UNION ALL
            SELECT 2 row_identifier, 'abc2' for_collection FROM DUAL)
  SELECT a.row_identifier, CAST (COLLECT (a.for_collection) AS tab_tst_nested) for_collection
    FROM base_data a
   WHERE a.row_identifier = 1
GROUP BY row_identifier;

Citation:

Envoyé par jio77

Très encourageant, je continue sur cette voie.

Avec le COLLECT donc, c'est mieux.

24/04/2017, 19h10
jio77

je teste tout ça demain, merci pour vos réponse et je reviens vers vous.
bonne soirée.
25/04/2017, 09h03
jio77

Merci à tous pour votre aide...
bonne journée.
27/04/2017, 10h03
jio77

Citation:

Envoyé par jio77

Merci à tous pour votre aide...
bonne journée.

j'aurai d'autres questions:

toujours avec le TYPE BANNER_LIST AS TABLE OF NUMBER

si j'ai ces valeurs:
ID_MEDIA ID_PRODUCT MONTH_MEDIA_NUM LIST_BANNERS
10000 629805 201501 BANNER_LIST(34406592, 34567375)
10000 629805 201501 BANNER_LIST(34406592, 34567375)
10000 629805 201502 BANNER_LIST(34406592)
10000 629805 201503 BANNER_LIST(34406592)
10000 629805 201503 BANNER_LIST(34406592)
10000 629805 201504 BANNER_LIST(34406592, 34567379)
10000 629805 201505 BANNER_LIST(34406592, 34567375, 34567379)

- comment faire en pl/sql pour ne retrouver que les lignes où il n'y a que les valeurs (34406592, 34567379)
- comment faire un distinct en pl/sql pour éliminer les doublons

Merci, je débute avec les nested, et votre aide est précieuse.
28/04/2017, 13h54
mnitu

Citation:

Envoyé par jio77

...
- comment faire en pl/sql pour ne retrouver que les lignes où il n'y a que les valeurs (34406592, 34567379)
- comment faire un distinct en pl/sql pour éliminer les doublons

On compare une nested table avec une autre
On utilise l'opérateur SET