-- Create table
create table TEST
(
farendma VARCHAR2(4) default '' not null,
mingxibh VARCHAR2(32) default '' not null,
dkzhangh VARCHAR2(32) default '' not null,
dkjiejuh VARCHAR2(35) default '' not null,
huobdhao VARCHAR2(3),
yngyjigo VARCHAR2(12) default '',
jiaoyirq VARCHAR2(8),
jiluztai VARCHAR2(1) not null
)
partition by range (JIAOYIRQ)
(
partition TEST_P170914 values less than ('20170915')
tablespace V7TLHX_DATA_TBS_01
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
),
explain plan for select * from TESTa where a.jiaoyirq >='20180907' and a.jiaoyirq<='20180913';
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
1 Plan hash value: 2638207556
2
3 --------------------------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
5 --------------------------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 200K| 55M| 3699 (1)| 00:00:01 | | |
7 | 1 | PARTITION RANGE ITERATOR| | 200K| 55M| 3699 (1)| 00:00:01 | 359 | 365 |
8 |* 2 | TABLE ACCESS FULL | TEST| 200K| 55M| 3699 (1)| 00:00:01 | 359 | 365 |
9 --------------------------------------------------------------------------------------------------------
10
11 Predicate Information (identified by operation id):
12 ---------------------------------------------------
13
14 2 - filter("A"."JIAOYIRQ"<='20180913')
create table TEST
(
farendma VARCHAR2(4) default '' not null,
mingxibh VARCHAR2(32) default '' not null,
dkzhangh VARCHAR2(32) default '' not null,
dkjiejuh VARCHAR2(35) default '' not null,
huobdhao VARCHAR2(3),
yngyjigo VARCHAR2(12) default '',
jiaoyirq VARCHAR2(8),
dkrzhzhh VARCHAR2(32) default '',
dkrzhzxh VARCHAR2(8) default '',
fkjineee NUMBER(21,2) default '0.00',
fkzjclfs VARCHAR2(1),
daixzhxh VARCHAR2(32) default '',
djiebhao VARCHAR2(32) default '',
zhchbjin NUMBER(21,2) default '0.00',
yuqibjin NUMBER(21,2) default '0.00',
dzhibjin NUMBER(21,2) default '0.00',
daizbjin NUMBER(21,2) default '0.00',
zijnlaiy VARCHAR2(1),
huankzhh VARCHAR2(32) default '',
hkzhhzxh VARCHAR2(8) default '',
keyongje NUMBER(21,2) default '0.00',
hkzongee NUMBER(21,2) default '0.00',
huankzht VARCHAR2(1),
ghbenjin NUMBER(21,2) default '0.00',
ghysyjlx NUMBER(21,2) default '0.00',
ghcsyjlx NUMBER(21,2) default '0.00',
ghynshqx NUMBER(21,2) default '0.00',
ghcushqx NUMBER(21,2) default '0.00',
ghysyjfx NUMBER(21,2) default '0.00',
ghcsyjfx NUMBER(21,2) default '0.00',
ghynshfx NUMBER(21,2) default '0.00',
ghcushfx NUMBER(21,2) default '0.00',
ghyjfuxi NUMBER(21,2) default '0.00',
ghfxfuxi NUMBER(21,2) default '0.00',
ghfajinn NUMBER(21,2) default '0.00',
ghfeiyin NUMBER(21,2) default '0.00',
jiaoyijg VARCHAR2(12) default '',
jiaoyigy VARCHAR2(10) default '',
jiaoyils VARCHAR2(32) default '',
jiaoyisj VARCHAR2(10) default '',
shjshuom VARCHAR2(80) default '',
jiaoyima VARCHAR2(16) default '',
zhaiyoms VARCHAR2(512) default '',
daikghfs VARCHAR2(2),
hkbeizhu VARCHAR2(1000) default '',
fenhbios VARCHAR2(4) not null,
weihguiy VARCHAR2(10) default '' not null,
weihjigo VARCHAR2(12) default '' not null,
weihriqi VARCHAR2(8) not null,
weihshij VARCHAR2(9) default '',
shijchuo NUMBER default '0' not null,
jiluztai VARCHAR2(1) not null
)
用sqldr导入数据:
select count(*) from TEST
create unique index TEST_IDX1 on TEST(MINGXIBH, DKJIEJUH, FARENDMA);
SQL> select a.column_name,
b.num_rows,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a, dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = 'TLCBUSER'
and a.table_name = 'TEST'; 2 3 4 5 6 7 8 9 10 11
no rows selected
SQL> select count(*) from TEST;
COUNT(*)
----------
228259
刚创建的表是没有统计信息的:
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TLCBUSER',
tabname => 'TEST',
estimate_percent => 100,
method_opt => 'for all columns size repeat',
no_invalidate => FALSE,
degree => 8,
cascade => TRUE);
END;
/
SQL> select a.column_name,
b.num_rows,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a, dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = 'TLCBUSER'
and a.table_name = 'TEST'; 2 3 4 5 6 7 8 9 10 11
COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
------------------------------ ---------- ----------- ----------- --------------- -----------
FARENDMA 228259 1 0 NONE 1
MINGXIBH 228259 172788 75.7 NONE 1
DKZHANGH 228259 133475 58.48 NONE 1
DKJIEJUH 228259 133475 58.48 NONE 1
HUOBDHAO 228259 3 0 NONE 1
YNGYJIGO 228259 306 .13 NONE 1
JIAOYIRQ 228259 7 0 NONE 1
DKRZHZHH 228259 31565 13.83 NONE 1
DKRZHZXH 228259 3 0 NONE 1
FKJINEEE 228259 7494 3.28 NONE 1
FKZJCLFS 228259 2 0 NONE 1
DAIXZHXH 228259 0 0 NONE 0
DJIEBHAO 228259 0 0 NONE 0
ZHCHBJIN 228259 42934 18.81 NONE 1
YUQIBJIN 228259 4042 1.77 NONE 1
DZHIBJIN 228259 2693 1.18 NONE 1
DAIZBJIN 228259 5 0 NONE 1
ZIJNLAIY 228259 2 0 NONE 1
HUANKZHH 228259 42600 18.66 NONE 1
HKZHHZXH 228259 8 0 NONE 1
KEYONGJE 228259 26046 11.41 NONE 1
HKZONGEE 228259 56779 24.87 NONE 1
HUANKZHT 228259 3 0 NONE 1
GHBENJIN 228259 46731 20.47 NONE 1
GHYSYJLX 228259 35661 15.62 NONE 1
GHCSYJLX 228259 1 0 NONE 1
GHYNSHQX 228259 1145 .5 NONE 1
GHCUSHQX 228259 85 .04 NONE 1
GHYSYJFX 228259 1330 .58 NONE 1
GHCSYJFX 228259 94 .04 NONE 1
GHYNSHFX 228259 54 .02 NONE 1
GHCUSHFX 228259 111 .05 NONE 1
GHYJFUXI 228259 433 .19 NONE 1
GHFXFUXI 228259 72 .03 NONE 1
GHFAJINN 228259 1 0 NONE 1
GHFEIYIN 228259 332 .15 NONE 1
JIAOYIJG 228259 342 .15 NONE 1
JIAOYIGY 228259 2079 .91 NONE 1
JIAOYILS 228259 166947 73.14 NONE 1
JIAOYISJ 228259 7 0 NONE 1
SHJSHUOM 228259 7 0 NONE 1
JIAOYIMA 228259 40 .02 NONE 1
ZHAIYOMS 228259 8 0 NONE 1
DAIKGHFS 228259 2 0 NONE 1
HKBEIZHU 228259 6 0 NONE 1
FENHBIOS 228259 22 .01 NONE 1
WEIHGUIY 228259 2079 .91 NONE 1
WEIHJIGO 228259 342 .15 NONE 1
WEIHRIQI 228259 7 0 NONE 1
WEIHSHIJ 228259 112632 49.34 NONE 1
SHIJCHUO 228259 219283 96.07 NONE 1
JILUZTAI 228259 1 0 NONE 1
52 rows selected.
因为使用了repert参数, 没有收集直方图
SQL> select a.column_name,
b.num_rows,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a, dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = 'TLCBUSER'
and a.table_name = 'TEST'; 2 3 4 5 6 7 8 9 10 11
COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
------------------------------ ---------- ----------- ----------- --------------- -----------
FARENDMA 228259 1 0 FREQUENCY 1
MINGXIBH 228259 172788 75.7 HEIGHT BALANCED 254
DKZHANGH 228259 133475 58.48 HEIGHT BALANCED 254
DKJIEJUH 228259 133475 58.48 HEIGHT BALANCED 254
HUOBDHAO 228259 3 0 FREQUENCY 3
YNGYJIGO 228259 306 .13 HEIGHT BALANCED 254
JIAOYIRQ 228259 7 0 FREQUENCY 7
DKRZHZHH 228259 31565 13.83 HEIGHT BALANCED 254
DKRZHZXH 228259 3 0 FREQUENCY 3
FKJINEEE 228259 7494 3.28 HEIGHT BALANCED 254
FKZJCLFS 228259 2 0 FREQUENCY 2
DAIXZHXH 228259 0 0 NONE 0
DJIEBHAO 228259 0 0 NONE 0
ZHCHBJIN 228259 42934 18.81 HEIGHT BALANCED 254
YUQIBJIN 228259 4042 1.77 HEIGHT BALANCED 254
DZHIBJIN 228259 2693 1.18 HEIGHT BALANCED 254
DAIZBJIN 228259 5 0 FREQUENCY 5
ZIJNLAIY 228259 2 0 FREQUENCY 2
HUANKZHH 228259 42600 18.66 HEIGHT BALANCED 254
HKZHHZXH 228259 8 0 FREQUENCY 8
KEYONGJE 228259 26046 11.41 HEIGHT BALANCED 254
HKZONGEE 228259 56779 24.87 HEIGHT BALANCED 254
HUANKZHT 228259 3 0 FREQUENCY 3
GHBENJIN 228259 46731 20.47 HEIGHT BALANCED 254
GHYSYJLX 228259 35661 15.62 HEIGHT BALANCED 254
GHCSYJLX 228259 1 0 FREQUENCY 1
GHYNSHQX 228259 1145 .5 HEIGHT BALANCED 254
GHCUSHQX 228259 85 .04 FREQUENCY 85
GHYSYJFX 228259 1330 .58 HEIGHT BALANCED 254
GHCSYJFX 228259 94 .04 FREQUENCY 94
GHYNSHFX 228259 54 .02 FREQUENCY 54
GHCUSHFX 228259 111 .05 FREQUENCY 111
GHYJFUXI 228259 433 .19 HEIGHT BALANCED 254
GHFXFUXI 228259 72 .03 FREQUENCY 72
GHFAJINN 228259 1 0 FREQUENCY 1
GHFEIYIN 228259 332 .15 HEIGHT BALANCED 254
JIAOYIJG 228259 342 .15 HEIGHT BALANCED 254
JIAOYIGY 228259 2079 .91 HEIGHT BALANCED 254
JIAOYILS 228259 166947 73.14 HEIGHT BALANCED 254
JIAOYISJ 228259 7 0 FREQUENCY 7
SHJSHUOM 228259 7 0 FREQUENCY 7
JIAOYIMA 228259 40 .02 FREQUENCY 40
ZHAIYOMS 228259 8 0 FREQUENCY 8
DAIKGHFS 228259 2 0 FREQUENCY 2
HKBEIZHU 228259 6 0 FREQUENCY 6
FENHBIOS 228259 22 .01 FREQUENCY 22
WEIHGUIY 228259 2079 .91 HEIGHT BALANCED 254
WEIHJIGO 228259 342 .15 HEIGHT BALANCED 254
WEIHRIQI 228259 7 0 FREQUENCY 7
WEIHSHIJ 228259 112632 49.34 HEIGHT BALANCED 254
SHIJCHUO 228259 219283 96.07 HEIGHT BALANCED 254
JILUZTAI 228259 1 0 FREQUENCY 1
52 rows selected.
SQL> select count(*) from TEST;
COUNT(*)
----------
228259
SQL> select count(distinct FARENDMA) from TEST;
COUNT(DISTINCTFARENDMA)
-----------------------
1
SQL> select count(distinct MINGXIBH) from TEST;
COUNT(DISTINCTMINGXIBH)
-----------------------
172788
SQL> explain plan for select * from TEST a where a.MINGXIBH='M2018062700000655000002' and a.DKJIEJUH='2018062700000655' and a.FARENDMA='9999';
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2899375220
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 303 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 303 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | TEST_IDX1 | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."MINGXIBH"='M2018062700000655000002' AND
"A"."DKJIEJUH"='2018062700000655' AND "A"."FARENDMA"='9999')
15 rows selected.
SQL> explain plan for select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao
from TEST
where dkjiejuh = '20151020000935'
and farendma = '9999'
and trim(translate(mingxibh, '0123456789', ' ')) is null; 2 3 4 5
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2367693466
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 481 (1)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | 46 | | |
|* 2 | INDEX FAST FULL SCAN| TEST_IDX1 | 1 | 46 | 481 (1)| 00:00:06 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DKJIEJUH"='20151020000935' AND
TRIM(TRANSLATE("MINGXIBH",'0123456789',' ')) IS NULL AND "FARENDMA"='9999')
15 rows selected.
create index TEST_IDX3 on TEST(DKJIEJUH, FARENDMA)
SQL> explain plan for select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao
from TEST
where dkjiejuh = '20151020000935'
and farendma = '9999'
and trim(translate(mingxibh, '0123456789', ' ')) is null; 2 3 4 5
Explained.
SQL>
select * from table(dbms_xplan.display());SQL>
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 898418289
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 46 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 46 | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TEST_IDX3 | 2 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TRIM(TRANSLATE("MINGXIBH",'0123456789',' ')) IS NULL)
3 - access("DKJIEJUH"='20151020000935' AND "FARENDMA"='9999')
16 rows selected.
走index fast full scan的情况:
SQL> explain plan for select MINGXIBH,DKJIEJUH, FARENDMA from KLNL_DKKHMX;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 978815853
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 228K| 10M| 481 (1)| 00:00:06 |
| 1 | INDEX FAST FULL SCAN| KLNL_DKKHMX_IDX1 | 228K| 10M| 481 (1)| 00:00:06 |
-----------------------------------------------------------------------------------------
8 rows selected.
SQL> explain plan for select MINGXIBH from KLNL_DKKHMX where DKJIEJUH='20151020000935' and farendma = '9999';
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 978815853
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 92 | 481 (1)| 00:00:06 |
|* 1 | INDEX FAST FULL SCAN| KLNL_DKKHMX_IDX1 | 2 | 92 | 481 (1)| 00:00:06 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DKJIEJUH"='20151020000935' AND "FARENDMA"='9999')
13 rows selected.
create unique index TEST_IDX1 on TEST(MINGXIBH, DKJIEJUH, FARENDMA);
SQL> explain plan for select MINGXIBH ,DKJIEJUH from KLNL_DKKHMX where farendma = '9999';
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 978815853
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 228K| 10M| 481 (1)| 00:00:06 |
|* 1 | INDEX FAST FULL SCAN| KLNL_DKKHMX_IDX1 | 228K| 10M| 481 (1)| 00:00:06 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FARENDMA"='9999')
13 rows selected.