oracle 复合函数,为什么我这里的复合函数索引的使用上存在问题呢?

最新推荐文章于 2021-04-16 01:11:00 发布
最新推荐文章于 2021-04-16 01:11:00 发布
阅读量192 收藏
点赞数
文章标签： oracle 复合函数

[/PHP]

SQL> select count(1) from diy_userlog where username is not null;

COUNT(1)

----------

22562

SQL> select count(1) from diy_userlog;

COUNT(1)

----------

1963782

diy_userlog中username为空的数据行很多.总共196W行数据中,username不空的总共才只有2W行数据.

这个表的大小是112M

SQL> select trunc(bytes/1024/1024) from user_segments where segment_name=upper('diy_userlog');

TRUNC(BYTES/1024/1024)

----------------------

112

原来就有索引index index1_diy_userlog on diy_userlog(type,username,inputdate),大小是51M;

SQL> select trunc(bytes/1024/1024) from user_segments where segment_name=upper('index1_diy_userlog');

TRUNC(BYTES/1024/1024)

----------------------

51

因为username绝大多数为空,但inputdate基本上都不为空,所以下面创建的复合索引还是很大:44M,所以我立即就删除了.

SQL> create index index3_diy_userlog on diy_userlog(inputdate,username);

Index created

SQL> select trunc(bytes/1024/1024) from user_segments where segment_name=upper('index3_diy_userlog');

TRUNC(BYTES/1024/1024)

----------------------

44

SQL> drop index index3_diy_userlog;

Index dropped

利用索引不索引索引字段全为空的数据行,我创建了下面的复合函数索引,因为username绝大部分都是空的,所以这个索引很小,不到1M

SQL> create index index2_v_diy_userlog on diy_userlog(case when username is not null then inputdate else null end,username);

Index created

SQL> select trunc(bytes/1024/1024) from user_segments where segment_name=upper('index2_v_diy_userlog');

TRUNC(BYTES/1024/1024)

----------------------

0

为了方便使用这里建立的函数索引,建立了如下的视图:

SQL> create view v_diy_userlog as select case when username is not null then inputdate else null end virt_inputdate,username from diy_userlog;

View created

这样

select username,max(inputdate) last_inputdate

from diy_userlog

where username is not null and inputdate is not null

group by username;

我改写成了

select username,max(virt_inputdate) last_inputdate

from v_diy_userlog

where username is not null and virt_inputdate is not null

group by username;

它们是等价的.

我收集了表,索引,包括隐含列上的统计信息,但这个复合的函数索引的使用上存在问题:

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

SQL> show parameter rewrite;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

query_rewrite_enabled                string      TRUE

query_rewrite_integrity              string      enforced

SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

optimizer_mode                       string      ALL_ROWS

SQL> set autotrace traceonly;

SQL> select username,max(inputdate) last_inputdate

from diy_userlog

where username is not null and inputdate is not null

group by username;

2218 rows selected.

SQL> /

2218 rows selected.

Execution Plan

----------------------------------------------------------

---------------------------------------------------------------------------------

| Id  | Operation             | Name               | Rows  | Bytes | Cost (%CPU)|

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |                    |  2216 | 22160 |  1127   (4)|

|   1 |  HASH GROUP BY        |                    |  2216 | 22160 |  1127   (4)|

|   2 |   INDEX FAST FULL SCAN| INDEX1_DIY_USERLOG | 22555 |   220K|  1124   (4)|

---------------------------------------------------------------------------------

Note

-----

- 'PLAN_TABLE' is old version

Statistics

----------------------------------------------------------

6370  consistent gets

2218  rows processed

SQL> select username,max(virt_inputdate) last_inputdate

from v_diy_userlog

where username is not null and virt_inputdate is not null

group by username;

2218 rows selected.

SQL> /

2218 rows selected.

Execution Plan

----------------------------------------------------------

---------------------------------------------------------------------------------

| Id  | Operation             | Name               | Rows  | Bytes | Cost (%CPU)|

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |                    |   245 |  2450 |  1126   (4)|

