分区表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
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/