存在一个分段表,根据字段umEventTimestamp进行分段,字段umEventTimestamp上存在一个位图索引。在进行如下查询时,竟然进行全部扫描,如何理解? 谢谢!explain plan的输出:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1319849956
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Tem
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7086K| 1351M|
| 1 | SORT GROUP BY | | 7086K| 1351M| 4
| 2 | PARTITION RANGE SINGLE| | 10M| 1911M|
|* 3 | TABLE ACCESS FULL | PARTITION_TEST_LOCALINDEX | 10M| 1911M|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("UMEVENTTIMESTAMP">=0 AND "UMEVENTTIMESTAMP"<1000000)查询语句:
explain plan for select UMBsid, UMAccessType, AVG(UMBsActiveUser)
from partition_test_localindex where umEventTimestamp >= 0 and umEventTimestamp < 1000000 group by UMBsid, UMAccessType order by UMBsid, UMAccessType; 表和索引的定义:
create table on partition_test_localindex
(
umID NUMERIC (10) NOT NULL,
umPDN VARCHAR (200) NOT NULL,
umBSID VARCHAR (200),
umAccessType NUMERIC (10),
umEventTimestamp NUMERIC(19),
umBsActiveUser NUMERIC (20),
umBsInactiveUser NUMERIC (20),
umBsLocalUser NUMERIC (20)
)
PARTITION BY RANGE (umEventtimestamp)
(
PARTITION p20120501 VALUES LESS THAN (20000000),
PARTITION p20120502 VALUES LESS THAN (40000000),
PARTITION p20120503 VALUES LESS THAN (60000000),
);
create bitmap Index partition_test_localindex_1 on partition_test_localindex (umEventTimestamp) Local;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1319849956
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Tem
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7086K| 1351M|
| 1 | SORT GROUP BY | | 7086K| 1351M| 4
| 2 | PARTITION RANGE SINGLE| | 10M| 1911M|
|* 3 | TABLE ACCESS FULL | PARTITION_TEST_LOCALINDEX | 10M| 1911M|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("UMEVENTTIMESTAMP">=0 AND "UMEVENTTIMESTAMP"<1000000)查询语句:
explain plan for select UMBsid, UMAccessType, AVG(UMBsActiveUser)
from partition_test_localindex where umEventTimestamp >= 0 and umEventTimestamp < 1000000 group by UMBsid, UMAccessType order by UMBsid, UMAccessType; 表和索引的定义:
create table on partition_test_localindex
(
umID NUMERIC (10) NOT NULL,
umPDN VARCHAR (200) NOT NULL,
umBSID VARCHAR (200),
umAccessType NUMERIC (10),
umEventTimestamp NUMERIC(19),
umBsActiveUser NUMERIC (20),
umBsInactiveUser NUMERIC (20),
umBsLocalUser NUMERIC (20)
)
PARTITION BY RANGE (umEventtimestamp)
(
PARTITION p20120501 VALUES LESS THAN (20000000),
PARTITION p20120502 VALUES LESS THAN (40000000),
PARTITION p20120503 VALUES LESS THAN (60000000),
);
create bitmap Index partition_test_localindex_1 on partition_test_localindex (umEventTimestamp) Local;
如果ORACLE分析,走索引的效率不如全表扫描的话,那么查询计划就是全表扫描