|   1 |  HASH GROUP BY        |                    |   245 |  2450 |  1126   (4)|

|   2 |   INDEX FAST FULL SCAN| INDEX1_DIY_USERLOG |   259 |  2590 |  1125   (4)|

---------------------------------------------------------------------------------

Note

-----

- 'PLAN_TABLE' is old version

Statistics

----------------------------------------------------------

6370  consistent gets

2218  rows processed

使用视图的形式,这里根本就不使用这个复合的函数索引.

SQL> select username,max(case when username is not null then inputdate else null end) last_inputdate

from diy_userlog

where username is not null and case when username is not null then inputdate els  3  e null end is not null

group by username;

2218 rows selected.

SQL> /

2218 rows selected.

Execution Plan

----------------------------------------------------------

---------------------------------------------------------------------------------

| Id  | Operation             | Name               | Rows  | Bytes | Cost (%CPU)|

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |                    |   245 |  2450 |  1126   (4)|

|   1 |  HASH GROUP BY        |                    |   245 |  2450 |  1126   (4)|

|   2 |   INDEX FAST FULL SCAN| INDEX1_DIY_USERLOG |   259 |  2590 |  1125   (4)|

---------------------------------------------------------------------------------

Note

-----

- 'PLAN_TABLE' is old version

Statistics

----------------------------------------------------------

6370  consistent gets

2218  rows processed

使用原表的形式,还是不使用这个复合的函数索引.

SQL> select /*+ index(v_diy_userlog index2_v_diy_userlog) */username,max(virt_inputdate) last_inputdate

2  from v_diy_userlog

3  where username is not null and virt_inputdate is not null

4  group by username;

2218 rows selected.

SQL> /

2218 rows selected.

Execution Plan

----------------------------------------------------------

------------------------------------------------------------------------------------------

| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)|

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                      |   245 |  2450 |    48   (3)|

|   1 |  HASH GROUP BY               |                      |   245 |  2450 |    48   (3)|

|   2 |   TABLE ACCESS BY INDEX ROWID| DIY_USERLOG          |   259 |  2590 |    47   (0)|

|   3 |    INDEX FULL SCAN           | INDEX2_V_DIY_USERLOG |     3 |       |    45   (0)|

------------------------------------------------------------------------------------------

Note

-----

- 'PLAN_TABLE' is old version

Statistics

----------------------------------------------------------

16702  consistent gets

2218  rows processed

使用视图的形式,强制使用这个复合索引,走了索引全扫描,关键是还回访了表,所以导致使用这个更小索引的执行计划比使用更大索引的执行计划的逻辑IO还要大.

SQL> select /*+ index_ffs(v_diy_userlog index2_v_diy_userlog) */username,max(virt_inputdate) last_inputdate

from v_diy_userlog

where username is not null and virt_inputdate is not null

group by username;

2218 rows selected.

SQL> /

2218 rows selected.

Execution Plan

----------------------------------------------------------

------------------------------------------------------------------------------------------

| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)|

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                      |   245 |  2450 |    48   (3)|

|   1 |  HASH GROUP BY               |                      |   245 |  2450 |    48   (3)|

|   2 |   TABLE ACCESS BY INDEX ROWID| DIY_USERLOG          |   259 |  2590 |    47   (0)|

|   3 |    INDEX FULL SCAN           | INDEX2_V_DIY_USERLOG |     3 |       |    45   (0)|

------------------------------------------------------------------------------------------

Note

-----

- 'PLAN_TABLE' is old version

Statistics

----------------------------------------------------------

16702  consistent gets

2218  rows processed

使用视图的形式,强制使用这个复合索引的快速全扫描,但还是走了和上面完全一样的执行计划.

SQL> select /*+ index_ffs(diy_userlog index2_v_diy_userlog) */username,max(case when username is not null then inputdate else null end) last_inputdate

from diy_userlog

where username is not null and case when username is not null then inputdate else null end is not null

group by username;

2218 rows selected.

SQL> /

2218 rows selected.

