oracle单表分页查询的索引创建

最新推荐文章于 2022-09-25 23:35:55 发布
最新推荐文章于 2022-09-25 23:35:55 发布
阅读量460 收藏
点赞数
分类专栏： SQL优化 文章标签： 数据库 索引 oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/sinat_33137351/article/details/109526883
版权

分页模板

select *
  from (select *
          from (select a.*, rownum rn
                  from (查询SQL) a)
         where rownum <= 10)
where rn >= 1;

创建测试表

SYS@ora19c>create table t_page as select * from dba_objects;

Table created.

--注意所有列都是可为空的

SYS@ora19c> desc t_page;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(128)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(128)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(23)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NAMESPACE                                          NUMBER
 EDITION_NAME                                       VARCHAR2(128)
 SHARING                                            VARCHAR2(18)
 EDITIONABLE                                        VARCHAR2(1)
 ORACLE_MAINTAINED                                  VARCHAR2(1)
 APPLICATION                                        VARCHAR2(1)
 DEFAULT_COLLATION                                  VARCHAR2(100)
 DUPLICATED                                         VARCHAR2(1)
 SHARDED                                            VARCHAR2(1)
 CREATED_APPID                                      NUMBER
 CREATED_VSNID                                      NUMBER
 MODIFIED_APPID                                     NUMBER
 MODIFIED_VSNID                                     NUMBER

下面5个例子中，需要将查询结果分页显示，每页显示10条。

例1：select * from t_page order by object_id;

没有过滤条件，只有排序。注意索引（位图索引除外）不保存全是null的记录。

create index IDX_T_PAGE_OBJ_ID on t_page(object_id,0);

select *
  from (select t.*, rownum rn
          from (select * from t_page order by object_id) t
         where rownum <= 10)
where rn > = 1;

----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |      1 |        |       |     3 (100)|          |     10 |00:00:00.01 |       5 |
|*  1 |  VIEW                          |                   |      1 |     10 |  4940 |     3   (0)| 00:00:01 |     10 |00:00:00.01 |       5 |
|*  2 |   COUNT STOPKEY                |                   |      1 |        |       |            |          |     10 |00:00:00.01 |       5 |
|   3 |    VIEW                        |                   |      1 |     10 |  4810 |     3   (0)| 00:00:01 |     10 |00:00:00.01 |       5 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T_PAGE            |      1 |  26010 |  2946K|     3   (0)| 00:00:01 |     10 |00:00:00.01 |       5 |
|   5 |      INDEX FULL SCAN           | IDX_T_PAGE_OBJ_ID |      1 |     10 |       |     2   (0)| 00:00:01 |     10 |00:00:00.01 |       3 |
----------------------------------------------------------------------------------------------------------------------------------------------

例2：select * from t_page where owner = 'SCOTT' order by object_id;

继续用例1创建的索引

select *
  from (select t.*, rownum rn
          from (select /*+ index(t_page IDX_T_PAGE_OBJ_ID)*/* from t_page where owner = 'SCOTT' order by object_id) t
         where rownum <= 10)
where rn > = 1;
----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |      1 |        |       |   550 (100)|          |     10 |00:00:00.01 |     659 |
|*  1 |  VIEW                          |                   |      1 |     10 |  4940 |   550   (1)| 00:00:01 |     10 |00:00:00.01 |     659 |
|*  2 |   COUNT STOPKEY                |                   |      1 |        |       |            |          |     10 |00:00:00.01 |     659 |
|   3 |    VIEW                        |                   |      1 |     10 |  4810 |   550   (1)| 00:00:01 |     10 |00:00:00.01 |     659 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| T_PAGE            |      1 |     10 |  1160 |   550   (1)| 00:00:01 |     10 |00:00:00.01 |     659 |
|   5 |      INDEX FULL SCAN           | IDX_T_PAGE_OBJ_ID |      1 |  26010 |       |    55   (0)| 00:00:01 |  26007 |00:00:00.01 |      66 |
----------------------------------------------------------------------------------------------------------------------------------------------

