一种优化的手段 (gather_plan_statistics + cardinality)

zhaosj1726 · 发表于 2010-3-17 17:09

其实,许多人已经提到过这个东西了,我这里只是举一个简单的例子演示一遍具体的流程而已

比如说优化这个语句:
SELECT MAX(P.PAGEVIEW)
  FROM PRODUCT P, CATALOGRELATEPRODUCT CATAP
WHERE CATAP.CATALOGID = 291
AND P.ID = CATAP.PRODUCTID
AND PUBLISHSTATUS = 3;
我收集运行时的统计信息:
SELECT /*+ gather_plan_statistics ZHAOSJ1*/max(P.PAGEVIEW)
  FROM PRODUCT P, CATALOGRELATEPRODUCT CATAP
WHERE CATAP.CATALOGID = 291
AND P.ID = CATAP.PRODUCTID
AND PUBLISHSTATUS = 3;

实际的运行这个SQL语句,gather_plan_statistics是收集运行时的统计信息的提示,ZHAOSJ1 就是一个普通的注释,是为了唯一的标识这个游标的.

SQL> SELECT SQL_ID,CHILD_NUMBER FROM V$SQL  WHERE SQL_TEXT LIKE '%ZHAOSJ1%' AND SQL_TEXT NOT LIKE '%V$SQL%';

SQL_ID       CHILD_NUMBER
------------- ------------
79gcyuucwuzwg          0

查找刚才的游标.
SET PAGESIZE 200;
SET LINESIZE 200;
COL PLAN_TABLE_OUTPUT FOR A195;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('79gcyuucwuzwg',0,'ALL IOSTATS LAST'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  79gcyuucwuzwg, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics ZHAOSJ1*/max(P.PAGEVIEW) FROM PRODUCT P, CATALOGRELATEPRODUCT CATAP  WHERE CATAP.CATALOGID =
291 AND P.ID = CATAP.PRODUCTID AND PUBLISHSTATUS = 3
Plan hash value: 745285829
--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation       | Name                      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------
| 1 |  SORT AGGREGATE |                            |    1 |    1 | 19 |          |       |    1 |00:00:00.08 | 17210 |
| 2 | NESTED LOOPS    |                            |    1 |    50 | 950 | 27 (0)| 00:00:01 | 8557 |00:00:00.08 | 17210 |
|*  3 | INDEX RANGE SCAN| INDEX2_CATALOGRELATEPRODUCT |    1 |    50 | 400 |    2 (0)| 00:00:01 | 8567 |00:00:00.01 |    30 |
|*  4 | INDEX RANGE SCAN| INDEX2_PRODUCT             | 8567 |    1 | 11 |    1 (0)| 00:00:01 | 8557 |00:00:00.06 | 17180 |
--------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 - SEL$1 / [email=CATAP@SEL$1]CATAP@SEL$1[/email]
4 - SEL$1 / [email=P@SEL$1]P@SEL$1[/email]
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CATAP"."CATALOGID"=291)
4 - access("P"."ID"="CATAP"."PRODUCTID" AND "PUBLISHSTATUS"=3)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) MAX("P"."PAGEVIEW")[22]
2 - "P"."PAGEVIEW"[NUMBER,22]
3 - "CATAP"."PRODUCTID"[NUMBER,22]
4 - "P"."PAGEVIEW"[NUMBER,22]

这里显示:这个语句总的逻辑IO是:17210(buffers 是实际的逻辑IO数量,这里是累计值,包括子操作的值)
starts 是对应的动作执行的次数
E-ROWS 是优化器估算这一步返回的数据行数
A-Rows  是这一步实际返回的数据行数

明显INDEX RANGE SCAN| INDEX2_CATALOGRELATEPRODUCT 这一步估算返回50行.但实际返回了8567行
因为估算返回50行,所以估算INDEX RANGE SCAN| INDEX2_PRODUCT 这一步会执行50次,但实际它执行了8567次.
显然估算与实际执行上存在着巨大的差异.

那优化器估算INDEX RANGE SCAN| INDEX2_CATALOGRELATEPRODUCT 这一步返回8567行的话,执行计划会是什么呢?实际的执行效果会怎样呢?
使用cardinality(t n) 提示不就可以了吗?!

