Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production SQL> SELECT SUBSTR (segment_name, 1, 30) segment_name, BYTES / 1024 / 1024 "Size in MB" 2 FROM user_segments 3 WHERE segment_name IN ('FFMAPREP', 'FFMAPREP_SALEDATE'); SEGMENT_NAME Size in MB ------------------------------ ---------- FFMAPREP 14975 FFMAPREP_SALEDATE 2688 SQL> SELECT index_name, clustering_factor 2 FROM user_indexes 3 WHERE index_name = 'FFMAPREP_SALEDATE'; INDEX_NAME CLUSTERING_FACTOR ------------------------------ ----------------- FFMAPREP_SALEDATE 141795296 SQL> set autot trace SQL> SELECT * FROM supermag.ffmaprep 2 WHERE saledate = TO_DATE ('01042006', 'DDMMYYYY'); 287721 строк выбрано. План выполнения ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=23741 Card=316290 By tes=42066570) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'FFMAPREP' (Cost=23741 Ca rd=316290 Bytes=42066570) 2 1 INDEX (RANGE SCAN) OF 'FFMAPREP_SALEDATE' (NON-UNIQUE) ( Cost=289 Card=316290) Статистика ---------------------------------------------------------- 0 recursive calls 0 db block gets 256246 consistent gets 233102 physical reads 60 redo size 27493775 bytes sent via SQL*Net to client 211483 bytes received via SQL*Net from client 19183 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 287721 rows processed SQL> SELECT * FROM supermag.ffmaprep 2 WHERE saledate BETWEEN TO_DATE ('01042006', 'DDMMYYYY') AND TO_DATE ('03042006', 'DDMMYYYY'); 849579 строк выбрано. План выполнения ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37008 Card=1266218 B ytes=168406994) 1 0 TABLE ACCESS (FULL) OF 'FFMAPREP' (Cost=37008 Card=1266218 Bytes=168406994) Статистика ---------------------------------------------------------- 1301 recursive calls 0 db block gets 1013794 consistent gets 915871 physical reads 60 redo size 74865810 bytes sent via SQL*Net to client 623510 bytes received via SQL*Net from client 56640 SQL*Net roundtrips to/from client 8 sorts (memory) 0 sorts (disk) 849579 rows processed SQL> SELECT SUBSTR (segment_name, 1, 30) segment_name, BYTES / 1024 / 1024 "Size in MB" 2 FROM user_segments WHERE segment_name IN ('FFMAPREP', 'FFMAPREP_SALEDATE'); SEGMENT_NAME Size in MB ------------------------------ ---------- FFMAPREP 14975 FFMAPREP_SALEDATE 544 SQL> SELECT index_name, clustering_factor 2 FROM user_indexes WHERE index_name = 'FFMAPREP_SALEDATE'; INDEX_NAME CLUSTERING_FACTOR ------------------------------ ----------------- FFMAPREP_SALEDATE 68241 DROP INDEX SUPERMAG.FFMAPREP_SALEDATE; CREATE BITMAP INDEX SUPERMAG.FFMAPREP_SALEDATE ON SUPERMAG.FFMAPREP(SALEDATE) NOLOGGING; SQL> set autot trace SQL> SELECT * FROM supermag.ffmaprep 2 WHERE saledate = TO_DATE ('01042006', 'DDMMYYYY'); 287721 строк выбрано. План выполнения ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6216 Card=316290 Byt es=42066570) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'FFMAPREP' (Cost=6216 Car d=316290 Bytes=42066570) 2 1 BITMAP CONVERSION (TO ROWIDS) 3 2 BITMAP INDEX (SINGLE VALUE) OF 'FFMAPREP_SALEDATE' Статистика ---------------------------------------------------------- 324 recursive calls 0 db block gets 237042 consistent gets 125460 physical reads 60 redo size 27493775 bytes sent via SQL*Net to client 211482 bytes received via SQL*Net from client 19183 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 287721 rows processed SQL> SELECT * FROM supermag.ffmaprep 2 WHERE saledate BETWEEN TO_DATE ('01042006', 'DDMMYYYY') AND TO_DATE ('03042006', 'DDMMYYYY'); 849579 строк выбрано. План выполнения ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16626 Card=1266218 B ytes=168406994) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'FFMAPREP' (Cost=16626 Ca rd=1266218 Bytes=168406994) 2 1 BITMAP CONVERSION (TO ROWIDS) 3 2 BITMAP INDEX (RANGE SCAN) OF 'FFMAPREP_SALEDATE' Статистика ---------------------------------------------------------- 0 recursive calls 0 db block gets 692106 consistent gets 603610 physical reads 60 redo size 81877538 bytes sent via SQL*Net to client 623509 bytes received via SQL*Net from client 56640 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 849579 rows processed