Discussion :

[Waldar]

Bonjour à tous,


Je rencontre un problème avec un choix que fait l'optimiseur Oracle dans son analyse d'une requête.

J'ai une table - partitionnée par jour - qui contient une photo quotidienne de clients qui satisfont un critère au moment de l'exécution de ladite photo.
Je cherche ensuite à vérifier si ce client fait un retour dans la table en regardant les données de la veille.

Voici mon jeu d'essai :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
create sequence seq_matable;
-- sequence SEQ_MATABLE créé(e).
 
create table matable
( pk_id        number(5)   not null
, snap_date    date        not null
, cust_id      number(5)   not null
, cust_value   varchar2(10)    null
, cust_insc    date        not null
)
partition by range(snap_date)
( partition p20150301 values less than (date '2015-03-02')
, partition p20150302 values less than (date '2015-03-03')
, partition p20150303 values less than (date '2015-03-04')
, partition p20150304 values less than (date '2015-03-05')
);
-- table MATABLE créé(e).
 
create trigger tbi_matable
before insert on matable
for each row
begin
    :new.pk_id := seq_matable.nextval;
end;
/
-- Elément TRIGGER TBI_MATABLE compilé
 
insert into matable (snap_date, cust_id, cust_value, cust_insc)
select date '2015-03-01', 1, 'Val 1.1', date '2015-03-01' from dual union all
select date '2015-03-02', 1, 'Val 1.2', date '2015-03-01' from dual union all
select date '2015-03-03', 1, 'Val 1.3', date '2015-03-01' from dual union all
select date '2015-03-04', 1, 'Val 1.4', date '2015-03-01' from dual union all
select date '2015-03-01', 2, 'Val 2.1', date '2015-03-01' from dual union all
select date '2015-03-02', 2, 'Val 2.2', date '2015-03-01' from dual union all
select date '2015-03-04', 2, 'Val 2.4', date '2015-03-01' from dual union all
select date '2015-03-02', 3, 'Val 3.2', date '2015-03-02' from dual union all
select date '2015-03-01', 4, 'Val 4.1', date '2015-03-01' from dual union all
select date '2015-03-04', 4, 'Val 4.4', date '2015-03-01' from dual;
-- 10 lignes inséré.
 
commit;
-- validé (commit).

J'affecte des statistiques en regard avec ma volumétrie, à savoir 1M de lignes par jour :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
begin
    sys.dbms_stats.set_table_stats(ownname => user, tabname => 'MATABLE', partname => 'P20150301', numrows => 1e6);
    sys.dbms_stats.set_table_stats(ownname => user, tabname => 'MATABLE', partname => 'P20150302', numrows => 1e6);
    sys.dbms_stats.set_table_stats(ownname => user, tabname => 'MATABLE', partname => 'P20150303', numrows => 1e6);
    sys.dbms_stats.set_table_stats(ownname => user, tabname => 'MATABLE', partname => 'P20150304', numrows => 1e6);
end;
/

Je cherche donc à rajouter un indicateur en passant par une vue afin de laisser les métiers autonomes sur leurs analyses et j'ai eu deux idées.

La première utilise une fonction de fenêtrage :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
create or replace view mavue (pk_id, snap_date, cust_id, cust_value, cust_insc, is_back) as
select pk_id, snap_date, cust_id, cust_value, cust_insc
     , case
         when snap_date <> cust_insc -- élimination des nouveaux entrants
          and snap_date = min(snap_date) over(partition by cust_id order by snap_date range between interval '1' day preceding and current row)
         then 1
         else 0
       end
  from matable;

En regardant au global, le résultat correspond bien à mes attentes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
  select pk_id, snap_date, cust_id, cust_value, cust_insc, is_back
    from mavue
order by pk_id asc;
 
     PK_ID SNAP_DATE     CUST_ID CUST_VALUE CUST_INSC     IS_BACK