SQL> SELECT /*+ CARDINALITY(CATAP 8500) gather_plan_statistics ZHAOSJ6*/max(P.PAGEVIEW)
  2 FROM PRODUCT P, CATALOGRELATEPRODUCT CATAP
  3 WHERE CATAP.CATALOGID = 291
  4    AND P.ID = CATAP.PRODUCTID
  5    AND PUBLISHSTATUS = 3;

MAX(P.PAGEVIEW)
---------------
      18524

SQL> SELECT SQL_ID,CHILD_NUMBER FROM V$SQL  WHERE SQL_TEXT LIKE '%ZHAOSJ6%' AND SQL_TEXT NOT LIKE '%V$SQL%';

SQL_ID       CHILD_NUMBER
------------- ------------
f9nj2kxhphm82          0

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('f9nj2kxhphm82',0,'ALL IOSTATS LAST'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  f9nj2kxhphm82, child number 0
-------------------------------------
SELECT /*+ CARDINALITY(CATAP 8500) gather_plan_statistics ZHAOSJ6*/max(P.PAGEVIEW) FROM PRODUCT P, CATALOGRELATEPRODUCT CATAP  WHERE
CATAP.CATALOGID = 291 AND P.ID = CATAP.PRODUCTID AND PUBLISHSTATUS = 3
Plan hash value: 2173417495
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------------
| 1 |  SORT AGGREGATE       |                            |    1 |    1 | 19 |          |       |    1 |00:00:00.09 | 1413 |
|*  2 | HASH JOIN          |                            |    1 | 8511 | 157K| 246 (3)| 00:00:03 | 8557 |00:00:00.09 | 1413 |
|*  3 | INDEX RANGE SCAN | INDEX2_CATALOGRELATEPRODUCT |    1 | 8500 | 68000 |    2 (0)| 00:00:01 | 8567 |00:00:00.01 |    30 |
|*  4 | INDEX FAST FULL SCAN| INDEX2_PRODUCT             |    1 | 236K|  2535K| 242 (2)| 00:00:03 | 236K|00:00:00.01 | 1383 |
------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 - SEL$1 / [email=CATAP@SEL$1]CATAP@SEL$1[/email]
4 - SEL$1 / [email=P@SEL$1]P@SEL$1[/email]
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."ID"="CATAP"."PRODUCTID")
3 - access("CATAP"."CATALOGID"=291)
4 - filter("PUBLISHSTATUS"=3)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) MAX("P"."PAGEVIEW")[22]
2 - (#keys=1) "P"."PAGEVIEW"[NUMBER,22]
3 - "CATAP"."PRODUCTID"[NUMBER,22]
4 - "P"."ID"[NUMBER,22], "P"."PAGEVIEW"[NUMBER,22]

明显hash join后估算的行数8511与实际返回的行数8557的差距已经很小了.
执行计划改变了.而且逻辑IO从1.7W下降到了1400.逻辑IO的下降还是很明显的

下面要做的就是通过使用提示使得它可以走这个执行计划:
SELECT /*+ use_hash(p catap)*/max(P.PAGEVIEW)
  FROM PRODUCT P, CATALOGRELATEPRODUCT CATAP
WHERE CATAP.CATALOGID = 291
AND P.ID = CATAP.PRODUCTID
AND PUBLISHSTATUS = 3;

但,事实上你要做的是查找一下优化器为什么估算返回的行数,估算错了呢?
SQL> select a.num_distinct,a.num_buckets,a.num_nulls,a.histogram,b.num_rows,round(b.num_rows/a.num_distinct) rows_per_key
  2  from user_tab_columns a,user_tables b where a.table_name='CATALOGRELATEPRODUCT' and a.column_name='CATALOGID' and b.table_name='CATALOGRELATEPRODUCT';

NUM_DISTINCT NUM_BUCKETS  NUM_NULLS HISTOGRAM       NUM_ROWS ROWS_PER_KEY
------------ ----------- ---------- --------------- ---------- ------------
   17943          1       0 NONE             904362          50
明显,这个列上并没有收集柱状图统计信息.所以对于min~max内的任意给定值,它估算的返回行数都是:num_rows/num_distinct =50
对于CATAP.CATALOGID = 291,估算确实不够准确.但问题在于两点:
1.应用程序中是使用绑定变量的.
2.对于典型输入值来说,确实返回不了几行数据(nl的执行计划确实是好的,不收集柱状图统计信息时,确实走NL了).291其实并不是一个典型输入值(对于这个非典型输入值来说,hash join确实是一个好的执行计划,而nl不是).所以如果收集了柱状图统计信息的话,每次硬分析的时候,都会peeking,这带有很大的随机性,如果peeking的刚好是291这个非典型输入值,采用hash join的话,对于一般的输入值来说,性能上其实是不好的.所以,其实就不应该收集柱状图统计信息:这样虽然对于极少数的输入值来说,执行计划并不好,但对于绝大多数的输入值来说,执行计划是很好的.对于极少数的非典型输入值来说,如果使用字面值的话,你可以使用use_hash之类的提示来纠正它的执行计划.

sundog315 · 发表于 2010-3-17 22:10

帮顶，学习。

ZALBB · 发表于 2010-3-18 00:20

你的方法：关键是提示：gather_plan_statistics，还是查询SQL执行过程统计信息的语句？
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('79gcyuucwuzwg',0,'ALL IOSTATS LAST'));

