组合索引,索引内过滤

最新推荐文章于 2022-01-18 11:44:12 发布

scan724

最新推荐文章于 2022-01-18 11:44:12 发布

阅读量431

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/zhaoyangjian724/article/details/49275957

本文展示了如何在Oracle数据库中收集表统计信息，并创建索引来优化查询效率。通过具体的SQL语句示例，演示了不同条件下的查询计划及性能表现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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