oadb01:/home/oracle/sbin> sqlplus query/query
SQL*Plus: Release 11.2.0.4.0 Production on 星期二 10月 20 11:16:19 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'DEPT',
estimate_percent => 30,
method_opt => 'for all columns size repeat',
no_invalidate => FALSE,
degree => 8,
cascade => TRUE);
END;
/
create table test(zipcode int,lastname varchar2(20),address varchar2(100),flag int);
begin
for i in 1..1000000
loop
insert into people values(i,i||'aa',i||'bb');
end loop;
end;
模拟数据:
begin
for i in 1..100
loop
insert into people values(9999,'zhao','zhongjun');
end loop;
end;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'QUERY',
tabname => 'PEOPLE',
estimate_percent => 100,
method_opt => 'for all columns size repeat',
no_invalidate => FALSE,
degree => 8,
cascade => TRUE);
END;
SQL> create index p_idx1 on people(zipcode);
Index created.
SQL> select count(*) from test;
COUNT(*)
----------
3100100
SQL> select count(*) from test where zipcode=9999;
COUNT(*)
----------
100003
SQL> select * from test where zipcode=9999 and lastname like '%aa%';
ZIPCODE LASTNAME ADDRESS FLAG
---------- -------------------- ---------- ----------
9999 9999aa 9999bb 9999
9999 9999aa 9999bb 9999
9999 9999aa 9999bb 9999
9999 144aa66 1bb55 1
9999 244aa66 2bb55 2
9999 344aa66 3bb55 3
9999 444aa66 4bb55 4
9999 544aa66 5bb55 5
9999 644aa66 6bb55 6
9999 744aa66 7bb55 7
9999 844aa66 8bb55 8
ZIPCODE LASTNAME ADDRESS FLAG
---------- -------------------- ---------- ----------
9999 944aa66 9bb55 9
9999 1044aa66 10bb55 10
13 rows selected.
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'test',
tabname => 'test',
estimate_percent => 100,
method_opt => 'for all columns size repeat',
no_invalidate => FALSE,
degree => 8,
cascade => TRUE);
END;
SQL> select * from test where zipcode=9999 and lastname like '%aa%';
13 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4199073991
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 28 | 7 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | P_IDX1 | 3 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LASTNAME" LIKE '%aa%' AND "LASTNAME" IS NOT NULL)
2 - access("ZIPCODE"=9999)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
596 consistent gets
0 physical reads
0 redo size
1057 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
13 rows processed
SQL> select * from test where zipcode=9999 and lastname like '%aa%';
13 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1934812006
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 28 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | P_IDX2 | 1 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ZIPCODE"=9999)
filter("LASTNAME" LIKE '%aa%' AND "LASTNAME" IS NOT NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
288 consistent gets
0 physical reads
0 redo size
1127 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
13 rows processed