马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。
您需要 登录 才可以下载或查看，没有账号？站点注册
×
select count(*) from T_Basic a,T_EStatus b where a.EID =b.EID 下面是我执行这条语句的跟踪结果，T_Basic和T_EStatus 都 有150万条数据，两张表的EID都是索引，大概执行15秒。 *******************************
Beginning selection of qualifying indexes for table 'T_Basic',
correlation name 'a', varno = 0, objectid 1856006612.
The table (Datarows) has 461020 rows, 106683 pages,
The table's Data Page Cluster Ratio 0.999990 Table scan cost is 461020 rows, 107416 pages,
using data prefetch (size 16K I/O),
in data cache 'bizcache' (cacheid 1) with MRU replacement
Index selection finished for table 'T_Basic' (objectid 1856006612).
The best qualifying index is 'T_18560066121' (indid 5)
costing 6570 pages,
with an estimate of 461020 rows to be returned per scan of the table,
using index prefetch (size 16K I/O) on leaf pages,
in index cache 'bizcache' (cacheid 1) with LRU replacement
Index covers query.
Search argumE selectivity is 1.000000. ******************************* *******************************
Beginning selection of qualifying indexes for table 'T_EStatus',
correlation name 'b', varno = 1, objectid 2080007410.
The table (Datarows) has 1419841 rows, 57705 pages,
The table's Data Page Cluster Ratio 0.999723
The table has 20 partitions.
The largest partition has 57666 pages.
The partition skew is 19.514721.
Table scan cost is 1419841 rows, 59081 pages,
using data prefetch (size 16K I/O),
in data cache 'bizcache' (cacheid 1) with MRU replacement
Index selection finished for table 'T_EStatus' (objectid 2080007410). The best qualifying index is 'id_Eid' (indid 3)
costing 21347 pages,
with an estimate of 1419841 rows to be returned per scan of the table,
using index prefetch (size 16K I/O) on leaf pages,
in index cache 'bizcache' (cacheid 1) with LRU replacement
Index covers query.
Search argumE selectivity is 1.000000. ******************************* *******************************
Beginning selection of qualifying indexes for table 'T_Basic',
correlation name 'a', varno = 0, objectid 1856006612.
The table (Datarows) has 461020 rows, 106683 pages,
The table's Data Page Cluster Ratio 0.999990
Table scan cost is 461020 rows, 107416 pages,
using data prefetch (size 16K I/O),
in data cache 'bizcache' (cacheid 1) with MRU replacement
Selecting best index for the JOIN CLAUSE:
T_Basic.EID = b.EID Estimated selectivity for EID,
selectivity = 0.000002.
Unique nonclustered index found, returns 1 row, 4 pages Estimating selectivity of index 'T_18560066121', indid 5
scan selectivity 0.000002, filter selectivity 0.000002
1 rows, 4 pages, index height 3,
Data Row Cluster Ratio 0.997759,
Index Page Cluster Ratio 0.932301,
Data Page Cluster Ratio 0.991322 The best qualifying Nested Loop join index is 'T_18560066121' (indid 5)
costing 4 pages,
with an estimate of 1 rows to be returned per scan of the table,
using no index prefetch (size 2K I/O) on leaf pages,
in index cache 'bizcache' (cacheid 1) with LRU replacement
Index covers query.
Join selectivity is 0.000002. If this access path is selected for a merge join, this table will be sorted. ******************************* *******************************
Beginning selection of qualifying indexes for table 'T_EStatus',
correlation name 'b', varno = 1, objectid 2080007410.
The table (Datarows) has 1419841 rows, 57705 pages,
The table's Data Page Cluster Ratio 0.999723
The table has 20 partitions.
The largest partition has 57666 pages.
The partition skew is 19.514721.
Table scan cost is 1419841 rows, 59081 pages,
using data prefetch (size 16K I/O),
in data cache 'bizcache' (cacheid 1) with MRU replacement
Selecting best index for the JOIN CLAUSE:
T_EStatus.EID = a.EID Estimated selectivity for EID,
selectivity = 0.000001. Estimating selectivity of index 'id_Eid', indid 3
scan selectivity 0.000001, filter selectivity 0.000001
1 rows, 4 pages, index height 3,
Data Row Cluster Ratio 0.489111,
Index Page Cluster Ratio 0.976601,
Data Page Cluster Ratio 0.350660 The best qualifying Nested Loop join index is 'id_Eid' (indid 3)
costing 4 pages,
with an estimate of 1 rows to be returned per scan of the table,
using no index prefetch (size 2K I/O) on leaf pages,
in index cache 'bizcache' (cacheid 1) with LRU replacement
Index covers query.
Join selectivity is 0.000001. If this access path is selected for a merge join, this table will be sorted. *******************************
QUERY IS CONNECTED
Number of tables in join: 2
Number of tables considered at a time: 2
Table count setting: 0 (default value used)
0 - 1 - NEW PLAN (total cost = 3780590): varno=0 (T_Basic) indexid=5 (T_18560066121)
path=0x2981B4EC pathtype=sclause
method=NESTED ITERATION
scanthreads=1
outerrows=1 rows=461020 joinsel=1.000000 scanpgs=6570
index_prefetch=YES index_iosize=16 index_bufreplace=LRU
scanlio=6570 scanpio=1300
corder=1 varno=1 (T_EStatus) indexid=3 (id_Eid)
path=0x2679B000 pathtype=join
method=NESTED ITERATION
scanthreads=1
outerrows=461020 rows=462472 joinsel=0.000001 jnpgs_per_scan=4
index_prefetch=YES index_iosize=16 index_bufreplace=LRU
scanlio=1844080 scanpio=3105
corder=3 jnvar=0 refcost=0 refpages=0 reftotpages=16920 ordercol[0]=3 ordercol[1]=1 1 - 0 - TOTAL # PERMUTATIONS: 2 TOTAL # PLANS CONSIDERED: 12 CACHE USED BY THIS PLAN: CacheID = 1: (2K) 0 (4K) 0 (8K) 0 (16K) 27917 FINAL PLAN (total cost = 3780590):
varno=0 (T_Basic) indexid=5 (T_18560066121)
path=0x2981B4EC pathtype=sclause
method=NESTED ITERATION
scanthreads=1
outerrows=1 rows=461020 joinsel=1.000000 scanpgs=6570
index_prefetch=YES index_iosize=16 index_bufreplace=LRU
scanlio=6570 scanpio=1300
corder=1
varno=1 (T_EStatus) indexid=3 (id_Eid)
path=0x2679B000 pathtype=join
method=NESTED ITERATION
scanthreads=1
outerrows=461020 rows=462472 joinsel=0.000001 jnpgs_per_scan=4
index_prefetch=YES index_iosize=16 index_bufreplace=LRU
scanlio=1844080 scanpio=3105
corder=3 jnvar=0 refcost=0 refpages=0 reftotpages=16920 ordercol[0]=3 ordercol[1]=1 | 
|Archiver|PowerBuilder(PB)BBS社区
( 鲁ICP备2021027222号-1 )
