SQL> -- To set autotrace and timing on: SQL> SQL> set autotrace on; SQL> set timing on; SQL> SQL> -- To get sample data (works at ITU until June 2005, may take a while): SQL> SQL> CREATE TABLE myprojects ( 2 first1 CHAR(10), last1 CHAR(10), grade1 INT, 3 first2 CHAR(10), last2 CHAR(10), grade2 INT, 4 first3 CHAR(10), last3 CHAR(10), grade3 INT 5 ); Table created. Elapsed: 00:00:00.08 SQL> SQL> INSERT INTO myprojects (SELECT * FROM pagh.projects); 116641 rows created. Elapsed: 00:00:59.22 Execution Plan ---------------------------------------------------------- 0 INSERT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'PROJECTS' Statistics ---------------------------------------------------------- 1637 recursive calls 7220 db block gets 3374 consistent gets 1285 physical reads 10725088 redo size 851 bytes sent via SQL*Net to client 605 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 116641 rows processed SQL> SQL> ------------------------------------------------------------------- SQL> -- Please remove the table (by writing "DROP TABLE myprojects;") -- SQL> -- when you are done, to free space on the database server. -- SQL> ------------------------------------------------------------------- SQL> SQL> -- Full table scan versus index scan: SQL> -- (notice the execution plan and the number of db block gets) SQL> SQL> SELECT count(*) FROM myprojects WHERE last2='Gearloose' and last3='Rabbit'; COUNT(*) ---------- 11664 Elapsed: 00:00:10.50 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'MYPROJECTS' Statistics ---------------------------------------------------------- 0 recursive calls 5 db block gets 2566 consistent gets 636 physical reads 76920 redo size 370 bytes sent via SQL*Net to client 350 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> SQL> INSERT INTO myprojects values ('Rasmus','Pagh',11,'Toger','Norgaard',10,'Lars','Bengtsson',10); 1 row created. Elapsed: 00:00:00.44 Execution Plan ---------------------------------------------------------- 0 INSERT STATEMENT Optimizer=CHOOSE Statistics ---------------------------------------------------------- 0 recursive calls 1 db block gets 1 consistent gets 0 physical reads 352 redo size 850 bytes sent via SQL*Net to client 647 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed SQL> SQL> CREATE INDEX groupmembers23 on myprojects (last2,last3,first2,first3); Index created. Elapsed: 00:02:10.77 SQL> SQL> INSERT INTO myprojects values ('Rasmus','Pagh',13,'Toger','Norgaard',11,'Lars','Bengtsson',11); 1 row created. Elapsed: 00:00:00.45 Execution Plan ---------------------------------------------------------- 0 INSERT STATEMENT Optimizer=CHOOSE Statistics ---------------------------------------------------------- 0 recursive calls 6 db block gets 1 consistent gets 2 physical reads 884 redo size 856 bytes sent via SQL*Net to client 647 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed SQL> SQL> SELECT count(*) FROM myprojects WHERE last2='Gearloose' and last3='Rabbit'; COUNT(*) ---------- 11664 Elapsed: 00:00:00.48 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 INDEX (RANGE SCAN) OF 'GROUPMEMBERS23' (NON-UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 92 consistent gets 91 physical reads 0 redo size 370 bytes sent via SQL*Net to client 350 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> SQL> SELECT count(*) FROM myprojects WHERE last2='Gearloose'; COUNT(*) ---------- 34992 Elapsed: 00:00:00.52 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 INDEX (RANGE SCAN) OF 'GROUPMEMBERS23' (NON-UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 272 consistent gets 180 physical reads 0 redo size 370 bytes sent via SQL*Net to client 331 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> SQL> SELECT count(*) FROM myprojects WHERE last3='Rabbit'; COUNT(*) ---------- 34992 Elapsed: 00:00:00.66 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'MYPROJECTS' Statistics ---------------------------------------------------------- 0 recursive calls 5 db block gets 1284 consistent gets 530 physical reads 0 redo size 370 bytes sent via SQL*Net to client 328 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> SQL> SQL> -- Buffering SQL> -- (notice the number of db block gets and physical reads) SQL> SQL> SELECT count(*) FROM myprojects WHERE last1='Gearloose' and last2='Rabbit'; COUNT(*) ---------- 5832 Elapsed: 00:00:00.61 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'MYPROJECTS' 3 2 INDEX (RANGE SCAN) OF 'GROUPMEMBERS23' (NON-UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 456 consistent gets 332 physical reads 0 redo size 369 bytes sent via SQL*Net to client 350 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> SQL> SELECT max(grade2) FROM myprojects WHERE last1='Gearloose' and last2='Rabbit'; MAX(GRADE2) ----------- 13 Elapsed: 00:00:00.58 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'MYPROJECTS' 3 2 INDEX (RANGE SCAN) OF 'GROUPMEMBERS23' (NON-UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 456 consistent gets 15 physical reads 0 redo size 371 bytes sent via SQL*Net to client 353 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> SQL> DROP TABLE myprojects; Table dropped. Elapsed: 00:00:00.26 SQL>