分区表index_join问题

分区表index_join问题,用globe index与 local index 得到结果不一致



SQL> select count(1) from t_userappview t where t.user_id = 223256935;

  COUNT(1)
----------
        51

建立全局索引
        
SQL> create index IX_USERAPPVIEW_USER_ID on t_userappview(user_id);

Index created.

SQL> create index IX_USERAPPVIEW_APPID on t_userappview(app_id);

Index created.

SQL> select count(app_id) from t_userappview t where t.user_id = 223256935;

COUNT(APP_ID)
-------------
           51


Execution Plan
----------------------------------------------------------
Plan hash value: 1393981304

--------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                        |     1 |    12 |     8   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE         |                        |     1 |    12 |            |          |
|   2 |   VIEW                  | index$_join$_001       |    17 |   204 |     8   (0)| 00:00:01 |
|*  3 |    HASH JOIN            |                        |       |       |            |          |
|   4 |     INDEX FAST FULL SCAN| IX_USERAPPVIEW_APPID   |    17 |   204 |     4   (0)| 00:00:01 |
|*  5 |     INDEX FAST FULL SCAN| IX_USERAPPVIEW_USER_ID |    17 |   204 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   3 - access(ROWID=ROWID)
   5 - filter("T"."USER_ID"=223256935)


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

SQL> drop index IX_USERAPPVIEW_USER_ID;


Index dropped.

SQL> SQL> drop index IX_USERAPPVIEW_APPID;

Index dropped.
---建立本地索引
SQL> create index IX_USERAPPVIEW_USER_ID on t_userappview(user_id) local;

Index created.

SQL> create index IX_USERAPPVIEW_APPID on t_userappview(app_id) local;

Index created.



       
       
SQL>  select count(app_id) from t_userappview t where t.user_id = 223256935;

COUNT(APP_ID)
-------------
            0
           



Execution Plan
----------------------------------------------------------
Plan hash value: 3341191217

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                        |     1 |    12 |     4   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE          |                        |     1 |    12 |            |          |       |       |
|*  2 |   VIEW                   | index$_join$_001       |    17 |   204 |     4   (0)| 00:00:01 |       |       |
|*  3 |    HASH JOIN             |                        |       |       |            |          |       |       |
|   4 |     PARTITION RANGE ALL  |                        |    17 |   204 |     1   (0)| 00:00:01 |     1 |1048575|
|*  5 |      INDEX RANGE SCAN    | IX_USERAPPVIEW_USER_ID |    17 |   204 |     1   (0)| 00:00:01 |     1 |1048575|
|   6 |     PARTITION RANGE ALL  |                        |    17 |   204 |     3   (0)| 00:00:01 |     1 |1048575|
|   7 |      INDEX FAST FULL SCAN| IX_USERAPPVIEW_APPID   |    17 |   204 |     3   (0)| 00:00:01 |     1 |1048575|
-------------------------------------------------------------------------------------------------------------------

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

   2 - filter("T"."USER_ID"=223256935)
   3 - access(ROWID=ROWID)
   5 - access("T"."USER_ID"=223256935)


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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16147313/viewspace-719398/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16147313/viewspace-719398/