索引（二）:储键值、有序、高度相关案例。

最新推荐文章于 2021-01-27 08:50:02 发布
最新推荐文章于 2021-01-27 08:50:02 发布
阅读量148 收藏
点赞数
分类专栏： Oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_22083251/article/details/106568348
版权

索引的特性存储键值、有序、高度相关案例。

1、‘分区表各类聚合优化玄机

drop table range_part_tab purge;
--注意，此分区为范围分区

--例子1
create table range_part_tab (id number,deal_date date,area_code number,nbr number,contents varchar2(4000))
           partition by range (deal_date)
           (
           partition p_201301 values less than (TO_DATE('2013-02-01', 'YYYY-MM-DD')),
           partition p_201302 values less than (TO_DATE('2013-03-01', 'YYYY-MM-DD')),
           partition p_201303 values less than (TO_DATE('2013-04-01', 'YYYY-MM-DD')),
           partition p_201304 values less than (TO_DATE('2013-05-01', 'YYYY-MM-DD')),
           partition p_201305 values less than (TO_DATE('2013-06-01', 'YYYY-MM-DD')),
           partition p_201306 values less than (TO_DATE('2013-07-01', 'YYYY-MM-DD')),
           partition p_201307 values less than (TO_DATE('2013-08-01', 'YYYY-MM-DD')),
           partition p_201308 values less than (TO_DATE('2013-09-01', 'YYYY-MM-DD')),
           partition p_201309 values less than (TO_DATE('2013-10-01', 'YYYY-MM-DD')),
           partition p_201310 values less than (TO_DATE('2013-11-01', 'YYYY-MM-DD')),
           partition p_201311 values less than (TO_DATE('2013-12-01', 'YYYY-MM-DD')),
           partition p_201312 values less than (TO_DATE('2014-01-01', 'YYYY-MM-DD')),
           partition p_201401 values less than (TO_DATE('2014-02-01', 'YYYY-MM-DD')),
           partition p_201402 values less than (TO_DATE('2014-03-01', 'YYYY-MM-DD')),
           partition p_max values less than (maxvalue)
           )
           ;