---------- ---------- ---------- ---------- ---------- ----------
         1 2015-03-01          1 Val 1.1    2015-03-01          0 
         2 2015-03-02          1 Val 1.2    2015-03-01          0 
         3 2015-03-03          1 Val 1.3    2015-03-01          0 
         4 2015-03-04          1 Val 1.4    2015-03-01          0 
         5 2015-03-01          2 Val 2.1    2015-03-01          0 
         6 2015-03-02          2 Val 2.2    2015-03-01          0 
         7 2015-03-04          2 Val 2.4    2015-03-01          1 
         8 2015-03-02          3 Val 3.2    2015-03-02          0 
         9 2015-03-01          4 Val 4.1    2015-03-01          0 
        10 2015-03-04          4 Val 4.4    2015-03-01          1

Malheureusement, en cherchant mes données sur un jour, Oracle comprend avec la fonction de fenêtrage qu'il doit lire dans le passé mais ne vérifie pas la borne, il va parcourir toute la table :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
explain plan for
select pk_id, snap_date, cust_id, cust_value, cust_insc, is_back
  from mavue
 where snap_date = date '2015-03-04';
 
SELECT * FROM TABLE(dbms_xplan.display);
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |  4000K|   205M|       | 20494   (2)| 00:02:14 |       |       |
|*  1 |  VIEW                 | MAVUE   |  4000K|   205M|       | 20494   (2)| 00:02:14 |       |       |
|   2 |   WINDOW SORT         |         |  4000K|   194M|   259M| 20494   (2)| 00:02:14 |       |       |
|   3 |    PARTITION RANGE ALL|         |  4000K|   194M|       |   234  (21)| 00:00:02 |     1 |     4 |
|   4 |     TABLE ACCESS FULL | MATABLE |  4000K|   194M|       |   234  (21)| 00:00:02 |     1 |     4 |
---------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):                                                      
---------------------------------------------------                                                      
 
   1 - filter("SNAP_DATE"=TO_DATE(' 2015-03-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

En réalité seules les partitions du 3 et du 4 mars suffisent pour répondre à la requête.

Ce que j'aurai aimé voir c'est le plan de cette requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
with cte_mavue as
(
select pk_id, snap_date, cust_id, cust_value, cust_insc
     , case
         when snap_date <> cust_insc
          and snap_date = min(snap_date) over(partition by cust_id order by snap_date range between interval '1' day preceding and current row)
         then 1
         else 0
       end as is_back
  from matable
 where snap_date in (date '2015-03-03', date '2015-03-04')
)
select pk_id, snap_date, cust_id, cust_value, cust_insc, is_back
  from cte_mavue
 where snap_date = date '2015-03-04';
 
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |         |   200K|    10M|       |  1133   (4)| 00:00:08 |       |       |
|*  1 |  VIEW                      |         |   200K|    10M|       |  1133   (4)| 00:00:08 |       |       |
|   2 |   WINDOW SORT              |         |   200K|  9960K|    13M|  1133   (4)| 00:00:08 |       |       |
|   3 |    PARTITION RANGE ITERATOR|         |   200K|  9960K|       |   118  (21)| 00:00:01 |     3 |     4 |
|   4 |     TABLE ACCESS FULL      | MATABLE |   200K|  9960K|       |   118  (21)| 00:00:01 |     3 |     4 |
--------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):                                                           
---------------------------------------------------                                                           
 
   1 - filter("SNAP_DATE"=TO_DATE(' 2015-03-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Analyser le tri dans la fonction de fenêtrage je comprends que ce n'est pas trivial.

Je passe alors par une seconde vue qui réalise une auto-jointure à J-1 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
create or replace view mavue2 (pk_id, snap_date, cust_id, cust_value, cust_insc, is_back) as
    select t1.pk_id, t1.snap_date, t1.cust_id, t1.cust_value, t1.cust_insc
         , case
             when t1.snap_date <> t1.cust_insc
              and t2.cust_id is null
             then 1
             else 0
           end
      from matable t1
 left join matable t2  on t2.cust_id   = t1.cust_id
                      and t2.snap_date = t1.snap_date - 1;

Et je repose le même problème :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
explain plan for
select pk_id, snap_date, cust_id, cust_value, cust_insc, is_back
  from mavue2
 where snap_date = date '2015-03-04';
 
SELECT * FROM TABLE(dbms_xplan.display);
 
---------------------------------------------------------------------------------------------------
| Id  | Operation               | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |         | 10000 |   712K|   308  (25)| 00:00:03 |       |       |
|*  1 |  HASH JOIN OUTER        |         | 10000 |   712K|   308  (25)| 00:00:03 |       |       |
|   2 |   PARTITION RANGE SINGLE|         | 10000 |   498K|    61  (22)| 00:00:01 |     4 |     4 |
|*  3 |    TABLE ACCESS FULL    | MATABLE | 10000 |   498K|    61  (22)| 00:00:01 |     4 |     4 |
|   4 |   PARTITION RANGE ALL   |         |  4000K|    83M|   226  (18)| 00:00:02 |     1 |     4 |
|   5 |    TABLE ACCESS FULL    | MATABLE |  4000K|    83M|   226  (18)| 00:00:02 |     1 |     4 |
---------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):                                                
---------------------------------------------------                                                
 
   1 - access("T2"."SNAP_DATE"(+)=INTERNAL_FUNCTION("T1"."SNAP_DATE")-1 AND                        
              "T2"."CUST_ID"(+)="T1"."CUST_ID")                                                    
   3 - filter("T1"."SNAP_DATE"=TO_DATE(' 2015-03-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Le filtre sur la première table réduit bien la partition a attaquer, mais l'auto-jointure va reperdre l'optimiseur qui n'arrive pas à interpréter ici la date - 1.

Ce que j'aurai aimé voir c'est le plan de cette requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
    select t1.pk_id, t1.snap_date, t1.cust_id, t1.cust_value, t1.cust_insc
         , case
             when t1.snap_date <> t1.cust_insc
              and t2.cust_id is null
             then 1
             else 0
           end
      from matable t1
 left join matable t2  on t2.cust_id   = t1.cust_id
                      and t2.snap_date = date '2015-03-03'
     where t1.snap_date = date '2015-03-04';
 
---------------------------------------------------------------------------------------------------
| Id  | Operation               | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |         | 10000 |   712K|   121  (21)| 00:00:01 |       |       |
|*  1 |  HASH JOIN RIGHT OUTER  |         | 10000 |   712K|   121  (21)| 00:00:01 |       |       |
|   2 |   PARTITION RANGE SINGLE|         | 10000 |   214K|    60  (20)| 00:00:01 |     3 |     3 |
|*  3 |    TABLE ACCESS FULL    | MATABLE | 10000 |   214K|    60  (20)| 00:00:01 |     3 |     3 |
|   4 |   PARTITION RANGE SINGLE|         | 10000 |   498K|    61  (22)| 00:00:01 |     4 |     4 |
|*  5 |    TABLE ACCESS FULL    | MATABLE | 10000 |   498K|    61  (22)| 00:00:01 |     4 |     4 |
---------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):                                                
---------------------------------------------------                                                
 
   1 - access("T2"."CUST_ID"(+)="T1"."CUST_ID")                                                    
   3 - filter("T2"."SNAP_DATE"(+)=TO_DATE(' 2015-03-03 00:00:00', 'syyyy-mm-dd                     
              hh24:mi:ss'))                                                                        
   5 - filter("T1"."SNAP_DATE"=TO_DATE(' 2015-03-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Je me demandais s'il ne fallait pas passer par une fonction pipeline afin de redescendre "en dur" mes filtres dans la requête, mais là ça déborde de mes compétences PL/SQL.
Néanmoins si vous avez une idée avec une solution purement SQL, ça me va aussi.

Merci pour votre lecture.