在我看来，是后者．
使用后者，知道实际运行的统计信息后，再用CARDINALITY来指定预估值，从而获取最佳执行计划，最后先前执行计划不佳的原因．
因而从统计信息入手，来评估改进执行计划，而不是使用提示．

〇〇 · 发表于 2010-3-18 09:11

记录收藏

zergduan · 发表于 2010-3-18 09:40

http://structureddata.org/2007/1 ... ad-execution-plans/
Where Cardinality Can Go Wrong

There are several common scenarios that can lead to inaccurate cardinality estimates. Some of those on the list are:

Data skew: Is the NDV inaccurate due to data skew and a poor dbms_stats sample?
Data correlation: Are two or more predicates related to each other?
Out-of-range values: Is the predicate within the range of known values?
Use of functions in predicates: Is the 5% cardinality guess for functions accurate?
Stats gathering strategies: Is your stats gathering strategy yielding representative stats?
Some possible solutions to these issues are:

Data skew: Choose a sample size that yields accurate NDV. Use DBMS_STATS.AUTO_SAMPLE_SIZE in 11g.
Data correlation: Use Extended Stats in 11g. If <= 10.2.0.3 use a CARDINALITY hint if possible.
Out-of-range values: Gather or manually set the statistics.
Use of functions in predicates: Use a CARDINALITY hint where possible.
Stats gathering strategies: Use AUTO_SAMPLE_SIZE. Adjust only where necessary. Be mindful of tables with skewed data.

OoNiceDream · 发表于 2010-3-18 10:17

我理解就是用来评估统计信息是否准确用的。

zhaosj1726 · 发表于 2010-3-18 14:33