应该在索引扫描时就得到10条数据,创建owner,object_id字段的联合索引

create index idx_t_page_owner_obj_id on t_page(owner,object_id,0);

select *
  from (select t.*, rownum rn
          from (select /*+ index(t_page idx_t_page_owner_obj_id)*/* from t_page where owner = 'SCOTT' order by object_id) t
         where rownum <= 10)
where rn > = 1;

----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                         |      1 |        |       |     3 (100)|          |     10 |00:00:00.01 |       6 |
|*  1 |  VIEW                          |                         |      1 |     10 |  4940 |     3   (0)| 00:00:01 |     10 |00:00:00.01 |       6 |
|*  2 |   COUNT STOPKEY                |                         |      1 |        |       |            |          |     10 |00:00:00.01 |       6 |
|   3 |    VIEW                        |                         |      1 |     10 |  4810 |     3   (0)| 00:00:01 |     10 |00:00:00.01 |       6 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T_PAGE                  |      1 |     10 |  1160 |     3   (0)| 00:00:01 |     10 |00:00:00.01 |       6 |
|*  5 |      INDEX RANGE SCAN          | IDX_T_PAGE_OWNER_OBJ_ID |      1 |     12 |       |     2   (0)| 00:00:01 |     10 |00:00:00.01 |       3 |
----------------------------------------------------------------------------------------------------------------------------------------------------

例3：select * from t_page order by object_id, object_name desc;

create index idx_t_page_obj_id_name on t_page(object_id, object_name desc,0);

select *
  from (select *
          from (select a.*, rownum rn
                  from (select /*+ index(t_page idx_t_page_obj_id_name)*/*
                          from t_page
                         order by object_id, object_name desc) a)
         where rownum <= 10)
where rn >= 1;

-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                        |      1 |        |       |   732 (100)|          |     10 |00:00:00.01 |       5 |
|*  1 |  VIEW                            |                        |      1 |     10 |  4940 |   732   (1)| 00:00:01 |     10 |00:00:00.01 |       5 |
|*  2 |   COUNT STOPKEY                  |                        |      1 |        |       |            |          |     10 |00:00:00.01 |       5 |
|   3 |    VIEW                          |                        |      1 |  26010 |    12M|   732   (1)| 00:00:01 |     10 |00:00:00.01 |       5 |
|   4 |     COUNT                        |                        |      1 |        |       |            |          |     10 |00:00:00.01 |       5 |
|   5 |      VIEW                        |                        |      1 |  26010 |    11M|   732   (1)| 00:00:01 |     10 |00:00:00.01 |       5 |
|   6 |       TABLE ACCESS BY INDEX ROWID| T_PAGE                 |      1 |  26010 |  2946K|   732   (1)| 00:00:01 |     10 |00:00:00.01 |       5 |
|   7 |        INDEX FULL SCAN           | IDX_T_PAGE_OBJ_ID_NAME |      1 |  26010 |       |   139   (0)| 00:00:01 |     10 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------------------------------------------------------------

例4：select * from t_page where owner = 'SYS' and object_id > 1000 order by object_name;

按照索引创建ESR原则，字段顺序为等值（owner = 'SYS'），排序(order by object_name)，范围(object_id > 1000)。

create index idx_owner_obj_name_obj_id on t_page(owner,object_name,object_id,0);

select *
  from (select *
          from (select a.*, rownum rn
                  from (select /*+ index(t_page idx_owner_obj_name_obj_id)*/*
                          from t_page
                         where owner = 'SYS'
                           and object_id > 1000
                         order by object_name) a)
         where rownum <= 10)
where rn >= 1;

