10g中function index無法避免排序

棉花糖ONE發表於2009-11-18
SQL> alter session set nls_language=american;

Session altered.

SQL> create table test_sort(name,object_name) as select 'select',object_name from
  2  dba_objects;

Table created.

SQL> create index i_test_sort_1 on test_sort(substr(name,0,3),object_name);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'TEST_SORT');

PL/SQL procedure successfully completed.

SQL> set autot trace
SQL> select * from (select * from test_sort where substr(name,0,3)='sel' order by
  2  object_name) where rownum<11;

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4202652051

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

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

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

|   0 | SELECT STATEMENT        |           |    10 |   740 |       |   504   (3
)| 00:00:07 |

|*  1 |  COUNT STOPKEY          |           |       |       |       |
|          |

|   2 |   VIEW                  |           | 49902 |  3606K|       |   504   (3
)| 00:00:07 |

|*  3 |    SORT ORDER BY STOPKEY|           | 49902 |  1559K|  3928K|   504   (3
)| 00:00:07 |

|*  4 |     TABLE ACCESS FULL   | TEST_SORT | 49902 |  1559K|       |    60   (5
)| 00:00:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<11)
   3 - filter(ROWNUM<11)
   4 - filter(SUBSTR("NAME",0,3)='sel')


Statistics
----------------------------------------------------------
        139  recursive calls
          0  db block gets
        279  consistent gets
          0  physical reads
          0  redo size
        695  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
         10  rows processed

sql doesn't choose index ,so I use hint to force oracle choose index

SQL> select * from (select /*+ index(test_sort) */ * from test_sort where substr(name,0,3)='sel'
  2  order by object_name) where rownum<11;

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1978014138

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

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

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

|   0 | SELECT STATEMENT               |               |    10 |   740 |       |
22869   (1)| 00:04:35 |

|*  1 |  COUNT STOPKEY                 |               |       |       |       |
            |          |

|   2 |   VIEW                         |               | 49902 |  3606K|       |
22869   (1)| 00:04:35 |

|*  3 |    SORT ORDER BY STOPKEY       |               | 49902 |  1559K|  3928K|
22869   (1)| 00:04:35 |

|   4 |     TABLE ACCESS BY INDEX ROWID| TEST_SORT     | 49902 |  1559K|       |
22424   (1)| 00:04:30 |

|*  5 |      INDEX RANGE SCAN          | I_TEST_SORT_1 | 49902 |       |       |
   278   (1)| 00:00:04 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<11)
   3 - filter(ROWNUM<11)
   5 - access(SUBSTR("NAME",0,3)='sel')


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

From the explain ,the sql chooses the index,but SORT ORDER BY STOPKEY  doesn't avoid,so oracle
must read all rows satisfy substr(name,0,3)='sel' ,the I rewrite the sql as the following,we can see
that sort ORDER BY STOPKEY does't happen,so oracle return 10 rows fast.

SQL> select * from (select /*+ index(test_sort) */ * from test_sort where substr(name,0,3)='sel'
  2  order by substr(name,0,3),object_name) where rownum<11;

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4154852915

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

| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%C
PU)| Time     |

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

|   0 | SELECT STATEMENT              |               |    10 |   740 |     8
(0)| 00:00:01 |

|*  1 |  COUNT STOPKEY                |               |       |       |
   |          |

|   2 |   VIEW                        |               |    10 |   740 |     8
(0)| 00:00:01 |

|   3 |    TABLE ACCESS BY INDEX ROWID| TEST_SORT     | 49902 |  1559K|     8
(0)| 00:00:01 |

|*  4 |     INDEX RANGE SCAN          | I_TEST_SORT_1 |    10 |       |     3
(0)| 00:00:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<11)
   4 - access(SUBSTR("NAME",0,3)='sel')
       filter(SUBSTR("NAME",0,3)='sel')


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

以上測試版本:

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production


11g已經解決這問題了



SQL> create table test_sort(name,object_name) as select 'select',object_name fro
m
  2  dba_objects;

表已建立。

SQL> create index i_test_sort_1 on test_sort(substr(name,0,3),object_name);

索引已建立。

SQL> exec dbms_stats.gather_table_stats(user,'TEST_SORT');

PL/SQL 過程已成功完成。

SQL> set autot trace
SQL> select * from (select * from test_sort where substr(name,0,3)='sel' order b
y
  2  object_name) where rownum<11;

已選擇10行。


執行計劃
----------------------------------------------------------
Plan hash value: 4154852915

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

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

| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%C

PU)| Time     |

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

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

|   0 | SELECT STATEMENT              |               |    10 |   740 |     8
(0)| 00:00:01 |

|*  1 |  COUNT STOPKEY                |               |       |       |
   |          |

|   2 |   VIEW                        |               |    10 |   740 |     8
(0)| 00:00:01 |

|   3 |    TABLE ACCESS BY INDEX ROWID| TEST_SORT     |    10 |   350 |     8
(0)| 00:00:01 |

|*  4 |     INDEX RANGE SCAN          | I_TEST_SORT_1 |       |       |     3
(0)| 00:00:01 |

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

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


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<11)
   4 - access(SUBSTR("NAME",0,3)='sel')


統計資訊
----------------------------------------------------------
        140  recursive calls
          0  db block gets
        372  consistent gets
          0  physical reads
          0  redo size
        696  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
         10  rows processed

SQL> select * from v$version;

BANNER
----------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

itpub原帖子:

http://www.itpub.net/viewthread.php?tid=1148854&pid=13305469&page=1&extra=#pid13305469

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/8984272/viewspace-619966/,如需轉載,請註明出處,否則將追究法律責任。

相關文章