Optimiseur et sélection des partitions

**Waldar** · 05/03/2015, 17h50

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.

**Waldar** · 05/03/2015, 18h10

Afin d'être complet, j'ai quand même une solution viable si je ne regarde que quelques jours et qui nécessite la création d'un index :

Code :

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

1
2
3
4
create unique index ix_matable
on matable (snap_date, cust_id)
compress 1
local;

Auquel cas je peux utiliser cette troisième vue :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
create or replace view mavue3 (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 not exists (select null
                                from matable t2
                               where t2.cust_id   = t1.cust_id
                                 and t2.snap_date = t1.snap_date - 1)
             then 1
             else 0
           end
      from matable t1;

Où j'ai un plan d'exécution correct :

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
explain plan for
select pk_id, snap_date, cust_id, cust_value, cust_insc, is_back
  from mavue3
 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 |   498K|    60  (20)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|            |     1 |    22 |     0   (0)| 00:00:01 |   KEY |   KEY |
|*  2 |   INDEX UNIQUE SCAN    | IX_MATABLE |     1 |    22 |     0   (0)| 00:00:01 |   KEY |   KEY |
|   3 |  PARTITION RANGE SINGLE|            | 10000 |   498K|    60  (20)| 00:00:01 |     4 |     4 |
|*  4 |   TABLE ACCESS FULL    | MATABLE    | 10000 |   498K|    60  (20)| 00:00:01 |     4 |     4 |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):                                                  
---------------------------------------------------                                                  
 
   2 - access("T2"."CUST_ID"=:B1 AND "T2"."SNAP_DATE"=:B2-1)                                         
   4 - filter("T1"."SNAP_DATE"=TO_DATE(' 2015-03-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

C'est amusant d'ailleurs on ne voit pas le NESTED LOOP induit par la sous-requête scalaire.

Néanmoins l'introduction d'un index n'étant pas anodin, si je pouvais m'en passer j'aimerai autant.
De surcroît, si je cherche des données sur trois mois par exemple, ça fait beaucoup d'appels à l'index et ce n'est plus vraiment performant.

**Waldar** · 18/03/2015, 11h47

Finalement, j'ai rajouté une colonne à ma table de départ et je mets à jour l'indicateur au moment du chargement quotidien, ce qui est finalement peu coûteux car je pilote facilement les partitions :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
alter table matable
  add is_back number(1) default 0 not null;
-- table MATABLE modifié(e).
 
update matable t1
   set t1.is_back    = 1
 where t1.is_back    = 0
   and t1.snap_date  = trunc(sysdate)
   and t1.snap_date <> t1.cust_insc
   and not exists (select null
                     from matable t2
                    where t2.cust_id   = t1.cust_id
                      and t2.snap_date = trunc(sysdate) - 1);

**mnitu** · 18/03/2015, 14h20

Je n'ai pas pu vraiment jouer avec vote exemple sur une base 11g comme je me suis proposé au départ. Mais un petit test sur une base 12c semblait indiquer que l'optimiseur devrait se mieux débrouiller dans cette version.