--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                           |      1 |        |       |  7201 (100)|          |     10 |00:00:00.01 |       8 |
|*  1 |  VIEW                            |                           |      1 |     10 |  4940 |  7201   (1)| 00:00:01 |     10 |00:00:00.01 |       8 |
|*  2 |   COUNT STOPKEY                  |                           |      1 |        |       |            |          |     10 |00:00:00.01 |       8 |
|   3 |    VIEW                          |                           |      1 |  14267 |  6882K|  7201   (1)| 00:00:01 |     10 |00:00:00.01 |       8 |
|   4 |     COUNT                        |                           |      1 |        |       |            |          |     10 |00:00:00.01 |       8 |
|   5 |      VIEW                        |                           |      1 |  14267 |  6701K|  7201   (1)| 00:00:01 |     10 |00:00:00.01 |       8 |
|   6 |       TABLE ACCESS BY INDEX ROWID| T_PAGE                    |      1 |  14267 |  1894K|  7201   (1)| 00:00:01 |     10 |00:00:00.01 |       8 |
|*  7 |        INDEX RANGE SCAN          | IDX_OWNER_OBJ_NAME_OBJ_ID |      1 |  14267 |       |    89   (0)| 00:00:01 |     10 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------------------------------------------------------

例5：select * from t_page where owner like 'SYS%' and object_id > 1000 order  by object_name;

create index idx_t_page_owner_obj_name_objid on t_page(object_name,owner,object_id,0);

select *
  from (select *
          from (select a.*, rownum rn
                  from (select /*+ index(t_page idx_t_page_owner_obj_name_objid)*/*
                          from t_page
                         where owner like 'SYS%'
                           and object_id > 1000
                         order by object_name) a)
         where rownum <= 10)
where rn >= 1;

--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                                 |      1 |        |       |  6817 (100)|          |     10 |00:00:00.01 |       8 |
|*  1 |  VIEW                            |                                 |      1 |     10 |  4940 |  6817   (1)| 00:00:01 |     10 |00:00:00.01 |       8 |
|*  2 |   COUNT STOPKEY                  |                                 |      1 |        |       |            |          |     10 |00:00:00.01 |       8 |
|   3 |    VIEW                          |                                 |      1 |  14723 |  7102K|  6817   (1)| 00:00:01 |     10 |00:00:00.01 |       8 |
|   4 |     COUNT                        |                                 |      1 |        |       |            |          |     10 |00:00:00.01 |       8 |
|   5 |      VIEW                        |                                 |      1 |  14723 |  6915K|  6817   (1)| 00:00:01 |     10 |00:00:00.01 |       8 |
|   6 |       TABLE ACCESS BY INDEX ROWID| T_PAGE                          |      1 |  14723 |  1955K|  6817   (1)| 00:00:01 |     10 |00:00:00.01 |       8 |
|*  7 |        INDEX FULL SCAN           | IDX_T_PAGE_OWNER_OBJ_NAME_OBJID |      1 |  14723 |       |   155   (0)| 00:00:01 |     10 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------------------------------------------------------------

 

