1 Plan hash value: 1138811981
2
3 ----------------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
5 ----------------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 1132K| 725M| | 185K (1)| 00:37:10 |
7 | 1 | SORT ORDER BY | | 1132K| 725M| 1608M| 185K (1)| 00:37:10 |
8 |* 2 | TABLE ACCESS FULL| MMS_SWFINFO0001 | 1132K| 725M| | 24884 (1)| 00:04:59 |
9 ----------------------------------------------------------------------------------------------
10
11 Predicate Information (identified by operation id):
12 ---------------------------------------------------
13
14 2 - filter("TXTID"='TRAN,MMS_SWFINFO0001,MMS_SWFINFO,B4,SWFO201808090002')
PLAN_TABLE_OUTPUT
1 Plan hash value: 1138811981
2
3 ----------------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
5 ----------------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 1128K| 724M| | 185K (1)| 00:37:06 |
7 | 1 | SORT ORDER BY | | 1128K| 724M| 1602M| 185K (1)| 00:37:06 |
8 |* 2 | TABLE ACCESS FULL| MMS_SWFINFO0001 | 1128K| 724M| | 24884 (1)| 00:04:59 |
9 ----------------------------------------------------------------------------------------------
10
11 Predicate Information (identified by operation id):
12 ---------------------------------------------------
13
14 2 - filter("TXTID"='TRAN,MMS_SWFINFO0001,MMS_SWFINFO,B4,SWFO201808090002')
select count(distinct(TXTID)) from MMS_SWFINFO0001
COUNT(DISTINCT(TXTID))
1 1096192
select count(*),count(distinct(TXTID)) ,count(*) /count(distinct(TXTID)) from MMS_SWFINFO0001
COUNT(*) COUNT(DISTINCT(TXTID)) COUNT(*)/COUNT(DISTINCT(TXTID)
1 1128455 1096192 1.02943188784447
PLAN_TABLE_OUTPUT
1 Plan hash value: 22513045
2
3 -------------------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 -------------------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 1 | 673 | 5 (0)| 00:00:01 |
7 | 1 | TABLE ACCESS BY INDEX ROWID| MMS_SWFINFO0001 | 1 | 673 | 5 (0)| 00:00:01 |
8 |* 2 | INDEX RANGE SCAN | P_SWFINFO0001_KEY | 1 | | 4 (0)| 00:00:01 |
9 -------------------------------------------------------------------------------------------------
10
11 Predicate Information (identified by operation id):
12 ---------------------------------------------------
13
14 2 - access("TXTID"='aa')
15 filter("TXTID"='aa')
PLAN_TABLE_OUTPUT
1 Plan hash value: 22513045
2
3 -------------------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 -------------------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 1128K| 724M| 493K (1)| 01:38:44 |
7 | 1 | TABLE ACCESS BY INDEX ROWID| MMS_SWFINFO0001 | 1128K| 724M| 493K (1)| 01:38:44 |
8 |* 2 | INDEX RANGE SCAN | P_SWFINFO0001_KEY | 1128K| | 26319 (1)| 00:05:16 |
9 -------------------------------------------------------------------------------------------------
10
11 Predicate Information (identified by operation id):
12 ---------------------------------------------------
13
14 2 - access("TXTID"='TRAN,MMS_SWFINFO0001,MMS_SWFINFO,B4,SWFO201808090002')
15 filter("TXTID"='TRAN,MMS_SWFINFO0001,MMS_SWFINFO,B4,SWFO201808090002')
COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
1 TXTID 1128461 4 0 FREQUENCY 4
2 TXTNO 1128461 8 0 NONE 1
3 TABNAME 1128461 1 0 NONE 1
4 FLDNAME 1128461 2 0 NONE 1
5 KEYVALUE 1128461 608224 53.9 NONE 1
6 TXTDATA 1128461 634070 56.19 NONE 1
7 TRXKEY 1128461 608224 53.9 NONE 1
8 TRXREF 1128461 608224 53.9 NONE 1
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'UTAN',
tabname => 'MMS_SWFINFO0001',
estimate_percent => 100,
method_opt => 'for columns TXTID size 1',
no_invalidate => FALSE,
degree => 8,
cascade => TRUE);
END;
干掉直方图:
method_opt => 'for columns TXTID size 1',
COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
1 TXTID 1128461 1096198 97.14 NONE 1
2 TXTNO 1128461 8 0 FREQUENCY 8
3 TABNAME 1128461 1 0 FREQUENCY 1
4 FLDNAME 1128461 2 0 FREQUENCY 2
5 KEYVALUE 1128461 608224 53.9 HEIGHT BALANCED 254
6 TXTDATA 1128461 576701 51.11 HEIGHT BALANCED 254
7 TRXKEY 1128461 608224 53.9 HEIGHT BALANCED 254
8 TRXREF 1128461 608224 53.9 HEIGHT BALANCED 254
explain plan for select * from MMS_SWFINFO0001 a where TXTID = 'TRAN,MMS' order by TXTNO ;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
1 Plan hash value: 22513045
2
3 -------------------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 -------------------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 1 | 673 | 5 (0)| 00:00:01 |
7 | 1 | TABLE ACCESS BY INDEX ROWID| MMS_SWFINFO0001 | 1 | 673 | 5 (0)| 00:00:01 |
8 |* 2 | INDEX RANGE SCAN | P_SWFINFO0001_KEY | 1 | | 4 (0)| 00:00:01 |
9 -------------------------------------------------------------------------------------------------
10
11 Predicate Information (identified by operation id):
12 ---------------------------------------------------
13
14 2 - access("TXTID"='TRAN,MMS')
15 filter("TXTID"='TRAN,MMS')
干掉直方图后走索引