Sous l’utilisateur system…
SQL> alter system set sga_target=150M
SQL> select * from v$version
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
Sous l’utilisateur scott…
SQL> CREATE TABLE TT
(ID NUMBER PRIMARY KEY,
LIB VARCHAR2(50),
N1 NUMBER,
N2 NUMBER,
N3 NUMBER,
N4 NUMBER,
N5 NUMBER,
N6 NUMBER,
N7 NUMBER,
N8 NUMBER,
N9 NUMBER,
N10 NUMBER,
N11 NUMBER,
N12 NUMBER,
N13 NUMBER,
N14 NUMBER,
N15 NUMBER,
N16 NUMBER,
N17 NUMBER,
N18 NUMBER,
N19 NUMBER,
N20 NUMBER) PCTFREE 99;
CREATE TABLE succeeded.
SQL> begin
for i in 1..40000 loop
--
-- soit si la taille des blocs est de 8k
-- une table qui fera 40000 x 8k soit 320MB
--
insert into tt values (i, 'Ceci est un test',
mod(i,3), mod(i,3), mod(i,3), mod(i,3), mod(i,3),
mod(i,3), mod(i,3), mod(i,3), mod(i,3), mod(i,3),
mod(i,3), mod(i,3), mod(i,3), mod(i,3), mod(i,3),
mod(i,3), mod(i,3), mod(i,3), mod(i,3), mod(i,3));
end loop;
commit;
end;
/
anonymous block completed
SQL> create index tt_idx on tt(n1, n2, n3, n4, n5, n6, n7, n8, n9, n10, n11, n12, n13, n14, n15, n16, n17, n18, n19, n20);
create index succeeded.
SQL> exec dbms_stats.gather_table_stats('SCOTT', 'TT', cascade=>true)
anonymous block completed
SQL> select segment_name, segment_type, tablespace_name, round(bytes/1000000) MBytes, blocks
from user_segments
where segment_name='TT';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME MBYTES BLOCKS
------------- -------------- ---------------- ------- -------
TT TABLE USERS 336 40960
1 rows selected
SQL> set autotrace on
SQL> set timing on
SQL> set lines 120
SQL> SELECT n1, SUM(id)
2 FROM tt
3 WHERE n1 = 1
4 GROUP BY n1;
N1 SUM(ID)
---------- ----------
1 266686667
Ecoulé : 00 :00 :00.29
Plan d'exécution
----------------------------------------------------------
Plan hash value: 657352684
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 220 (2)| 00:00:03 |
| 1 | SORT GROUP BY NOSORT | | 1 | 8 | 220 (2)| 00:00:03 |
|* 2 | VIEW | index$_join$_001 | 13334 | 104K| 220 (2)| 00:00:03 |
|* 3 | HASH JOIN | | | | | |
|* 4 | INDEX RANGE SCAN | TT_IDX | 13334 | 104K| 211 (2)| 00:00:03 |
| 5 | INDEX FAST FULL SCAN| SYS_C005463 | 13334 | 104K| 95 (2)| 00:00:02 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("N1"=1)
3 - access(ROWID=ROWID)
4 - access("N1"=1)
Statistiques
----------------------------------------------------------
1324 recursive calls
0 db block gets
493 consistent gets
241 physical reads
0 redo size
389 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
27 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT n1, SUM(id)
2 FROM tt
3 WHERE n1 = 1 AND n2 = 1 AND n3 = 1 AND n4 = 1 AND n5 = 1
4 AND n6 = 1 AND n7 = 1 AND n8 = 1 AND n9 = 1 AND n10 = 1
5 AND n11 = 1 AND n12 = 1 AND n13 = 1 AND n14 = 1 AND n15 = 1
6 AND n16 = 1 AND n17 = 1 AND n18 = 1 AND n19 = 1 AND n20 = 1
7 GROUP BY n1;
N1 SUM(ID)
---------- ----------
1 266686667
Ecoulé : 00 :00 :00.74
Plan d'exécution
----------------------------------------------------------
Plan hash value: 2689658785
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 4 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT | | 1 | 65 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TT | 1 | 65 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TT_IDX | 1 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("N1"=1 AND "N2"=1 AND "N3"=1 AND "N4"=1 AND "N5"=1 AND "N6"=1
AND "N7"=1 AND "N8"=1 AND "N9"=1 AND "N10"=1 AND "N11"=1 AND "N12"=1 AND
"N13"=1 AND "N14"=1 AND "N15"=1 AND "N16"=1 AND "N17"=1 AND "N18"=1 AND
"N19"=1 AND "N20"=1)
Statistiques
----------------------------------------------------------
414 recursive calls
0 db block gets
13599 consistent gets
13402 physical reads
0 redo size
389 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
24 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT /*+ dynamic_sampling(4) */ n1, SUM(id)
2 FROM tt
3 WHERE n1 = 1 AND n2 = 1 AND n3 = 1 AND n4 = 1 AND n5 = 1
4 AND n6 = 1 AND n7 = 1 AND n8 = 1 AND n9 = 1 AND n10 = 1
5 AND n11 = 1 AND n12 = 1 AND n13 = 1 AND n14 = 1 AND n15 = 1
6 AND n16 = 1 AND n17 = 1 AND n18 = 1 AND n19 = 1 AND n20 = 1
7 GROUP BY n1;
N1 SUM(ID)
---------- ----------
1 266686667
Ecoulé : 00 :00 :00.34
Plan d'exécution
----------------------------------------------------------
Plan hash value: 657352684
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 246 (2)| 00:00:03 |
| 1 | SORT GROUP BY NOSORT | | 1 | 65 | 246 (2)| 00:00:03 |
|* 2 | VIEW | index$_join$_001 | 16250 | 1031K| 246 (2)| 00:00:03 |
|* 3 | HASH JOIN | | | | | |
|* 4 | INDEX RANGE SCAN | TT_IDX | 16250 | 1031K| 150 (1)| 00:00:02 |
| 5 | INDEX FAST FULL SCAN| SYS_C005463 | 16250 | 1031K| 95 (2)| 00:00:02 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("N1"=1 AND "N2"=1 AND "N3"=1 AND "N4"=1 AND "N5"=1 AND "N6"=1 AND
"N7"=1 AND "N8"=1 AND "N9"=1 AND "N10"=1 AND "N11"=1 AND "N12"=1 AND "N13"=1 AND
"N14"=1 AND "N15"=1 AND "N16"=1 AND "N17"=1 AND "N18"=1 AND "N19"=1 AND "N20"=1)
3 - access(ROWID=ROWID)
4 - access("N1"=1 AND "N2"=1 AND "N3"=1 AND "N4"=1 AND "N5"=1 AND "N6"=1 AND
"N7"=1 AND "N8"=1 AND "N9"=1 AND "N10"=1 AND "N11"=1 AND "N12"=1 AND "N13"=1 AND
"N14"=1 AND "N15"=1 AND "N16"=1 AND "N17"=1 AND "N18"=1 AND "N19"=1 AND "N20"=1)
Note
-----
- dynamic sampling used for this statement
Statistiques
----------------------------------------------------------
225 recursive calls
0 db block gets
339 consistent gets
171 physical reads
0 redo size
389 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
