一个问题 SELECT MIN(ID),MAX(ID) FROM TABLE 如何优化
好的现在来做个实验:
- SQL> select * from v$version where rownum<2;
- BANNER
- --------------------------------------------------------------------------------
- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
- SQL> create table test as select * from dba_objects;
- Table created.
- SQL> alter table test modify object_id not null;
- Table altered.
- <pre class="html" name="code"> </pre><pre class="html" name="code">SQL> create index i_object_id on test(object_id);
- Index created.</pre><pre class="html" name="code"> </pre><pre class="html" name="code">SQL> select max(object_id),min(object_id) from test;
- MAX(OBJECT_ID) MIN(OBJECT_ID)
- -------------- --------------
- 74644 2
- Elapsed: 00:00:00.34
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1751978921
- -------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 5 | 37 (0)| 00:00:01 |
- | 1 | SORT AGGREGATE | | 1 | 5 | | |
- | 2 | INDEX FAST FULL SCAN| I_OBJECT_ID | 72860 | 355K| 37 (0)| 00:00:01 |
- -------------------------------------------------------------------------------------
- Statistics
- ----------------------------------------------------------
- 1 recursive calls
- 0 db block gets
- 167 consistent gets
- 145 physical reads
- 0 redo size
- 501 bytes sent via SQL*Net to client
- 420 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 1 rows processed
- </pre><pre class="html" name="code">可以看到CBO选择了 INDEX FAST FULL SCAN,有145个物理读,167个逻辑读,现在改写SQL如下</pre><pre class="html" name="code"> </pre><pre class="html" name="code"><pre class="html" name="code">SQL> select (select min(object_id) min_id from test a),(select max(object_id) max_id from test b) from dual;
- (SELECTMIN(OBJECT_ID)MIN_IDFROMTESTA) (SELECTMAX(OBJECT_ID)MAX_IDFROMTESTB)
- ------------------------------------- -------------------------------------
- 2 74644
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 4224666897
- ------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 |
- | 1 | SORT AGGREGATE | | 1 | 5 | | |
- | 2 | INDEX FULL SCAN (MIN/MAX)| I_OBJECT_ID | 1 | 5 | 2 (0)| 00:00:01 |
- | 3 | SORT AGGREGATE | | 1 | 5 | | |
- | 4 | INDEX FULL SCAN (MIN/MAX)| I_OBJECT_ID | 1 | 5 | 2 (0)| 00:00:01 |
- | 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
- ------------------------------------------------------------------------------------------
- Statistics
- ----------------------------------------------------------
- 1 recursive calls
- 0 db block gets
- 4 consistent gets
- 0 physical reads
- 0 redo size
- 547 bytes sent via SQL*Net to client
- 420 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 1 rows processed</pre><pre class="html" name="code"> </pre><pre class="html" name="code">可以看到逻辑读降低为4,大家看看这个是不是最优化的方法?还有更进一步的方法优化吗?支付宝说这个案例很经典,可能是我水平太低了,只能想出这个方法
- <pre class="html" name="code"> </pre><pre class="html" name="code">----update,我能想到的另外一种方法,不过逻辑读也是4---------------------</pre><pre class="html" name="code"> </pre><pre class="html" name="code">SQL> select (select /*+ index_asc(test i_object_id) */ object_id from test where rownum=1) min
- ,(select /*+ index_desc(test i_object_id) */ object_id from test where rownum=1) max from dual; 2
- MIN MAX
- ---------- ----------
- 2 74644
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 91314419
- -------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 |
- |* 1 | COUNT STOPKEY | | | | | |
- | 2 | INDEX FULL SCAN | I_OBJECT_ID | 1 | 5 | 2 (0)| 00:00:01 |
- |* 3 | COUNT STOPKEY | | | | | |
- | 4 | INDEX FULL SCAN DESCENDING| I_OBJECT_ID | 1 | 5 | 2 (0)| 00:00:01 |
- | 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
- -------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 1 - filter(ROWNUM=1)
- 3 - filter(ROWNUM=1)
- Statistics
- ----------------------------------------------------------
- 1 recursive calls
- 0 db block gets
- 4 consistent gets
- 0 physical reads
- 0 redo size
- 479 bytes sent via SQL*Net to client
- 420 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 1 rows processed
- </pre><br></pre></pre>
转载:http://blog.csdn.net/robinson1988/article/details/6623773