【oracle 学习笔记 8】索引

SQL> edit Wrote file afiedt.buf 1* SELECT *, ROWID FROM emp SQL> / SELECT *, ROWID FROM emp * ERROR at line 1: ORA-00923: FROM keyword not found where expected SQL> edit Wrote file afiedt.buf 1* SELECT ROWID FROM emp SQL> / ROWID ------------------ AAAL+ZAAEAAAAAdAAA AAAL+ZAAEAAAAAdAAB AAAL+ZAAEAAAAAdAAC AAAL+ZAAEAAAAAdAAD AAAL+ZAAEAAAAAdAAE AAAL+ZAAEAAAAAdAAF AAAL+ZAAEAAAAAdAAG AAAL+ZAAEAAAAAdAAH AAAL+ZAAEAAAAAdAAI AAAL+ZAAEAAAAAdAAJ AAAL+ZAAEAAAAAdAAK ROWID ------------------ AAAL+ZAAEAAAAAdAAL AAAL+ZAAEAAAAAdAAM AAAL+ZAAEAAAAAdAAN 14 rows selected. SQL> edit Wrote file afiedt.buf 1* SELECT ROWID,ename FROM emp SQL> / ROWID ENAME ------------------ ---------- AAAL+ZAAEAAAAAdAAA SMITH AAAL+ZAAEAAAAAdAAB ALLEN AAAL+ZAAEAAAAAdAAC WARD AAAL+ZAAEAAAAAdAAD JONES AAAL+ZAAEAAAAAdAAE MARTIN AAAL+ZAAEAAAAAdAAF BLAKE AAAL+ZAAEAAAAAdAAG CLARK AAAL+ZAAEAAAAAdAAH SCOTT AAAL+ZAAEAAAAAdAAI KING AAAL+ZAAEAAAAAdAAJ TURNER AAAL+ZAAEAAAAAdAAK ADAMS ROWID ENAME ------------------ ---------- AAAL+ZAAEAAAAAdAAL JAMES AAAL+ZAAEAAAAAdAAM FORD AAAL+ZAAEAAAAAdAAN MILLER 14 rows selected. SQL> conn ymc Connected. SQL> edit Wrote file afiedt.buf 1 CREATE TABLE testPri ( 2 id CHARACTER(8) constraint pk_id primary key 3* ) SQL> / CREATE TABLE testPri ( * ERROR at line 1: ORA-00955: name is already used by an existing object SQL> edit Wrote file afiedt.buf 1 -- CREATE TABLE testPri ( 2 -- id CHARACTER(8) constraint pk_id primary key 3 -- ) 4* DROP TABLE testPri SQL> / Table dropped. SQL> edit Wrote file afiedt.buf 1 CREATE TABLE testPri ( 2 id CHARACTER(8) constraint pk_id primary key 3* ) SQL> / Table created. SQL> edit Wrote file afiedt.buf 1 SELECT index_name, index_type, TABLE_name 2* FROM user_indexes WHERE table_name=upper('testPri') SQL> / INDEX_NAME INDEX_TYPE ------------------------------ --------------------------- TABLE_NAME ------------------------------ PK_ID NORMAL TESTPRI SQL> edit Wrote file afiedt.buf SQL> conn school/school Connected. SQL> edit Wrote file afiedt.buf 1 CREATE INDEX sname_index on student(sname) 2* TABLESPACE users SQL> / Index created. SQL> edit Wrote file afiedt.buf 1 CREATE bitmap INDEX sclass_index on student(sclass) 2* TABLESPACE users SQL> / Index created. SQL> set autot traceonly SQL> edit Wrote file afiedt.buf 1 SELECT * FROM student 2* WHERE sname='刘丽' SQL> / Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=37) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'STUDENT' (TABLE) (Cost=2 Card=1 Bytes=37) 2 1 INDEX (RANGE SCAN) OF 'SNAME_INDEX' (INDEX) (Cost=1 Card =1) Statistics ---------------------------------------------------------- 66 recursive calls 0 db block gets 12 consistent gets 0 physical reads 0 redo size 651 bytes sent via SQL*Net to client 512 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> edit Wrote file afiedt.buf 1* ALTER INDEX sname_index monitoring usage SQL> / Index altered. SQL> edit Wrote file afiedt.buf 1 SELECT * FROM v$object_usage 2* WHERE table_name=upper('student') SQL> / Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=125 ) 1 0 NESTED LOOPS (Cost=3 Card=1 Bytes=125) 2 1 NESTED LOOPS (Cost=2 Card=1 Bytes=95) 3 2 NESTED LOOPS (Cost=2 Card=1 Bytes=81) 4 3 TABLE ACCESS (FULL) OF 'OBJECT_USAGE' (TABLE) (Cost= 2 Card=1 Bytes=48) 5 3 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' (TABLE) (Cos t=0 Card=1 Bytes=33) 6 5 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (INDEX (UNIQUE)) ( Cost=0 Card=1) 7 2 TABLE ACCESS (BY INDEX ROWID) OF 'IND$' (CLUSTER) (Cos t=0 Card=1 Bytes=14) 8 7 INDEX (UNIQUE SCAN) OF 'I_IND1' (INDEX (UNIQUE)) (Co st=0 Card=1) 9 1 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' (TABLE) (Cost=1 Card=1 Bytes=30) 10 9 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (INDEX (UNIQUE)) (Cost =0 Card=1) Statistics ---------------------------------------------------------- 264 recursive calls 0 db block gets 84 consistent gets 7 physical reads 0 redo size 744 bytes sent via SQL*Net to client 512 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 12 sorts (memory) 0 sorts (disk) 1 rows processed SQL> edit Wrote file afiedt.buf 1* analyze INDEX sname_index validate structure SQL> / Index analyzed. SQL> edit Wrote file afiedt.buf 1 SELECT br_rows, br_blks,lf_rows,del_lf_rows 2 FROM index_stats 3* WHERE name=upper('sname_index') SQL> / Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=135 Card=3 Bytes=2 07) 1 0 VIEW OF 'INDEX_STATS' (VIEW) (Cost=135 Card=3 Bytes=207) 2 1 UNION-ALL 3 2 NESTED LOOPS (Cost=55 Card=1 Bytes=161) 4 3 NESTED LOOPS (Cost=54 Card=1 Bytes=150) 5 4 HASH JOIN (Cost=53 Card=1 Bytes=134) 6 5 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' (TABLE) (Cost=28 Card=2 Bytes=60) 7 6 INDEX (SKIP SCAN) OF 'I_OBJ2' (INDEX (UNIQUE)) (Cost=26 Card=2) 8 5 FIXED TABLE (FULL) OF 'X$KDXST' (TABLE (FIXED)) (Cost=24 Card=100 Bytes=10400) 9 4 TABLE ACCESS (BY INDEX ROWID) OF 'IND$' (CLUSTER) (Cost=1 Card=1 Bytes=16) 10 9 INDEX (UNIQUE SCAN) OF 'I_IND1' (INDEX (UNIQUE)) (Cost=0 Card=1) 11 3 TABLE ACCESS (CLUSTER) OF 'SEG$' (CLUSTER) (Cost=1 C ard=1 Bytes=11) 12 11 INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (INDEX) (C ost=0 Card=1) 13 2 NESTED LOOPS (Cost=55 Card=1 Bytes=161) 14 13 NESTED LOOPS (Cost=54 Card=1 Bytes=150) 15 14 HASH JOIN (Cost=53 Card=1 Bytes=134) 16 15 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' (TABLE) (Cost=28 Card=2 Bytes=60) 17 16 INDEX (SKIP SCAN) OF 'I_OBJ2' (INDEX (UNIQUE)) (Cost=26 Card=2) 18 15 FIXED TABLE (FULL) OF 'X$KDXST' (TABLE (FIXED)) (Cost=24 Card=100 Bytes=10400) 19 14 TABLE ACCESS (BY INDEX ROWID) OF 'INDPART$' (TABLE ) (Cost=1 Card=1 Bytes=16) 20 19 INDEX (RANGE SCAN) OF 'I_INDPART_OBJ$' (INDEX) ( Cost=0 Card=1) 21 13 TABLE ACCESS (CLUSTER) OF 'SEG$' (CLUSTER) (Cost=1 C ard=1 Bytes=11) 22 21 INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (INDEX) (C ost=0 Card=1) 23 2 NESTED LOOPS (Cost=26 Card=1 Bytes=197) 24 23 NESTED LOOPS (Cost=26 Card=1 Bytes=186) 25 24 MERGE JOIN (CARTESIAN) (Cost=26 Card=1 Bytes=156) 26 25 TABLE ACCESS (FULL) OF 'INDSUBPART$' (TABLE) (Co st=2 Card=1 Bytes=52) 27 25 BUFFER (SORT) (Cost=24 Card=100 Bytes=10400) 28 27 FIXED TABLE (FULL) OF 'X$KDXST' (TABLE (FIXED) ) (Cost=24 Card=100 Bytes=10400) 29 24 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' (TABLE) (C ost=0 Card=1 Bytes=30) 30 29 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (INDEX (UNIQUE)) (Cost=0 Card=1) 31 23 TABLE ACCESS (CLUSTER) OF 'SEG$' (CLUSTER) (Cost=0 C ard=1 Bytes=11) 32 31 INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (INDEX) (C ost=0 Card=1) Statistics ---------------------------------------------------------- 596 recursive calls 0 db block gets 188 consistent gets 5 physical reads 0 redo size 573 bytes sent via SQL*Net to client 512 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 12 sorts (memory) 0 sorts (disk) 1 rows processed SQL> edit Wrote file afiedt.buf 1 SELECT br_rows, br_blks,lf_rows,del_lf_rows 2 FROM index_stats 3* WHERE name=upper('sname_index') SQL> / Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=135 Card=3 Bytes=2 07) 1 0 VIEW OF 'INDEX_STATS' (VIEW) (Cost=135 Card=3 Bytes=207) 2 1 UNION-ALL 3 2 NESTED LOOPS (Cost=55 Card=1 Bytes=161) 4 3 NESTED LOOPS (Cost=54 Card=1 Bytes=150) 5 4 HASH JOIN (Cost=53 Card=1 Bytes=134) 6 5 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' (TABLE) (Cost=28 Card=2 Bytes=60) 7 6 INDEX (SKIP SCAN) OF 'I_OBJ2' (INDEX (UNIQUE)) (Cost=26 Card=2) 8 5 FIXED TABLE (FULL) OF 'X$KDXST' (TABLE (FIXED)) (Cost=24 Card=100 Bytes=10400) 9 4 TABLE ACCESS (BY INDEX ROWID) OF 'IND$' (CLUSTER) (Cost=1 Card=1 Bytes=16) 10 9 INDEX (UNIQUE SCAN) OF 'I_IND1' (INDEX (UNIQUE)) (Cost=0 Card=1) 11 3 TABLE ACCESS (CLUSTER) OF 'SEG$' (CLUSTER) (Cost=1 C ard=1 Bytes=11) 12 11 INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (INDEX) (C ost=0 Card=1) 13 2 NESTED LOOPS (Cost=55 Card=1 Bytes=161) 14 13 NESTED LOOPS (Cost=54 Card=1 Bytes=150) 15 14 HASH JOIN (Cost=53 Card=1 Bytes=134) 16 15 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' (TABLE) (Cost=28 Card=2 Bytes=60) 17 16 INDEX (SKIP SCAN) OF 'I_OBJ2' (INDEX (UNIQUE)) (Cost=26 Card=2) 18 15 FIXED TABLE (FULL) OF 'X$KDXST' (TABLE (FIXED)) (Cost=24 Card=100 Bytes=10400) 19 14 TABLE ACCESS (BY INDEX ROWID) OF 'INDPART$' (TABLE ) (Cost=1 Card=1 Bytes=16) 20 19 INDEX (RANGE SCAN) OF 'I_INDPART_OBJ$' (INDEX) ( Cost=0 Card=1) 21 13 TABLE ACCESS (CLUSTER) OF 'SEG$' (CLUSTER) (Cost=1 C ard=1 Bytes=11) 22 21 INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (INDEX) (C ost=0 Card=1) 23 2 NESTED LOOPS (Cost=26 Card=1 Bytes=197) 24 23 NESTED LOOPS (Cost=26 Card=1 Bytes=186) 25 24 MERGE JOIN (CARTESIAN) (Cost=26 Card=1 Bytes=156) 26 25 TABLE ACCESS (FULL) OF 'INDSUBPART$' (TABLE) (Co st=2 Card=1 Bytes=52) 27 25 BUFFER (SORT) (Cost=24 Card=100 Bytes=10400) 28 27 FIXED TABLE (FULL) OF 'X$KDXST' (TABLE (FIXED) ) (Cost=24 Card=100 Bytes=10400) 29 24 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' (TABLE) (C ost=0 Card=1 Bytes=30) 30 29 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (INDEX (UNIQUE)) (Cost=0 Card=1) 31 23 TABLE ACCESS (CLUSTER) OF 'SEG$' (CLUSTER) (Cost=0 C ard=1 Bytes=11) 32 31 INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (INDEX) (C ost=0 Card=1) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 67 consistent gets 0 physical reads 0 redo size 573 bytes sent via SQL*Net to client 512 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> spool off