Execution Plan

----------------------------------------------------------

------------------------------------------------------------------------------------------

| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)|

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                      |   245 |  2450 |    48   (3)|

|   1 |  HASH GROUP BY               |                      |   245 |  2450 |    48   (3)|

|   2 |   TABLE ACCESS BY INDEX ROWID| DIY_USERLOG          |   259 |  2590 |    47   (0)|

|   3 |    INDEX FULL SCAN           | INDEX2_V_DIY_USERLOG |     3 |       |    45   (0)|

------------------------------------------------------------------------------------------

Note

-----

- 'PLAN_TABLE' is old version

Statistics

----------------------------------------------------------

16702  consistent gets

2218  rows processed

使用原表的形式,强制使用这个复合索引的快速全扫描,但还是走了和上面完全一样的执行计划.

SQL> select /*+ index(diy_userlog index2_v_diy_userlog) */username,max(case when username is not null then inputdate else null end) last_inputdate

2  from diy_userlog

3  where username is not null and case when username is not null then inputdate else null end is not null

4  group by username;

2218 rows selected.

SQL> /

2218 rows selected.

Execution Plan

----------------------------------------------------------

------------------------------------------------------------------------------------------

| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)|

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                      |   245 |  2450 |    48   (3)|

|   1 |  HASH GROUP BY               |                      |   245 |  2450 |    48   (3)|

|   2 |   TABLE ACCESS BY INDEX ROWID| DIY_USERLOG          |   259 |  2590 |    47   (0)|

|   3 |    INDEX FULL SCAN           | INDEX2_V_DIY_USERLOG |     3 |       |    45   (0)|

------------------------------------------------------------------------------------------

Note

-----

- 'PLAN_TABLE' is old version

Statistics

----------------------------------------------------------

16705  consistent gets

2218  rows processed

使用原表的形式,强制使用这个复合索引,但还是走了和上面完全一样的执行计划.

trusted形式呢?按说不用设置成这个的,病急乱投医吧!

SQL> alter session set query_rewrite_integrity=trusted;

Session altered.

SQL> select /*+ index(v_diy_userlog index2_v_diy_userlog) */username,max(virt_inputdate) last_inputdate

2  from v_diy_userlog

3  where username is not null and virt_inputdate is not null

4  group by username;

2218 rows selected.

SQL> /

2218 rows selected.

Execution Plan

----------------------------------------------------------

------------------------------------------------------------------------------------------

| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)|

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                      |   245 |  2450 |    48   (3)|

|   1 |  HASH GROUP BY               |                      |   245 |  2450 |    48   (3)|

|   2 |   TABLE ACCESS BY INDEX ROWID| DIY_USERLOG          |   259 |  2590 |    47   (0)|

|   3 |    INDEX FULL SCAN           | INDEX2_V_DIY_USERLOG |     3 |       |    45   (0)|

------------------------------------------------------------------------------------------

Note

-----

- 'PLAN_TABLE' is old version

Statistics

----------------------------------------------------------

16705  consistent gets

2218  rows processed

SQL> select /*+ index(diy_userlog index2_v_diy_userlog) */username,max(case when username is not null then inputdate else null end) last_inputdate

2  from diy_userlog

3  where username is not null and case when username is not null then inputdate else null end is not null

4  group by username;

2218 rows selected.

SQL> /

2218 rows selected.

Execution Plan

----------------------------------------------------------

------------------------------------------------------------------------------------------

| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)|

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                      |   245 |  2450 |    48   (3)|

|   1 |  HASH GROUP BY               |                      |   245 |  2450 |    48   (3)|

|   2 |   TABLE ACCESS BY INDEX ROWID| DIY_USERLOG          |   259 |  2590 |    47   (0)|

|   3 |    INDEX FULL SCAN           | INDEX2_V_DIY_USERLOG |     3 |       |    45   (0)|

------------------------------------------------------------------------------------------

Note

-----

- 'PLAN_TABLE' is old version

Statistics

----------------------------------------------------------

16705  consistent gets

