[9i] Eviter un table access full via une vue

Version imprimable

Bonjour voici une vue :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create or replace view FACTHEA_VUE AS 
SELECT 2 AS CODSOC, a.* FROM FACTHEA_1 a
UNION ALL
SELECT 3 AS CODSOC, b.* FROM FACTHEA_2 b
UNION ALL
SELECT 4 AS CODSOC, c.* FROM FACTHEA_3 c
UNION ALL
SELECT 5 AS CODSOC, d.* FROM FACTHEA_4 d
UNION ALL
SELECT 6 AS CODSOC, e.* FROM FACTHEA_5 e
UNION ALL
SELECT 7 AS CODSOC, f.* FROM FACTHEA_6 f
UNION ALL
SELECT 8 AS CODSOC, g.* FROM FACTHEA_7 g
UNION ALL
SELECT 9 AS CODSOC, h.* FROM FACTHEA_8 h

Je voudrais que l'accès suivant ne fasse pas un FULL SCAN sur chaque table de la vue :

Code:

SELECT * FROM FACTHEA_VUE WHERE CODSOC = 2

Avez-vous une idée ?

Merci par avance pour vos réponses,

25/10/2006, 11h27
orafrance

bah pourquoi tu fais une vue pour joindre des ensembles qui sont disjoints ??? :koi:
25/10/2006, 12h46
Débéa

C'est un progiciel !

Je pense qu'il faut passer par une vue materialisée.

Existe-t-il une autre solution ?
25/10/2006, 12h47
orafrance

mouais... mais attention, la MV n'est pas synchrone :?
25/10/2006, 12h49
Débéa

C'est possible d'avoir une fonction PL/SQL qui renvoie un curseur.

Un truc du type :

Select *
from MAPLSQL(moncode) ?
25/10/2006, 12h54
orafrance

oui :arrow: recherche REF CURSOR
25/10/2006, 16h08
Yorglaa

à mon humble avis ça ne changera rien au problème puisqu'il n'y a simplement aucune clause where qui pourrait restreindre les lignes de chaque table...

donc pas de clause where => pas de restriction => on veut toutes les lignes => Full Table Scan...

moi je ne trouve rien de bizarre...
25/10/2006, 16h20
orafrance

la procédure permettrait de n'exécuter que la requête nécessaire en fonction du paramétre mais c'est pas super joli :?

Justement avec les union all, c'est assez bien géré, il ne fait pas de full sur toute les tables. On vois apparaitre le full dans le plan mais il y a un FILTER avant, donc ce full n'est exécuté que lorsque le FILTER est vérifié, donc dans ton cas simplement dans la table FACTHEA_1.

Si tu veux en avoir le coeur net, crée 9 séquences (une par table) et fais ta vue en appelant à chaque morceau du union all une séquence différente. Puis regardes à l'arrivée celle(s) qui a(ont) avancé...
Code:

1 2 3 4 5 6 CREATE OR REPLACE VIEW FACTHEA_VUE_TEST AS SELECT 2 AS CODSOC, SEQ_A.NEXTVAL as SEQ, a.* FROM FACTHEA_1 a UNION ALL SELECT 3 AS CODSOC, SEQ_B.NEXTVAL as SEQ, b.* FROM FACTHEA_2 b UNION ALL ... etc...

J'ai vainement essayé la technique d'appeler la séquence avec 9.2.0.6 ou 10.2.0.1: dans les 2 cas Oracle refuse de créer la vue. Il semble qu'il y a des restrictions pour l'utilisation des séquences (http://download-west.oracle.com/docs...s6a.htm#111114)
Dans quels cas cette technique peut-elle fonctionner pour "tracer" des plans d'exécution ?

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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
 
SQL> 
SQL> drop table f1;
 
Table dropped.
 
SQL> drop table f2;
 
Table dropped.
 
SQL> drop table f3;
 
Table dropped.
 
SQL> drop sequence s1;
 
Sequence dropped.
 
SQL> drop sequence s2;
 
Sequence dropped.
 
SQL> drop sequence s3;
 
Sequence dropped.
 
SQL> 
SQL> whenever sqlerror exit failure;
SQL> 
SQL> select * from v$version;
 
BANNER                                                                          
----------------------------------------------------------------                
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production                      
PL/SQL Release 9.2.0.6.0 - Production                                           
CORE	9.2.0.6.0	Production                                                       
TNS for 32-bit Windows: Version 9.2.0.6.0 - Production                          
NLSRTL Version 9.2.0.6.0 - Production                                           
 
SQL> 
SQL> create sequence s1;
 
Sequence created.
 
SQL> create sequence s2;
 
Sequence created.
 
SQL> create sequence s3;
 
Sequence created.
 
SQL> 
SQL> create table f1(x char, y int);
 
Table created.
 
SQL> insert into f1 select '1', rownum from audit_actions;
 
144 rows created.
 
SQL> create table f2(x char, y int);
 
Table created.
 
SQL> insert into f2 select '2', rownum from audit_actions;
 
144 rows created.
 
SQL> create table f3(x char, y int);
 
Table created.
 
SQL> insert into f3 select '3', rownum from audit_actions;
 
144 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> 
SQL> create or replace view v123 as
  2  select 1 as tn, f1.* from f1
  3  union all
  4  select 2 as tn, f2.* from f2
  5  union all
  6  select 3 as tn, f3.* from f3;
 
View created.
 
SQL> set autotrace traceonly;
SQL> select * from v123 where tn = 1;
 
144 rows selected.
 
 
Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE                                     
   1    0   VIEW OF 'V123'                                                      
   2    1     UNION-ALL                                                         
   3    2       TABLE ACCESS (FULL) OF 'F1'                                     
   4    2       FILTER                                                          
   5    4         TABLE ACCESS (FULL) OF 'F2'                                   
   6    2       FILTER                                                          
   7    6         TABLE ACCESS (FULL) OF 'F3'                                   
 
 
 
 
Statistics
----------------------------------------------------------                      
          7  recursive calls                                                    
          0  db block gets                                                      
         19  consistent gets                                                    
          0  physical reads                                                     
          0  redo size                                                          
       2807  bytes sent via SQL*Net to client                                   
        598  bytes received via SQL*Net from client                             
         11  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
        144  rows processed                                                     
 
SQL> 
SQL> create or replace view vs123 as
  2  select 1 as tn, s1.nextval as seq, f1.* from f1
  3  union all
  4  select 2 as tn, s2.nextval as seq, f2.* from f2
  5  union all
  6  select 3 as tn, s3.nextval as seq, f3.* from f3
  7  ;
select 1 as tn, s1.nextval as seq, f1.* from f1
                   *
ERROR at line 2:
ORA-02287: sequence number not allowed here

25/10/2006, 23h09
remi4444

:oops:
désolé j'ai écrit sans tester, j'ai plusieurs fois utilisé la technique mais à chaque fois en passant par une fonction....

Par contre il me semble qu'on vois bien dés le plan d'exécution on vois qu'il ne fait un FULL que sur la premiere table. Dans ton exemple, il faudrait voir le plan avec :

Code:

SELECT * FROM v123 WHERE tn = '2';