其实我觉得这里最主要还是查看优化器估算值和实际运行值之间是否存在着巨大的差距,如估算返回行数和实际返回行数之间,估算字节数和实际字节数之间,特别是前两者之间是否存在着巨大的差距，如果是的话,就应用着手调查为什么会出现这样的问题了,是优化器统计信息陈旧还是收集优化器统计信息的方法不对,比如说该收集柱状图统计信息的时候没有收集,或者是不该收集的时候收集了柱状图统计信息等等!
其次是查看逻辑读这样一些运行时的统计信息,使用它们作为基准值来比较哪一个执行计划是更好的.我觉得这里还有一个很重要的东西就是:A-Time,它是一个累积值,你通过查看它,可以知道到底哪
一步操作耗费了绝大多数的时间,从而从这里寻求突破口(10046跟踪文件也提供这些信息,但它不提供优化器估算值).比如说一个执行计划是hash join之后排序,而后取前N行,你发现是排序这里耗费了绝大多数的时间(这里主要还是CPU时间),你就可以着手解决这个问题,是否可以先排序,再取前N行,而后再连接呢?因为连接之后,更大的结果集,更多的引用字段,显然排序要耗费更多的CPU,另外,它需要更多的工作区,越容易导致临时表空间的读写.而先排序,取前N行后再连接可以避免CPU的过多消耗(当然,你也应该看看是否可以通过索引尽量的避免排序操作).如果不能这样的话,比如说因为order by字段中包括了多个表的字段,不能采取我前面说的方法,而必须连接后排序的话,同时你这里select的字段很多的话,那么是否可以先只select出rowid,再排序取前N行后,再关联表取其它字段呢?这样对一个有更少引用字段的结果集进行排序,从而避免CPU的过度消耗.
至于cardinality的用途,既然是优化器统计信息不准确,那如果优化器统计信息是准确的,结果会如何呢?执行计划会变好吗?在你正确的重新收集优化器统计信息前,使用cardinality提示给了你一种不用重新收集优化器统计信息却可以知道如果收集准确了效果是否会变好的一种方法。其实,我这里写cardinality提示,更多的是想展示一种纠正优化器统计信息错误的一种方式.比如说,就像zergduan所说的,在有些版本里,优化器在估算返回的行数时存在一些个缺陷,这其实不是咱们在收集优化器统计信息时出现的问题,而是优化器本身存在的缺陷,这时候如何解决呢?比如说在10G中,where constellation=\'天蝎座\' and month=6,优化器会认为它的选择性是1/144,但明显这是错误的,在11G引入扩展的统计信息之前,我们好像只能通过cardinality提示来纠正优化器的这个错误. 再比如说在10.2.0.1中,我使用层次查询的时候,oracle优化器在估算返回的行数时老是有问题,本来就是返回几行数据的事,它老是认为会返回几万行数据,因为这个问题,再连接其它表的时候,它的连接顺序就存在问题,导致整个的执行计划很差,我知道是估算行数上出现问题了,可我无论怎么收集优化器统计信息,问题都解决不了(当然,也许还是我收集优化器统计信息的方法有问题,我觉得在10.2.0.4中这个问题好像得到了解决),于是我只能通过ordered等提示指定表的连接顺序和连接方法,其实使用这个提示是最简单的,你不需要指定其它任何的提示了,因为它的本质问题就是优化器在估算层次查询返回的行数上出现差错导致的,你只需要纠正这一个问题就可以了. 另外大家有时候需要用小表来模拟大表的情况,一般的方法就是手工修改优化器统计信息,其实我觉得这个提示也是一种方法.另外应用中可能存在这样一个问题,就是一个表的数据特征几乎没有变化,但数据行数变化却很大,这次查询的时候有几十万行,而下次查询的时候可能只有几行数据了,很多时候显然它们的执行计划是不应该一样的,但定期收集优化器统计信息的方法显然解决不了这个问题,这时就可以使用这个提示来帮助解决这个问题.其实物化视图快速刷新的时候就存在这样的问题,这次刷新的时候,物化视图日志中有几十万行数据,而下次刷新的时候只有几行数据了,oracle就是使用这个提示来解决这个问题的.

[ 本帖最后由 zhaosj1726 于 2010-3-19 14:20 编辑 ]

zhaosj1726 · 发表于 2010-3-18 18:53

你得承认,oracle的优化器还是存在一些个问题的.但更多时候,优化器选择了糟糕的执行计划,其实都是我们自己造成的.我觉得主要存在以下几点因素:
   1.优化器统计信息陈旧或者是我们收集优化器统计信息的方法不对.比如说我上面提到的该收集柱状图统计信息的时候没有收集,或者是不该收集的时候收集了柱状图统计信息等等!

   2.缺少必要的约束信息.你只把数据堆放在那里,不告诉ORACLE其它任何的信息,你就指望它给你选择一个最优的执行计划?!它毕竟是人而不是神.当然它也越来越神化了.比如说10G的时候,gather_stats_job会自动为你收集陈旧的统计信息(即使你以前没有任何的收集统计信息的JOB.当然你可以根据自己的实际情况停掉这个JOB,只根据实际需要手工的收集).optimizer_dynamic_sampling的默认值也由1变成了2,这样对于刚导入的数据没有收集优化器统计信息的,绝大多数时候它会动态采样,而不再使用内部的默认值了.当然我觉得也是因为这样一些个改进,10g的时候,optimizer_mode的默认值由choose变成了all_rows.在优化器统计信息收集这一块儿,它主动的替我们做了很多的工作(虽然说还是存在一些个问题的),但在约束这一块儿,它却不能替我们做任何的决定.一个有1000W行数据的表的字段A上,在收集统计信息的时候,发现它不存在任何的NULL值,但你的表定义中这个字段是NULL的,那么它就必须按这个字段是可以为NULL的去处理,而不能替我们做出这样的决定:这个字段是NOT NULL的,它绝不会这样做的.但假如说实际上这个字段确实是不会为NULL的,你却没有NOT NULL的约束限制,那么有些可选的执行计划,ORACLE也只能放弃掉了:比如说因为没有NOT NULL的约束限制,有些本可以通过索引扫描避免的排序操作也只能放弃,而选择排序操作了.比如说NOT IN操作,本来可以ANTI JOIN的,它也只能放弃,而选择有些时候并不好的FILTER操作了,所以很多时候,我们在遇到not in的时候,使用not exists,外连接+判断为NULL这样一些个替代方案,not in被放弃了,有时候人们也纳闷:为什么not in这种很符合人的思维习惯的标准SQL,oracle却做的这么不好呢?我们没有意识到,因为缺少必要的约束限制,oracle只能放弃一些个转换,因为它们不是等价的,但实际上它们是等价的,因为这样的约束限制其实就数据本身来说是存在的,只是我们没有通过表定义告诉数据库而已.

   3.oracle有时候还不足够智能(虽说它已经很智能了),有些我们的思维习惯它还理解不了,所以我们需要去理解它的思维习惯,去适应它的这种思维习惯,尽量按照它可以理解的方式去写SQL.比如说这样一个例子:

product表中id是主键,productaverpoint表中productid也是主键,product表的pageview字段是可以为空的,pageview上有一个单列索引

select * from
(
select tt.*,rownum n from
                        (
                              select p.image1,p.id,p.productname,p.pageview,p.brandname,p.brandid,
                                    nvl(a.appearpoint,0) as appearpoint,pcount as pcount
                              from product p
                              left join productaverpoint a on a.productid=p.id
                              left join (select productid, count(productid) pcount from productpic group by productid) pic on p.id = pic.productid
                              where p.pageview is not null
                              order by p.pageview desc
                        ) tt
)
where n>0 and n<=15;

--------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |                | 254K| 75M|    | 20585 (1)| 00:04:08 |
|*  1 |  VIEW                      |                | 254K| 75M|    | 20585 (1)| 00:04:08 |
| 2 | COUNT                   |                |    |    |    |          |       |
| 3 | VIEW                   |                | 254K| 71M|    | 20585 (1)| 00:04:08 |
| 4 |    SORT ORDER BY          |                | 254K| 36M| 77M| 20585 (1)| 00:04:08 |
|*  5 |    HASH JOIN RIGHT OUTER  |                | 254K| 36M|  2576K| 12201 (2)| 00:02:27 |
| 6 |    VIEW                |                | 90779 |  1507K|    |  2298 (4)| 00:00:28 |
| 7 |       HASH GROUP BY       |                | 90779 | 354K| 15M|  2298 (4)| 00:00:28 |
| 8 |       INDEX FAST FULL SCAN| IDX_P_PIC_PID |  1225K|  4789K|    | 832 (2)| 00:00:10 |
|*  9 |    HASH JOIN RIGHT OUTER |                | 254K| 32M|  4448K|  8038 (1)| 00:01:37 |
|  10 |       TABLE ACCESS FULL | PRODUCTAVERPOINT | 252K|  1480K|    | 204 (2)| 00:00:03 |
|* 11 |       TABLE ACCESS FULL | PRODUCT       | 254K| 30M|    |  5950 (1)| 00:01:12 |
--------------------------------------------------------------------------------------------------------

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

product 用来关联的字段ID是主键,productaverpoint用来关联的字段productid是主键,
(select productid, count(productid) pcount from productpic group by productid) pic 对视图pic来说,productid又可以当作主键来使用.
而且又都是左外连接,order by p.pageview desc只是根据一个表的字段来进行排序,而且是取前n行数据,
所以,你当然可以像下面这样改写:

select p.image1,p.id,p.productname,p.pageview,p.brandname,p.brandid,
   nvl(a.appearpoint, 0) as appearpoint,
   (select count(productid) pcount from productpic where productid=p.id) pcount
