[11gR2] Optimiseur et sélection des partitions

Optimiseur et sélection des partitions

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:

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:

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:

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:

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:

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:

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:

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:

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:

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.

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:

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:

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:

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.

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:

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);

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.

C'est vrai que je n'ai pas de 12c sous la main pour essayer.
Même sur APEX c'est de la 11.2.0.4.

Pourriez-vous vérifier la valeur du paramètre "_bloom_pruning_enabled" sur votre base 11g ?

Il est à TRUE.