17,086
社区成员
发帖
与我相关
我的任务
分享oracle索引选择问题
这张表的数据量在2000w左右.分区键使用的是SCAN_MONTH.查询的时候出现下面两种情况.
情况1:
SELECT T.COUNTER_NSN_ID FROM COUNTER_NSN_TABLE T WHERE 1=1 and t.SCAN_MONTH = '01' and t.COUNTER_NO = 'LY1A140616957' and t.SCAN_DATE >= '2017-01-01' and t.SCAN_DATE <= '2017-01-31' ORDER BY T.SCAN_MONTH
这种查询速度很快,解释计划如下:
统计上述语句总数的时候:
SELECT COUNT(*) FROM (SELECT T.COUNTER_NSN_ID FROM COUNTER_NSN_TABLE T WHERE 1=1 and t.SCAN_MONTH = '01' and t.COUNTER_NO = 'LY1A140616792' and t.SCAN_DATE >= '2017-01-01' and t.SCAN_DATE <= '2017-01-31' ORDER BY T.SCAN_MONTH)
这时查询速度很慢.30s左右:
情况2:
SELECT T.COUNTER_NSN_ID FROM COUNTER_NSN_TABLE T WHERE 1=1 and t.COUNTER_NO = 'LY1A140616810' and t.SCAN_DATE >= '2017-01-01' and t.SCAN_DATE <= '2017-01-31' ORDER BY T.SCAN_MONTH
将where后面条件,t.scan_month去掉以后.查询速度就慢了下来.大概50s左右:
SELECT COUNT(*) FROM (SELECT T.COUNTER_NSN_ID FROM COUNTER_NSN_TABLE T WHERE 1=1 and t.COUNTER_NO = 'LY1A140616810' and t.SCAN_DATE >= '2017-01-01' and t.SCAN_DATE <= '2017-01-31' ORDER BY T.SCAN_MONTH)
这种去掉scan_month条件的select count却快了很多:
想请教下各路大神,这里面的原理到底是什么.
还有疑惑就是我挑选了情况1中的查询语句,情况2中的统计数量语句结合使用.
在分页的时候将数据存入LIST的时候很慢.是不是因为前面速度较慢的查询,已经把数据存入了内存中?
求各路大神指点迷津啊...............
...全文
请发表友善的回复…
发表回复
xsjCoding 2018-01-03
- 打赏
- 举报
还没有.我准备在本地造7kw的数据再试试.同事那边有事目前没法配合测了.
拖的太久就先结贴了.
minsic78 2018-01-03
- 打赏
- 举报
楼主，问题解决了吗？
minsic78 2017-12-26
- 打赏
- 举报
补充个dbms_xplan.display_cursor的例子：
SQL> set pagesize 1000
SQL> set linesize 200
SQL> select /*+gather_plan_statistics*/count(*) from ttt;
COUNT(*)
----------
1643
SQL> select sql_id,sql_text from v$sql where sql_text ='select /*+gather_plan_statistics*/count(*) from ttt';
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2j6cbrxqxds4v
select /*+gather_plan_statistics*/count(*) from ttt
SQL> select * from table(dbms_xplan.display_cursor('2j6cbrxqxds4v',null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2j6cbrxqxds4v, child number 0
-------------------------------------
select /*+gather_plan_statistics*/count(*) from ttt
Plan hash value: 3818711063
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 11 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 11 |
| 2 | INDEX FAST FULL SCAN| I_ttt_ID | 1 | 1643 | 1643 |00:00:00.01 | 11 |
----------------------------------------------------------------------------------------------
14 rows selected.
SQL>minsic78 2017-12-26
- 打赏
- 举报
1、返表的问题：返表当然是很费劲的事情，返表与很多因素有关系，比如：数据量大小，比如索引的聚簇因子，数据量越大，聚簇因子越大，返表的性能会越糟糕；
2、分页SQL，逻辑上也许会类似游标，就是循环读取数据，但读的什么数据，从哪里读，要看你走什么样的执行计划，也是是TABLE FULL SCAN，也许是INDEX FULL SCAN等等～
3、第三个问题，很难解释，你确定数据量相差3倍甚至以上的情况下，逻辑读是一样的？用dbms_xplan,.display_cursor来取个更详细更清晰的文本上来看看。另外查下你这两套库上对应表的pctfree、pctused等存储参数
xsjCoding 2017-12-25
- 打赏
- 举报
大神..十分感激解答.
现在还有点小问题想请教一下.
我同事那边数据7000w,我这边仍然是2000w的数据.
问题1:返表是不是本身效率就很低?
问题2:后台查询使用的是分页.比如说我要查询第20000条至20015条数据.oracle是不是有一个游标要慢慢往后跑的.所以效率低.具体的原理是怎么样的.
问题3.同事那边的select count(*)速度有点慢.20s左右.我这边很快.1s左右.同样是hashjoin为啥差距这么大,统计信息也差不多.
具体情况如下.
同事的:
我的:
minsic78 2017-12-22
- 打赏
- 举报
你对这快慢的判断有点问题。
针对你的情况1：在plsql dev中执行sql，快慢很多时候是不准确的，因为没有返回所有结果集，可能导致真正执行的执行计划并非你F5显示的执行计划。你可以用sqlplus的autotrace，关闭屏幕输出结果集来查看sql执行要花多长时间，或者像toad这种工具，可以更方便地打开auto trace，在这种返回所有结果集的情况下，才是真正的SQL执行时间。如果情况1中的非统计sql返回所有数据，那么应该跟统计SQL的执行时间是差不太多的，当然结果集较大的话，会比统计要慢点，因为毕竟要返回更多的数据，而统计只要返回一个数字而已。
针对情况2：因为少了一个条件的过滤，数据量大了，而且也没法过滤分区，所以他需要处理所有的分区数据，而且，大数据量的返表会更糟糕——返表就是那个table access by local index rowid，而第二个统计sql，根本就没有返表，只是两条索引的hash join，可以看作是只有单个字段的两张小表的hash join，自然速度不会太慢。
HxH-404 2017-12-22
- 打赏
- 举报
无力帮顶