数据库打工人
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
【数据库】Oracle单表查询性能优化 - 索引应用
你的格子衬衫
05-24 944
目录 问题说明 解决方案 - 场景A 解决方案 - 场景B 解决方案 - 场景C 深入理解索引 - 索引的逻辑结构 深入理解索引 - 索引的三个特点 深入理解索引 - 索引的注意事项 问题说明： 系统上线后，用户数据不断增长，从最初的几十万记录发展至上千万条记录。运维人员反馈原有的存储过程运行效率愈发低下。review时发现以下可改进之处： 场景A：数据表的索引与查询语句子条件（where）筛选字段无关，未合理创建索引； 场景B：业务逻辑分析不足，产生非必要的表关联，50%的语..
数据库_Oracle学习分页、索引、视图
qq_40795780的博客
03-12 176
分页 rowid 和 rownum rownum —— 是伪列，是一组结果集的序号 只要有select就会有结果集。就会有rownum，每一套结果集都会有自己的rownum。（每一个select都不一样） 规律：把已经确定了的结果集中的数据。第一行用1开始标记，按行标记值依次+1 select empno,ename,rownum from emp; //显示伪劣 注意：序列号是生产结果...
ORACLE分页查询基于索引的考虑
06-01 393
分页查询在应用中使用非常广泛，在oracle中最常用的做法是使用rownum进行分页查询。 比如： select * from (select rownum rnm, z.* from (select *...
分页查询、视图、索引
yk_3215123的博客
11-09 300
*分页查询： --  需求:当每次查询的记录数比较大，              通常一页显示不下，              此时我们可以进行分页查询。 --  关键字:limit --  用法:  limit begin,size; --  begin：记录的开始行数. 偏移量 --  size：每页的最大记录数。 --  完整的DQL     书写顺序：select,from,where,...
oracle 单表优化索引,Oracle索引的优化设计
weixin_39633054的博客
04-03 180
通过合理使用和管理Oracle索引，可以加快数据库的查询效率，下面就为您详细介绍Oracle索引的优化设计，希望能够对您有所启迪。1、管理组织索引Oracle索引可以大大加快数据库的查询速度，Oracle索引把表中的逻辑值映射到安全的RowID，因此索引能进行快速定位数据的物理地址。但是有些DBA发现，对一个大型表建立的索引，并不能改善数据查询速度，反而会影响整个数据库的性能。这主要是和SGA的数...
Oracle实现分页查询的SQL语法汇总
12-15
使用WITH子查询可以创建一个临时表，然后在此基础上进行分页。尽管这看起来更复杂，但在某些情况下可能更有效。如下： ```sql With partdata as( SELECT ROWNUM AS ROWNO, TT.* FROM ( Select *from k_task T ...
Oracle分页查询性能优化代码详解
09-09
总之，Oracle分页查询的性能优化需要综合考虑查询语句的编写、索引的使用以及数据库设计等多个方面。理解`ROWNUM`的工作原理，以及如何利用执行计划进行优化，是提升分页查询性能的关键。在实际操作中，应根据具体...
oracle分页查询sql
01-15
### Oracle分页查询详解 #### 一、分页查询的重要性 在数据库操作中，分页查询是一项非常重要的技术。尤其当处理大量数据时，一次性获取所有数据不仅会消耗大量的网络带宽，还可能导致前端页面加载缓慢，用户体验...
Oracle分页查询的实例详解
12-15
以下是对Oracle分页查询的实例详解： 1. **Oracle分页查询基础** Oracle分页查询通常涉及ROWNUM伪列的使用。ROWNUM在查询执行过程中为每一行分配一个唯一的数字，从1开始递增。在以下两个示例中，我们可以看到两种...
mysql oracle和sqlserver分页查询实例解析
12-16
本文将分别介绍MySQL、Oracle和SQL Server三种主流数据库系统的分页查询实现。 首先，MySQL的分页查询非常直观，只需要使用`LIMIT`关键字。基本语法是`SELECT * FROM (query) o LIMIT firstIndex, pageSize`。这里...
在oracle上实现的单表的分页框架，使用该框架只需要新建一个显示的jsp，更改数据库连接，创建对应的javaBean就可以使用，详情请下载后查看分页框架说明
08-24
本人在使用分页的时候嫌其麻烦，就自己改写了后为一个框架，暂时只支持对单表分页，该分页通过对表的相关反射，使用该分页只需要保证javaBean中的属性和表中的属性对应一致(不区分大小写)然后在页面上传上相关参数就可以得到一个封装好了的list然后在页面上直接使用就可以非常方便
添加聚集索引后高效分页查询的效率
TaLinBoy的博客
05-24 873
GO ALTER proc [dbo].[ex3]( @count int --次数 ) as begin declare @i int set @i = 0 while @i < @count begin SELECT * FROM product0 w1 WHERE id in ( SELECT top 3 id FROM
Oracle优化——单表分页优化
雅冰石的专栏
11-03 180
错误的写法： select * from (select t.*,rownum rn from (需要分页的sql)t ) where rn >=1 and rn <=10; 正确的写法： select * from (select * from (select a.*,rownum rn from (需要分页的sql) a ) where rownum <=10) where rn >=1; --参考了Oracle优化——单表分页优化_一个笨小孩_51CTO博客 ...
sql oracle分页查询,【SQL】Oracle分页查询的三种方法-Oracle
weixin_42466518的博客
04-10 2783
【SQL】Oracle分页查询的三种方法采用伪列 rownum查询前10条记录[sql]select * from t_user t where ROWNUM <10;按照学生ID排名，抓取前三条记录[java]SELECT * FROM(SELECT id,realname FROM T_USER ORDER BY id asc ) WHERE ROWNUM <=3分页SQL写法，从...
oracle的学习2-单表查询&链接查询&子查询&分页查询
云计算大数据学习之小学生的博客
08-03 549
查询 一、单表查询 （一）简单条件查询 1.精确查询 需求：查询水表编号为 30408 的业主记录 查询语句： select * from T_OWNERS where watermeter='30408'   2.模糊查询 需求：查询业主名称包含“刘”的业主记录 查询语句： select * from t_owners where name like '%刘%'   ...
【MySQL开发技巧 】- 分页和索引
m0_64750119的博客
09-25 2082
分页索引、单列索引、组合索引。
数据库 mysql 优化一
weixin_40459875的博客
04-18 997
最近遇到了一个问题，我们测试时的用例库只有一万条数据，但是查询已经肉眼可见的很慢了，于是想着或许可以优化一下查询。 然后今天让男朋友讲了一下数据库优化的知识，虽然自己也学习过，但一直也没在实战中用到过。男朋友做过数据库优化的内容，所以今天就让他借禅道的数据库讲了一下。 比较散装，但是也很口语化非常容易理解，而且是项目中经常用到的。 前言：做数据库优化的前提是，我们的表已经存在了，我们在不改变表结构的前提下，如何利用索引、修改查询语句、修改查询方式等方法进行优化。 一、确定索引 1、索引解释及索引查
oracle 查询所以对象,Oracle查询数据库对象所属用户
weixin_35929851的博客
04-03 357
--查询某用户下的表、视图SELECT TABLE_NAME,OWNER,TABLE_TYPE FROM ALL_TAB_COMMENTS WHERE OWNER = ‘VASS‘;--查询某用户下的视图SELECT VIEW_NAME,OWNER FROM ALL_VIEWS WHERE OWNER = ‘VASS‘;--查询某用下的索引SELECT INDEX_NAME,OWNER,TABLE...
springboot oracle 多表分页查询
最新发布
05-10
Spring Boot是一种基于Spring Framework的快速开发Web应用程序的框架，而Oracle是一种强大的关系型数据库。在一些大型项目中，我们需要使用多个表进行分页查询，并将查询结果呈现给用户，这时我们可以结合Spring Boot和Oracle实现多表分页查询。 实现多表分页查询需要引入Spring Boot的分页插件和Oracle的JDBC驱动。在查询数据之前，我们需要定义一个分页信息对象，包含要查询的页数、每页的数据量，以及查询结果的排序信息和过滤条件等。然后，我们可以使用Spring Boot提供的JdbcTemplate或MyBatis框架与Oracle数据库进行数据交互。 为了实现多表分页查询，我们需要在SQL语句中使用Join语句将多个表连接起来，并使用分页插件对查询结果进行分页处理。在处理分页查询时，我们可以采用传统的分页方式，即先查询出所有符合条件的记录总数，然后进行分页查询；也可以采用性能更高的Limit方式，直接指定查询结果的起始位置和数目。 在实现多表分页查询时，我们还需要注意查询效率和查询结果的正确性。通常情况下，我们可以对经常被访问和查询的表进行索引优化，提高查询速度。同时，我们还应该注意使用正确的字段进行Join和分组操作，避免查询结果出现重复数据或遗漏数据的情况。 在使用Spring Boot和Oracle进行多表分页查询时，我们需要掌握相关的知识和技术，才能更好地完成项目开发任务。为此，我们可以参考相关的教程和文档，了解其运行原理和应用场景，提高开发效率和质量。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值