alter table RANGE_PART_TAB modify nbr not null;
--以下是插入2020年一整年日期随机数和表示xx地区号含义（591到599）的随机数记录，共有10万条，如下：
insert into range_part_tab (id,deal_date,area_code,nbr,contents)
      select rownum,
             to_date( to_char(sysdate-365,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'),
             ceil(dbms_random.value(591,599)),
             ceil(dbms_random.value(18900000001,18999999999)),
             rpad('*',400,'*')
        from dual
      connect by rownum <= 100000;
commit;

 

--以下是插入2020年一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有10万条，如下：
insert into range_part_tab (id,deal_date,area_code,nbr,contents)
      select rownum,
             to_date( to_char(sysdate,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'),
             ceil(dbms_random.value(591,599)),
             ceil(dbms_random.value(18900000001,18999999999)),
             rpad('*',400,'*')
        from dual
      connect by rownum <= 100000;
commit;

create index idx_part_id on range_part_tab (id) ;
create index idx_part_nbr on range_part_tab (nbr) local;

--统计信息系统一般会自动收集，这只是首次建成表后需要操作一下，以方便测试
exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'RANGE_PART_TAB',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ;  


set autotrace on
set linesize 1000
select max(nbr) max_nbr from range_part_tab partition(p_201305);(使用分区的写法，使用了nbr的索引)

Execution Plan
----------------------------------------------------------
Plan hash value: 1219885076

------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time       | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |     1 |    13 |     1   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE         |           |     1 |    13 |        |       |       |       |
|   2 |   PARTITION RANGE SINGLE    |           |     1 |    13 |     1   (0)| 00:00:01 |     5 |     5 |
|   3 |    INDEX FULL SCAN (MIN/MAX)| IDX_PART_NBR |     1 |    13 |     1   (0)| 00:00:01 |     5 |     5 |
------------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      0  consistent gets
      0  physical reads
      0  redo size
    523  bytes sent via SQL*Net to client
    525  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

select max(nbr) max_nbr（经典查询方法走了全表扫描，而不走索引，因为有deal_date过滤条件上没有索引，假设走了索引那么它需要nbr索引的rowid回表去取 deal_data的数据）
  from range_part_tab
 where deal_date >= TO_DATE('2013-05-01', 'YYYY-MM-DD')
   and deal_date < TO_DATE('2013-06-01', 'YYYY-MM-DD');

Execution Plan
----------------------------------------------------------
Plan hash value: 4190023598

----------------------------------------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |     1 |    22 |     2   (0)| 00:00:01 |     |     |
|   1 |  SORT AGGREGATE     |         |     1 |    22 |          |      |     |     |
|   2 |   PARTITION RANGE SINGLE|         |     1 |    22 |     2   (0)| 00:00:01 |     5 |     5 |
|   3 |    TABLE ACCESS FULL    | RANGE_PART_TAB |     1 |    22 |     2   (0)| 00:00:01 |     5 |     5 |
----------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      0  consistent gets
      0  physical reads
      0  redo size
    523  bytes sent via SQL*Net to client
    525  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

2、分区索引的性能反而更低

drop table part_tab purge;
create table part_tab (id int,col2 int,col3 int)
        partition by range (id)
        (
        partition p1 values less than (10000),
        partition p2 values less than (20000),
        partition p3 values less than (30000),
        partition p4 values less than (40000),
        partition p5 values less than (50000),
        partition p6 values less than (60000),
        partition p7 values less than (70000),
        partition p8 values less than (80000),
        partition p9 values less than (90000),
        partition p10 values less than (100000),
        partition p11 values less than (maxvalue)
        )
        ;
insert into part_tab select rownum,rownum+1,rownum+2 from dual connect by rownum <=110000;
commit;
create  index idx_par_tab_col2 on part_tab(col2) ;
create  index idx_par_tab_col3 on part_tab(col3) ;

drop table norm_tab purge;
create table norm_tab  (id int,col2 int,col3 int);
insert into norm_tab select rownum,rownum+1,rownum+2 from dual connect by rownum <=110000;
commit;
create  index idx_nor_tab_col2 on norm_tab(col2) ;
create  index idx_nor_tab_col3 on norm_tab(col3) ;

set autotrace traceonly
set linesize 1000
set timing on
select * from part_tab where col2=8 ;
执行计划
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                  |     1 |    39 |    13   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL               |                  |     1 |    39 |    13   (0)| 00:00:01 |     1 |    11 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PART_TAB         |     1 |    39 |    13   (0)| 00:00:01 |     1 |    11 |
|*  3 |    INDEX RANGE SCAN                | IDX_PAR_TAB_COL2 |     1 |       |    12   (0)| 00:00:01 |     1 |    11 |
-----------------------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         24  consistent gets
          0  physical reads
          0  redo size
        539  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
          
          
select * from norm_tab where col2=8 ;

执行计划
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    39 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| NORM_TAB         |     1 |    39 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_NOR_TAB_COL2 |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        543  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
        
        
        
          
select * from part_tab where col2=8 and id=2;
select * from norm_tab where col2=8 and id=2;

--查看索引高度等信息
select index_name,
          blevel,
          leaf_blocks,
          num_rows,
          distinct_keys,
          clustering_factor
     from user_ind_statistics
    where table_name in( 'NORM_TAB');
    
select index_name,
          blevel,
          leaf_blocks,
          num_rows,
          distinct_keys,
          clustering_factor FROM USER_IND_PARTITIONS where index_name like 'IDX_PAR_TAB%';

以上查询条件建立了本地索引，但是本地索引在查询条件中没有被用到，这样反而不如建立全局索引；以上还说明如果本都索引用不到的话，对分区表的查询反而不如普通表的索引查询效率要高。所以建立本地索引还是全局索引要考业务的需求来建立。

3、同时取最大值和最小值的优化案例

MAX/MIN 的索引优化
  drop table t purge;
  create table t as select * from dba_objects;
  update t set object_id=rownum;
  commit;
  alter table t add constraint pk_object_id primary key (OBJECT_ID);
  set autotrace on
  set linesize 1000
  select max(object_id) from t;
  select min(object_id) from t;

 
--等价改写，为数不多的SQL改写复杂了性能更优的情况
set linesize 1000
set autotrace on

select max(object_id),min(object_id) from t;
执行计划
--------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |     1 |    13 |    46   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |              |     1 |    13 |            |          |
|   2 |   INDEX FAST FULL SCAN| PK_OBJECT_ID | 74796 |   949K|    46   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        160  consistent gets
          0  physical reads
          0  redo size
        502  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
   
   
          
select max, min
  from (select max(object_id) max from t ) a,
       (select min(object_id) min from t ) b;
执行计划
---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    26 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |              |     1 |    26 |     4   (0)| 00:00:01 |
|   2 |   VIEW                       |              |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE            |              |     1 |    13 |            |          |
|   4 |     INDEX FULL SCAN (MIN/MAX)| PK_OBJECT_ID |     1 |    13 |     2   (0)| 00:00:01 |
|   5 |   VIEW                       |              |     1 |    13 |     2   (0)| 00:00:01 |
|   6 |    SORT AGGREGATE            |              |     1 |    13 |            |          |
|   7 |     INDEX FULL SCAN (MIN/MAX)| PK_OBJECT_ID |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        480  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

生命只剩百分之一
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
索引存储结构和分块(索引)查找
further_的博客
07-08 2810
索引存储结构 索引存储结构＝数据表+索引表 索引表(index blocked table)中的每一项称为索引项，索引项的一般形式:(关键字,地址) 举个例子： 分块查找的基本思想 分块查找又称分块索引查找(index blocked search)是一种介于顺序查找和二分查找之间的查找方法。其基本思想是： （1）将数据表str[0…n-1]均分为b块,前b-1块中记录个数为s=n/b，最后一块即第b块的记录数小于等于s； （2）每一块中的关键字不一定有序，但前一块中的最大关键字必须小于后一块中的最小关键
知识：my学习主题索引
02-03
学习MySQL时，应掌握SQL基本操作、事务处理、索引优化、备份与恢复等核心概念。 【Kubernetes】 Kubernetes，通常简称为K8s，是Google开源的一款容器编排系统，用于自动化部署、扩展和管理容器化应用程序。...
mysql的索引例子_数据库索引实例
weixin_32098599的博客
01-27 368
参考文献：1.创建表并插入数据在Sql Server2008中创建测试数据库Test，接着创建数据库表并插入数据，sql代码如下：USETestIF EXISTS (SELECT * FROMINFORMATION_SCHEMA.TABLESWHERE TABLE_NAME = 'emp_pay')DROP TABLEemp_payGOUSETestIF EXISTS (SELECT name F...
取得对象键值属性有序的数组
u012773082的博客
05-13 171
var o={ name:"qiaozhen", work:function(){ console.log("working..."); }, _age:20, get age(){ return this._age; }, set age(val){ if(val<0||val>150){ throw ...
【数据库原理系列】索引存储及分类
菜鸡也有大佬梦
11-27 3024
索引 概述 索引 索引是定义在存储表(Table)基础之上，有助于无需检查所有记录而快速定位所需记录的一种辅助存储结构，由一系列存储在磁盘上的索引项(indexentries)组成，每一索引项又由两部分构成: 索引字段：由Table中某些列(通常是一列)中的值串接而成。索引中通常存储了索引字段的每一个值(也有不是这样的)。 行指针：指向Table中包含索引字段值的记录在磁盘上的存储位置。 存储索引项的文件为索引文件，相对应，存储表又称为主文件 索引文件比主文件小很多。通过检索一个小的索引
MySql索引那些事
weixin_33918357的博客
03-31 279
概述 上一篇文章《一条sql语句在mysql中是如何执行的》我们聊到了sql语句内部的执行，包括InnoDB引擎是如何支持事务的，如何做到可以备份恢复的，那么今天我们来聊一聊MySql索引的那些事，在这篇文章中，我会主要聊聊InnoDB下索引的数据结构，索引如何起作用的，如何更好的利用索引提高效率。 一、什么是索引 数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中...
Python程序设计：NBA球星信息查询（案例.pptx
06-12
字典是Python中的一种关联数据结构，由键值对组成，键是唯一的，用于查找对应的值。在这个任务中，我们可以创建一个字典，以球队为键，球员信息的列表为值，这样可以方便地根据球队快速获取球员信息。 集合是一种...
pythontest:小型Python案例，包括sortprintdictrequestsurllib
03-30
4. **列表与字典**：列表（list）是有序可变序列，而字典（dict）是无序的键值对集合。它们在Python中被广泛使用，理解其操作如索引、切片、增删改查等至关重要。 5. **排序与打印**：`sort`方法用于对列表进行原地...
泛型和集合案例.7z
08-08
1. List：有序的集合，允许重复元素，支持索引访问。ArrayList和LinkedList是两种常见的List实现。 2. Set：无序且不允许重复元素的集合。HashSet、LinkedHashSet和TreeSet提供了不同的性能和排序特性。 3. Map：...
Google编写的快速键值存储库
04-06
2. **排序的键**：LevelDB中的所有键都是有序的，这意味着你可以通过范围查询来获取一系列连续的键值对。这对于数据聚合和索引构建非常有用。 3. **数据版本控制**：LevelDB支持多版本并发控制（MVCC），允许同时...
存储（四）—— 索引
xinzhongtianxia的博客
03-22 3432
本文同步发表于自己的博客上，那里比较清静，没有广告：存储（四）—— 索引 何谓索引 如存储（一）—— 序言中所说，找出自己想要的东西并不难，难的是从大量自己不想要的信息中飞快的找出自己想要的东西。 缓存已经帮我们把数据从更远更慢的介质提前移动到了更近更快的介质上，即便如此，从百万、千万、上亿条的数据中找出我们想要的那条数据，也并非易事。 好比查字典，不可能一页一页翻，所以，字典都是有目录的，这个...
LinkedHashMap存放有序键值对，以及迭代方式
fyldragonfyl
04-09 1034
HashMap存放键值对是无序的： package com.example.myapp.linkedhashmap; import org.junit.Test; import java.util.HashMap; import java.util.LinkedHashMap; import java.util.Map; /** * Created by mike. * Create...
关系数据库的索引技术
weixin_33913377的博客
08-22 860
摘  要：索引是影响关系数据库（RDBMS）性能的重要因素之一。当今常用的关系数据库，如SQL Server、Sybase、Oracle、DB2等，为了提高性能，都提供相应的索引机制。本文以微软的SQL Server数据库为例，介绍索引的概念、使用方法以及日常维护。 关键词：数据库、索引、检索 1 索引的概念 索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表...
索引案例与维护索引和表
shareing
07-22 2155
索引的案例 支持多种过滤条件 避免多个范围条件 优化排序 维护索引和表 找到并修复损坏的表 更新索引统计信息 减少索引和数据的碎片 索引的案例 假设一个在线约会网站的用户信息表中含有国家、地区、城市、性别、眼睛颜色等。若想让该表有更高效的索引组和来提供这些特征的各种组合来搜索用户，应该如何去做呢？ 支持多种过滤条件 一般来说我们需要看看哪些列拥有很多不同的取...
链表的有序集合
11-13 485
链表的有序集合 Time Limit: 1000ms   Memory limit: 65536K  有疑问？点这里^_^ 题目描述 　　集合有一个重要的特性：互异性，即集合中任意两个元素都是不同的，互异性使得集合中的元素没有重复。给你 n 个包含重复数字的无序正整数序列，建立一个有序链表，链表中的结点按照数值非降序排列且不包含重复元素，输出该有序链表。 输入 输入包
Oracle DBA 面试题整理（更新中）
qq_22083251的博客
07-21 3719
1、解释一条update语句的执行流程？ 2、简述实例恢复的过程？以及实例恢复的起点和终点。 3、RAC中lms和lmd进程的作用是什么？ 4、当数据库中出现了逻辑坏块需要怎么修复？ 5、业务反馈数据库很慢，请简述盘查的过程？ 6、异构平台下迁移数据，请简述迁移流程？ 7、Oracle中的大页占用内存的多少？pga和sga是怎么分配的？ 8、RAC中的vip和scan-ip的作用？ 9、网络心跳正常的情况下，磁盘心跳出了问题会发生什么？ 10、Oracle中内核参数调优有哪几个？ 11、搭建环境的时候ora
Oracle的日志挖掘恢复数据
qq_22083251的博客
10-16 3236
　　oracle日志挖掘是一种十分强大的数据恢复技术，只要你保障你的归档日志和重做日志是完整的，那么就可以将你的数据恢复到任何时刻。简单叙述一下日志挖掘的基本原理，然后进行一个简单的小实验。 　　日志挖掘时基于redo日志和归档日志的基础之上来进行日志加载并进行恢复，挖掘，挖掘，挖的就是你的redo日志和归档日志，当数据库运行过程中，你的所有数据更改操作会被记录在redo日志中，而oracle ...
ORA-29770 案例分析。
qq_22083251的博客
07-26 2876
1.ORA-29770 was occured because of the LGWR is hung for a long time, so LMHB process terminating the instance. <alert_xxx1.log> 2020-05-07T02:00:40.087133+08:00 Errors in file /opt/oracle/diag/rdbms/edw/edw1/trace/edw1_lmhb_56258.trc (incident=10...
V$LOCK和V$LOCKED_OBJECT的区别
qq_22083251的博客
07-18 2566
V$LOCK和V$LOCKED_OBJECT 查询锁表都会用到V$LOCK和V$LOCKED_OBJECT,但这两者不是相同的。我们先看看它们各自的主要字段：1.v$lock sid： 会话SID，通常与v$session关联。 type： 锁类型，TM表示表锁或DML锁，TX表示行锁或事务锁，UL表示用户锁。我们主要关注TX和TM两种型的锁其它均为系统锁，会...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值