from
(
select prowid,n
from
(
   select prowid,rownum n from
   (
      select rowid prowid from product p where p.pageview is not null order by p.pageview desc
   )
   where rownum<=15
) where n>0
) ptemp,product p,productaverpoint a
where ptemp.prowid=p.rowid and p.id=a.productid(+)
order by n;

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                | Rows  | Bytes | Cost (%CPU)| Time    |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                |                      | 15 |  2535 | 520 (1)| 00:00:07 |
| 1 |  SORT AGGREGATE                |                      |    1 |    4 |          |       |
|*  2 | INDEX RANGE SCAN             | IDX_P_PIC_PID       | 14 | 56 |    2 (0)| 00:00:01 |
| 3 |  SORT ORDER BY                |                      | 15 |  2535 | 520 (1)| 00:00:07 |
| 4 | NESTED LOOPS OUTER          |                      | 15 |  2535 | 519 (1)| 00:00:07 |
| 5 | NESTED LOOPS                |                      | 15 |  2445 | 512 (1)| 00:00:07 |
|*  6 |    VIEW                      |                      | 15 | 375 | 497 (1)| 00:00:06 |
|*  7 |    COUNT STOPKEY             |                      |    |    |          |       |
| 8 |    VIEW                      |                      | 254K|  2982K| 497 (1)| 00:00:06 |
|*  9 |       INDEX FULL SCAN DESCENDING| INDEX_PRODUCT_PAGEVIEW | 254K|  3728K| 497 (1)| 00:00:06 |
|  10 |    TABLE ACCESS BY USER ROWID | PRODUCT             |    1 | 138 |    1 (0)| 00:00:01 |
|  11 | TABLE ACCESS BY INDEX ROWID | PRODUCTAVERPOINT    |    1 |    6 |    1 (0)| 00:00:01 |
|* 12 |    INDEX UNIQUE SCAN          | PK_PRODUCTAVERPOINT |    1 |    |    1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

但问题在于,我必要的约束都是存在的,其实完全可以product表按pageview排序后取前n行,然后再和其他表关联的,但为什么oracle没有这样做呢?
其实对于第一个SQL语句稍微改写一下就可以:
select * from
(
select tt.*,rownum n from
                        (
                              select p.image1,
                                    p.id,p.productname,p.pageview,p.brandname,p.brandid,
                                    nvl(a.appearpoint,0) as appearpoint,
                                    (select count(productid) pcount from productpic where productid=p.id) pcount
                              from product p
                              left join productaverpoint a on a.productid=p.id
                              where p.pageview is not null
                              order by p.pageview desc
                        ) tt
                        where rownum<=15
)
where n>0;

----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                | Rows  | Bytes | Cost (%CPU)| Time    |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |                      | 15 |  4635 | 79 (0)| 00:00:01 |
| 1 |  SORT AGGREGATE                |                      |    1 |    4 |          |       |
|*  2 | INDEX RANGE SCAN             | IDX_P_PIC_PID       | 14 | 56 |    2 (0)| 00:00:01 |
|*  3 |  VIEW                         |                      | 15 |  4635 | 79 (0)| 00:00:01 |
|*  4 | COUNT STOPKEY                |                      |    |    |          |       |
| 5 | VIEW                      |                      | 15 |  4440 | 79 (0)| 00:00:01 |
| 6 |    NESTED LOOPS OUTER       |                      | 15 |  1980 | 79 (0)| 00:00:01 |
| 7 |    TABLE ACCESS BY INDEX ROWID| PRODUCT             | 254K| 30M| 72 (0)| 00:00:01 |
|*  8 |    INDEX FULL SCAN DESCENDING| INDEX_PRODUCT_PAGEVIEW | 15 |    |    2 (0)| 00:00:01 |
| 9 |    TABLE ACCESS BY INDEX ROWID| PRODUCTAVERPOINT    |    1 |    6 |    1 (0)| 00:00:01 |
|* 10 |    INDEX UNIQUE SCAN       | PK_PRODUCTAVERPOINT |    1 |    |    1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

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

这个执行计划和第二个语句的执行计划看起来有些不太一样,其实你从运行的统计信息来看,它们的执行计划是完全相同的.但它和第一个SQL语句又有什么不一样的呢?
其实我只是把where n>0 and n<=15 改写了一下,让oracle可以进行stopkey操作了而已.

ignu · 发表于 2010-6-30 11:29

讲得很好啊

kevy_small · 发表于 2013-11-19 15:30

楼主写的不错，如果排版更好一点，就好了

一种优化的手段 (gather_plan_statistics + cardinality)

浏览过的版块