Hello,

Voilà c'est très simple

Disons que j'ai 3 tables:

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
 
ACTOR (+- 1800000 rows)
FILES (+- 1700000 rows)
FILE_CONTRACT (+-500000 rows)
la requête paginée suivante:
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
 
select * 
  from 
( select rownum rnum, a.*
    from (SELECT contract.INTEGRATED_FILE_ID
          FROM FILE_CONTRACT file_contract,
               FILES contract,
               ACTOR declarant
          WHERE file_contract.CONTRACTOR_ID=declarant.ID
          AND file_contract.FILE_ID=contract.ID
          AND declarant.ACTOR_TYPE_CODE ='contractor'
          AND declarant.BCE_NUM=999999724
          ORDER BY contract.INTEGRATED_FILE_ID desc
         ) a
   where rownum < 25 
)
where rnum >= 10;
qui contient donc un order by (pour la pagination).

les indexes sur les champs utilisés dans cette requête sont:
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
 
ACTOR(BCE_NUM)
FILES(ID,INTEGRATED_FILE_ID)
FILE_CONTRACT(CONTRACTOR_ID,FILE_ID)
l'explain plan:
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
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation                         |  Name                           | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                                 |     1 |    26 |    10 |
|*  1 |  VIEW                             |                                 |     1 |    26 |    10 |
|*  2 |   COUNT STOPKEY                   |                                 |       |       |       |
|   3 |    VIEW                           |                                 |     1 |    13 |    10 |
|*  4 |     SORT ORDER BY STOPKEY         |                                 |     1 |    40 |    10 |
|   5 |      NESTED LOOPS                 |                                 |     1 |    40 |     8 |
|   6 |       NESTED LOOPS                |                                 |     4 |   116 |     4 |
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
|*  7 |        TABLE ACCESS BY INDEX ROWID| ACTOR                           |     1 |    18 |     2 |
|*  8 |         INDEX RANGE SCAN          | ACTOR_M2_BCE_NUM                |     2 |       |     1 |
|*  9 |        INDEX RANGE SCAN           | FILE_CONTRACT_M1_CONTRACTOR_ID  |     1 |    11 |     2 |
|* 10 |       INDEX RANGE SCAN            | FILES_M3_INT_FILE_ID            |  1635K|    17M|     1 |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("from$_subquery$_001"."RNUM">=10)
   2 - filter(ROWNUM<25)
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
   4 - filter(ROWNUM<25)
   7 - filter("DECLARANT"."ACTOR_TYPE_CODE"='contractor')
   8 - access("DECLARANT"."BCE_NUM"=416451583)
   9 - access("FILE_CONTRACT"."CONTRACTOR_ID"="DECLARANT"."ID")
  10 - access(SYS_OP_DESCEND("FILE_CONTRACT"."FILE_ID")=SYS_OP_DESCEND("CONTRACT"."ID"))
       filter("FILE_CONTRACT"."FILE_ID"="CONTRACT"."ID")
Le problème c'est que c'est quand même assez couteux... Ca tombe même en time out (30sec!) quand la db est chargée ;(

Et ça à l'air de venir de la clause order by. (Normal j'imagine que ça prenne du temps mais si quelqu'un sait comment optimiser ça, en jouant sur les indexes ou autre chose...)

Voici, pour info les traces, on voit clairement la différence avec la clause order by ou sans:

Avec:
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
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=26)
   1    0   VIEW (Cost=10 Card=1 Bytes=26)
   2    1     COUNT (STOPKEY)
   3    2       VIEW (Cost=10 Card=1 Bytes=13)
   4    3         SORT (ORDER BY STOPKEY) (Cost=10 Card=1 Bytes=40)
   5    4           NESTED LOOPS (Cost=8 Card=1 Bytes=40)
   6    5             NESTED LOOPS (Cost=4 Card=4 Bytes=116)
   7    6               TABLE ACCESS (BY INDEX ROWID) OF 'ACTOR' (Cost
          =2 Card=1 Bytes=18)
 
   8    7                 INDEX (RANGE SCAN) OF 'ACTOR_M2_BCE_NUM' (NO
          N-UNIQUE) (Cost=1 Card=2)
 
   9    6               INDEX (RANGE SCAN) OF 'FILE_CONTRACT_M1_CONTRA
          CTOR_ID' (NON-UNIQUE) (Cost=2 Card=1 Bytes=11)
 
  10    5             INDEX (RANGE SCAN) OF 'FILES_M3_INT_FILE_ID' (NO
          N-UNIQUE) (Cost=1 Card=1635731 Bytes=17993041)
 
 
 
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       5970  consistent gets
       1630  physical reads
          0  redo size
        669  bytes sent via SQL*Net to client
        368  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         15  rows processed
et sans:
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
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=26)
   1    0   VIEW (Cost=8 Card=1 Bytes=26)
   2    1     COUNT (STOPKEY)
   3    2       NESTED LOOPS (Cost=8 Card=1 Bytes=40)
   4    3         NESTED LOOPS (Cost=4 Card=4 Bytes=116)
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'ACTOR' (Cost=2 C
          ard=1 Bytes=18)
 
   6    5             INDEX (RANGE SCAN) OF 'ACTOR_M2_BCE_NUM' (NON-UN
          IQUE) (Cost=1 Card=2)
 
   7    4           INDEX (RANGE SCAN) OF 'FILE_CONTRACT_M1_CONTRACTOR
          _ID' (NON-UNIQUE) (Cost=2 Card=1 Bytes=11)
 
   8    3         INDEX (RANGE SCAN) OF 'FILES_M3_INT_FILE_ID' (NON-UN
          IQUE) (Cost=1 Card=1635731 Bytes=17993041)
 
 
 
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         59  consistent gets
          0  physical reads
          0  redo size
        640  bytes sent via SQL*Net to client
        368  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         15  rows processed

Voilà j'ai l'impression que ce sont les physical reads qui plombent tout mais je ne sais pas trop d'ou ça vient... et même s'il y a moyen d'optimiser ça...

Merci pour ceux qui ont lu jusqu'au bout