2218  rows processed

还是不行呀!

未使用提示时,oracle不使用这个索引可能是因为使用这个索引代价更大(当然从cost看不是这样),明显,它的逻辑读很高.

可问题是:我通过提示强制使用这个索引的时候,它为什么要走索引全扫描,而不是索引快速全扫描呢,特别是它为什么要回访表呢?

这里要使用的所有列我这个复合索引里都有呀!

如果从产品的角度来讲,我觉得这里的username为空的记录行应该是垃圾记录,应该删除掉的.

但如果这些记录确实是需要保留的,

按现在的情况来讲,我这里的解决方案问题出在哪里呢?

或者说这里有什么其他的好的解决方案吗?

[/PHP]

Shepherd Young
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Oracle 复合索引
banlindong2674的博客
04-15 323
内容参考自http://yijiangyanyu.iteye.com/blog/1677694这篇文章，加上自己的理解。 复合索引，是指在一张表的多个字段上，复合建立一个索引，属于一个数据库对象。 （我觉得复合索引存在的意义是为了解决一张表需要多个字段去进行where条件的筛选，但是Oracle在扫描一张表的时候只能同时访问一个index，最优必然是主键索引，效率最高，但是有时候主键...
oracle函数大全
11-30
oracle所有函数,包括SQL中的单记录函数,复合函数等
oracle 复合函数,BUAA-OO-表达式解析与求导
weixin_42433470的博客
04-16 236
BUAA-OO-表达式解析与求导解析按照常规，解析这一部分我们分为词法分析与语法分析。当然由于待解析的字符串较简单，词法分析器和语法分析器不必单独实现。词法分析器按照常规，我们先手写一个词法分析器，而不使用正则表达式。词法分析器：读取字符流，产生标记流。它聚合字符形成单词，并应用一组规则来判断每个单词在源语言中是否合法，如果合法则为其分配一个语法范畴，产生一个标记。我们的词法分析器行为如下：如果 ...
ORACLE复合索引使用 注意事项
走马观花
12-22 1328
http://blog.sina.com.cn/s/blog_456c25570100i3za.html 复合索引的第一列一定要出现在WHERE子句中。 一.like "%_" 百分号在前.     二.WHERE中单独引用复合索引里第一位置的索引列，并且SELECT、GROUP BY等子句中包含非复合索引列 例句： 用不到索引 SELECT          CORP.BATCHC
ORACLE 中的复合变量的使用
微风
05-05 1102
ORACLE 中的复合变量的使用 定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同，这时可以使用%TYPE。 使用%TYPE特性的优点在于： l 所引用的数据库列的数据类型可以不必知道； l 所引用的数据库列的数据类型可以实时改变。
Oracle复合索引与空值的索引使用问题小结
09-09
如果A列没有空值，但B列允许空值，那么在计算如`min(B)`这样的聚合函数时，Oracle可能不会使用这个复合索引。原因是，由于B列可以为空，索引中可能不存在某些记录的B值，而计算最小值时必须检查所有记录以确认是否有...
ORACLE分析函数1.ppt
最新发布
11-12
为了进一步优化，可以考虑为相关的列（如owner和trunc(created,'dd')）创建复合索引，这样不仅可以加速子查询的执行，还能减少排序的需求。建立合适的索引后，原始SQL的逻辑读/COST比值将进一步降低，如案例中的111/...
索引优化原则及Oracle中索引总结
12-14
　· where语句中不得不对查询列采用函数查询，如upper函数，好建立相应函数索引； 　· 在SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引 　· 用于联接的列（主健/外健）上建立索引； 　· 在经常存取...
oracle索引介绍(图文详解)
12-16
本文将深入探讨Oracle索引的类型、结构以及如何创建和使用。 首先，让我们来了解索引的逻辑分类。逻辑分类主要涉及索引的构建方式： 1. 单列索引（Single Column Index）：这种索引针对表中的单一列创建。例如，在...
函数索引使用
04-06
**示例1**：基于多个列上的函数创建复合函数索引。 ```sql SQL> CREATE INDEX example_ndx ON myexample(mycola, UPPER(mycolb), mycolc); SQL> SELECT mycolc FROM myexample WHERE mycola = 55 AND UPPER(mycolb) ...
oracle中复合变量的用法
龙神add-走向数据架构师的路
06-29 1258
1.复合变量例子 (1)PL/SQL记录 declare type emp_record_type  is record( name test.ename%type, salary test.sal%type, title test.job%type ); emp_record emp_record_type; begin select ename,sal,job into
mysql复合函数索引_mysql高级
weixin_39665507的博客
01-28 525
1.1、启动MySQL服务器service MySQL start#启动服务器service mysql stop #关闭服务器1.2、登录mysqlmysql -u root -p #登录root用户 然后输入密码，cat /root/.mysql_secret#查看随机生成的密码进入mysql后输入：set password =password("新密码")#设置新的密码设置远程访问：gran...
复合索引与函数索引优化一例
cqrw65623的博客
08-25 192
复合索引与函数索引优化一例 导语：业务上有很多应用需求都是date类型转换成char类型进行比较求值。这样的应用，使用索引往往就要应用函数索引，本文讲一例函数索引的应用。 SELECT MAX(tt...
oracle数据库常用的语法与复合函数
jingke_1524的博客
01-19 729
oracle查用到一些复合函数以及一些常用的方法来快速查询数据，以下是我收集的一下查用方法，推荐给大家： 1、 当分组之后，针对某一属性值进行合并并以逗号进行分割： （1）所有版本都可使用： 合并数据：使用wm_concat(column)函数进行数据合并，以分组的形式将同一人的权限进行合并展示在一个属性值中，结果为集合形式clob； select u_id, wmsys.wm_concat(goods) goods_sum from shopping group by u_id 切割数据：合并数据后将
Oracle 通过复合索引提高查询性能的一个真实客户例子
chisky的专栏
04-19 2721
这是一个真实的应用,客户报了一个EBS的performance bug, 在Invoice form里,选择了一个客户后,再打开账单地址(location)的LOV,花了20分钟出来了3条地址数据. 分析了客户上传的trc文件(转成prf),发现查询是类似这样的: select t1.customer_id, t1.customer_name, t2.location ...
oracle case when索引 空值索引 位图索引
u010653311的专栏
07-19 2400
T1表里面有200W条数据，其中ID=2只有10条记录，查询ID=2时就可以用到case when索引。 建索引可以这样： CREATE INDEX I_1 ON T1(CASE WHEN ID>1 THEN ID ELSE NULL END);   或者类似CASE ID WHEN 2 THEN 2...或者不需要大于小于，可以用DECODE 查询的SQL也要改： SELECT * F
oracle索引分析、普通索引、函数索引、case when
qq_39126966的博客
08-10 869
索引查询性能分析 没有建立索引、1000w条数据，全表扫描（full）、 执行时间：第一次3s 第二次2s 第一次执行 Sql执行计划 建立普通索引 1000w条数据 Create index status_index on epcc_paymsg (prcess_status); 全表扫描 执行时间： 第一次 2s 第二次 2s 第一次执行 Sql执行计划 建立函数索引 Create index status_index on e...
复合索引
weixin_34051201的博客
08-02 321
复合索引 索引可以包含一个、两个或更多个列。两个或更多个列上的索引被称作复合索引。例如，以下语句创建一个具有两列的复合索引： CREATE INDEX name ON employee (emp_lname, emp_fname) 如果第一列不能单独提供较高的选择性，复合索引将会非常有用。例如，当许多雇员具有相同的姓氏时，emp_lname 和 emp_fname 上的复合索引非常...
Oracle数据库常用函数解析：ASCII, CHR, CONCAT等
"Oracle数据库中的110个常用函数" 在Oracle数据库中，有许多内置的函数用于处理和操作数据。以下是一些常见的函数，它们对于数据库查询和数据处理至关重要： 1. ASCII 函数 ASCII函数返回一个字符对应的十进制...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值