SQL> create table test(id varchar2(60) not null primary key,mc varchar2(60));
表已创建。
SQL> begin
2 for i in 1..1000 loop
3 insert into test values(i,i);
4 end loop;
5 end;
6 /
PL/SQL 过程已成功完成。
SQL> commit;
提交完成。
SQL> select index_name from user_indexes where table_name='TEST';
INDEX_NAME
------------------------------------------------------------
SYS_C00198273
SQL> select /*+index(test SYS_C00198273)*/ * from test where id like '%333';
ID
--------------------------------------------------------------------------------
MC
--------------------------------------------------------------------------------
333
333
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=64)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (TABLE) (Cost=1 Ca
rd=1 Bytes=64)
2 1 INDEX (RANGE SCAN) OF 'SYS_C00198273' (INDEX (UNIQUE)) (
Cost=2 Card=9)
统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
120 consistent gets
3 physical reads
224 redo size
391 bytes sent via SQL*Net to client
504 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from test where id like '%333';
ID
--------------------------------------------------------------------------------
MC
--------------------------------------------------------------------------------
333
333
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=1 Bytes=64)
1 0 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=9 Card=1 Bytes
=64)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
391 bytes sent via SQL*Net to client
504 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from test where id like '333%';
ID
--------------------------------------------------------------------------------
MC
--------------------------------------------------------------------------------
333
333
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=64)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (TABLE) (Cost=1 Ca
rd=1 Bytes=64)
2 1 INDEX (RANGE SCAN) OF 'SYS_C00198273' (INDEX (UNIQUE)) (
Cost=2 Card=1)
统计信息
----------------------------------------------------------
9 recursive calls
0 db block gets
30 consistent gets
3 physical reads
0 redo size
391 bytes sent via SQL*Net to client
504 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed