Wrong select Plan

guorke · *nix forums beginner Joined: 26 Jul 2005 Posts: 2

I have two table:
one is telephone_info, another is trunk_group_info
and my sql
select c.telephone_id
from trunk_group_info b,telephone_info c
where
b.trunk_id=c.trunk_id and b.state = '10A' and c.state='10A'
1> sp__help telephone_info
2> go
Name Owner Object_type
------------------------------ -----------------
----------------------
table name insert trigger update trigger delete
trigger
-------------------- ------------------ ------------------
------------------
telephone_info .................. ..................
...................
Column name Type I Null Dflt Rule Table
Num
-------------------- ------------ - ---- ---- ----
-------------------- ---
telephone_id Domain_ask_i 0 No telephone_info
1
serv_id Domain_serv_ 0 Yes telephone_info
2
trunk_id varchar(20 ) 0 Yes telephone_info
3
acc_nbr Domain_acc_n 0 Yes telephone_info
4
tele_num_grade char(1 ) 0 Yes telephone_info
5
new_func varchar(20 ) 0 Yes telephone_info
6
password varchar(4 ) 0 Yes telephone_info
7
tele_addr Domain_addr 0 Yes telephone_info
8
tele_near_phone Domain_acc_n 0 Yes telephone_info
9
tele_back_func char(1 ) 0 Yes telephone_info
10
direction char(1 ) 0 Yes telephone_info
11
telephone_statu char(1 ) 0 Yes telephone_info
12
state Domain_state 0 Yes telephone_info
13
state_date Domain_state 0 Yes telephone_info
14
transaction_id Domain_trans 0 No telephone_info
15
install_date datetime 0 Yes telephone_info
16
install_staff_id Domain_id_6 0 Yes telephone_info
17
complete_date datetime 0 Yes telephone_info
18
note Domain_note 0 Yes telephone_info
19

**** Index Information ****
Name c u i a s List of Index Keys
------------------------------ - - - - -
-----------------------------------
telephone_info.PK_TELEPHONE_IN Y Y telephone_id,transaction_id
telephone_info.index1 serv_id
telephone_info.Index_4 acc_nbr
telephone_info.pan_1 trunk_id
telephone_info.Index_2 serv_id

-----------------------------------------------------------

1> sp__help trunk_group_info
2> go
Name Owner Object_type
------------------------------ -----------------
----------------------
table name insert trigger update trigger delete
trigger
-------------------- ------------------ ------------------
------------------
trunk_group_info .................. ..................
...................
Column name Type I Null Dflt Rule Table
Num
-------------------- ------------ - ---- ---- ----
-------------------- ---
trunk_id char(20 ) 0 No trunk_group_info
1
serv_id char(20 ) 0 Yes trunk_group_info
2
trunk_type char(1 ) 0 Yes trunk_group_info
3
in_count int 0 Yes trunk_group_info
4
out_count int 0 Yes trunk_group_info
5
bidirection_count int 0 Yes trunk_group_info
6
trunk_near_phone char(20 ) 0 Yes trunk_group_info
7
trunk_addr char(80 ) 0 Yes trunk_group_info
8
direction_num char(20 ) 0 Yes trunk_group_info
9
trunk_func char(20 ) 0 Yes trunk_group_info
10
trunk_statu char(1 ) 0 No trunk_group_info
11
state char(3 ) 0 Yes trunk_group_info
12
state_date datetime 0 Yes trunk_group_info
13
transaction_id char(20 ) 0 No trunk_group_info
14
note char(100) 0 Yes trunk_group_info
15

the output of dbcc traceon(3604,302,310)
===================================================
Beginning selection of qualifying indexes for table
'trunk_group_info',
correlation name 'b', varno = 0, objectid 1415673060.
The table (Allpages) has 232 rows, 23 pages,
Data Page Cluster Ratio 0.550000

Table scan cost is 232 rows, 23 pages,
using data prefetch (size 16K I/O),
in data cache 'default data cache' (cacheid 0) with LRU replacement

Selecting best index for the SEARCH CLAUSE:
trunk_group_info.state = '10A'

No statistics available for state,
using the default equality selectivity to estimate selectivity.

Estimated selectivity for state,
selectivity = 0.100000.

Estimating selectivity of index 'trunk_group_info_x', indid 1
scan selectivity 1.000000, filter selectivity 1.000000
232 rows, 24 pages, index height 1,
Data Row Cluster Ratio 1.000000,
Index Page Cluster Ratio 0.000000,
Data Page Cluster Ratio 0.550000

The best qualifying access is a table scan,
costing 23 pages,
with an estimate of 23 rows to be returned per scan of the table,
using data prefetch (size 16K I/O),
in data cache 'default data cache' (cacheid 0) with LRU replacement
Search argument selectivity is 0.100000.

*******************************

*******************************
Beginning selection of qualifying indexes for table 'telephone_info',
correlation name 'c', varno = 1, objectid 343669241.
The table (Allpages) has 1678090 rows, 138273 pages,
Data Page Cluster Ratio 0.036623

Table scan cost is 1678090 rows, 138273 pages,
using data prefetch (size 16K I/O),
in data cache 'default data cache' (cacheid 0) with MRU replacement

Selecting best index for the SEARCH CLAUSE:
telephone_info.state = '10A'

No statistics available for state,
using the default equality selectivity to estimate selectivity.

Estimated selectivity for state,
selectivity = 0.100000.

Estimating selectivity of index 'PK_TELEPHONE_INFO', indid 1
scan selectivity 1.000000, filter selectivity 1.000000
1678090 rows, 138277 pages, index height 4,
Data Row Cluster Ratio 1.000000,
Index Page Cluster Ratio 0.000000,
Data Page Cluster Ratio 0.036623

The best qualifying access is a table scan,
costing 138273 pages,
with an estimate of 167809 rows to be returned per scan of the
table,
using data prefetch (size 16K I/O),
in data cache 'default data cache' (cacheid 0) with MRU replacement
Search argument selectivity is 0.100000.

*******************************

*******************************
Beginning selection of qualifying indexes for table 'trunk_group_info',
correlation name 'b', varno = 0, objectid 1415673060.
The table (Allpages) has 232 rows, 23 pages,
Data Page Cluster Ratio 0.550000

Table scan cost is 232 rows, 23 pages,
using data prefetch (size 16K I/O),
in data cache 'default data cache' (cacheid 0) with LRU replacement

Selecting best index for the JOIN CLAUSE:
trunk_group_info.trunk_id = c.trunk_id
trunk_group_info.state = '10A'

Estimated selectivity for state,
selectivity = 0.100000.

Estimated selectivity for trunk_id,
selectivity = 0.012922.

Estimating selectivity of index 'trunk_group_info_x', indid 1
scan selectivity 0.012922, filter selectivity 0.012922
3 rows, 2 pages, index height 1,
Data Row Cluster Ratio 1.000000,
Index Page Cluster Ratio 0.000000,
Data Page Cluster Ratio 0.550000

The best qualifying Nested Loop join index is 'trunk_group_info_x'
(indid 1)
costing 2 pages,
with an estimate of 1 rows to be returned per scan of the table,
using no index prefetch (size 2K I/O) on non-leaf pages,
in index cache 'default data cache' (cacheid 0) with LRU replacement
using no data prefetch (size 2K I/O),
in data cache 'default data cache' (cacheid 0) with LRU replacement
Join selectivity is 0.012922.

If this access path is selected for a merge join, this table will be
sorted.

*******************************

JOIN structure (at 0x1b35f8800) for variable 0
jnvar=1 refcost=11656 refpages=2 reftotpages=1 ordercol[0]=1
jnordercol[1]=3

cindid=1 crows=1 cpages=2 csargsel=0.004310 cjoinsel=0.012922
corder=0x1
cmatch=1
cstat=(0x00400000 (HASCLUST), 0x00002000 (INVSORTAVERT), 0x00001000
(BASESCAN),
0x00000800 (UNIQIDFR))
cstrategy=(0x00000000)
TERMS:
AND (!:0x1b3600aa8) (TokClass: LOGOP) (R: 0x0) (andstat:(0x0002
(TOP))
andstat2:(0x0010 (EQUIJOIN), 0x0001 (JNCLAUSE)) andvarno: 0 andrgid: -1
andojid:
0)
EQ (L:0x168d817c0) (TokClass: RELOP) (rsltype:0x27 (VARCHAR)
(hierarchy:7)
rsllen:20 rslprec:0 rslscale:0 opstat:0x48 (0x40 (WHERE), 0x08
(STATNULL))
opstat2:0x8 (0x08 (OP2_GRP_AVG)) opsubq:0 oppred:0x0 exp#:0 )
VAR (L:0x1b3601740) (TokClass: GVAR) (varname:trunk_id
right:0x168d81728 varno:0 colid:1 colstat: 0x0 (0x00) coltype:0x2f
(CHAR)
(hierarchy:7) colen:20 colprec:0 colscale:0 coloff:2 varutypeid:1
varusecnt:1 varjtcid:-1 varstat:0x84 ((0x0080 (HASARG), 0x0004 (EQ)))
varstat2:0x8 ((0x00000008 (STATSNEEDED))) varlevel:0 varsubq:0)
VAR (R:0x168d81728) (TokClass: GVAR) (varname:trunk_id varno:1
colid:3
colstat: 0x8 (0x08 (STATNULL)) coltype:0x27 (VARCHAR) (hierarchy:7)
colen:20
colprec:0 colscale:0 coloff:-3 varutypeid:2 varnext:0x1b3601420
varusecnt:1 varjtcid:-1 varstat:0x0 ((0x0000)) varstat2:0x8
((0x00000008
(STATSNEEDED))) varlevel:0 varsubq:0)

AND

/
EQ
(# 0)

/ \
VAR VAR
(0,1) (1,3)
trunk_id trunk_id

*******************************
Beginning selection of qualifying indexes for table 'telephone_info',
correlation name 'c', varno = 1, objectid 343669241.
The table (Allpages) has 1678090 rows, 138273 pages,
Data Page Cluster Ratio 0.036623

Table scan cost is 1678090 rows, 138273 pages,
using data prefetch (size 16K I/O),
in data cache 'default data cache' (cacheid 0) with MRU replacement

Selecting best index for the JOIN CLAUSE:
telephone_info.trunk_id = b.trunk_id
telephone_info.state = '10A'

Estimated selectivity for state,
selectivity = 0.100000.

Estimated selectivity for trunk_id,
selectivity = 0.501208.

Estimating selectivity of index 'PK_TELEPHONE_INFO', indid 1
scan selectivity 1.000000, filter selectivity 1.000000
1678090 rows, 138277 pages, index height 4,
Data Row Cluster Ratio 1.000000,
Index Page Cluster Ratio 0.000000,
Data Page Cluster Ratio 0.036623

Estimating selectivity of index 'pan_1', indid 4
scan selectivity 0.501208, filter selectivity 0.501208
841072 rows, 134047 pages, index height 3,
Data Row Cluster Ratio 0.924824,
Index Page Cluster Ratio 0.446518,
Data Page Cluster Ratio 0.918296

The best qualifying Nested Loop join index is 'pan_1' (indid 4)
costing 134047 pages,
with an estimate of 84107 rows to be returned per scan of the table,
using index prefetch (size 16K I/O) on leaf pages,
in index cache 'default data cache' (cacheid 0) with MRU replacement
using data prefetch (size 16K I/O),
in data cache 'default data cache' (cacheid 0) with MRU replacement
Join selectivity is 0.501208.

If this access path is selected for a merge join, this table will be
sorted.

*******************************

JOIN structure (at 0x1b35f7800) for variable 1
jnvar=0 refcost=11423886 refpages=1797 reftotpages=3583 ordercol[0]=3
jnordercol[1]=1

cindid=4 crows=84107 cpages=134047 csargsel=0.050121 cjoinsel=0.501208
corder=0x3 cmatch=1
cstat=(0x00002000 (INVSORTAVERT), 0x00001000 (BASESCAN), 0x00000400
(ALLKEYS))
cstrategy=(0x00000020 (IXP_REPLACEBUF), 0x00000010 (DTP_REPLACEBUF),
0x00000008
(SEQ_IXPREFETCH), 0x00000001 (SEQ_DTPREFETCH))
TERMS:
AND (!:0x1b3600aa8) (TokClass: LOGOP) (R: 0x0) (andstat:(0x0002
(TOP))
andstat2:(0x0010 (EQUIJOIN), 0x0001 (JNCLAUSE)) andvarno: 0 andrgid: -1
andojid:
0)
EQ (L:0x168d817c0) (TokClass: RELOP) (rsltype:0x27 (VARCHAR)
(hierarchy:7)
rsllen:20 rslprec:0 rslscale:0 opstat:0x48 (0x40 (WHERE), 0x08
(STATNULL))
opstat2:0x8 (0x08 (OP2_GRP_AVG)) opsubq:0 oppred:0x0 exp#:0 )
VAR (L:0x168d81728) (TokClass: GVAR) (varname:trunk_id
right:0x1b3601740 varno:1 colid:3 colstat: 0x8 (0x08 (STATNULL))
coltype:0x27 (VARCHAR) (hierarchy:7) colen:20 colprec:0 colscale:0
coloff:-3
varutypeid:2 varnext:0x1b3601420 varusecnt:1 varjtcid:-1
varstat:0x84
((0x0080 (HASARG), 0x0004 (EQ))) varstat2:0x8 ((0x00000008
(STATSNEEDED)))
varlevel:0 varsubq:0)
VAR (R:0x1b3601740) (TokClass: GVAR) (varname:trunk_id
right:0x168d81728 varno:0 colid:1 colstat: 0x0 (0x00) coltype:0x2f
(CHAR)
(hierarchy:7) colen:20 colprec:0 colscale:0 coloff:2 varutypeid:1
varusecnt:1 varjtcid:-1 varstat:0x84 ((0x0080 (HASARG), 0x0004 (EQ)))
varstat2:0x8 ((0x00000008 (STATSNEEDED))) varlevel:0 varsubq:0)

AND

/
EQ
(# 0)

/ \
VAR VAR
(1,3) (0,1)
trunk_id trunk_id

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 = 11572078):=====================A

varno=0 (trunk_group_info) indexid=0 ()
path=0x16963c3f8 pathtype=sclause
method=NESTED ITERATION
scanthreads=1
outerrows=1 rows=23 joinsel=1.000000 scanpgs=23
data_prefetch=YES data_iosize=16 data_bufreplace=LRU
scanlio=23 scanpio=11
corder=1

varno=1 (telephone_info) indexid=4 (pan_1)
path=0x1b35f7800 pathtype=join
method=REFORMATTING
scanthreads=1
outerrows=23 rows=1951286 joinsel=0.501208 jnpgs_per_scan=134047
index_prefetch=YES index_iosize=16 index_bufreplace=MRU
data_prefetch=YES data_iosize=16 data_bufreplace=MRU
scanlio=41691 scanpio=3587
corder=3

jnvar=0 refcost=11423886 refpages=1797 reftotpages=3583 ordercol[0]=3
ordercol[1]=1

NEW PLAN (total cost = 6856200):-========================B

varno=0 (trunk_group_info) indexid=0 ()
path=0x16963c3f8 pathtype=sclause
method=NESTED ITERATION
scanthreads=1
outerrows=1 rows=23 joinsel=1.000000 scanpgs=23
data_prefetch=YES data_iosize=16 data_bufreplace=LRU
scanlio=23 scanpio=11
corder=1

varno=1 (telephone_info) indexid=4 (pan_1)
path=0x1b35f7800 pathtype=join
method=NESTED ITERATION
scanthreads=1
outerrows=23 rows=1951286 joinsel=0.501208 jnpgs_per_scan=134047
index_prefetch=YES index_iosize=16 index_bufreplace=MRU
data_prefetch=YES data_iosize=16 data_bufreplace=MRU
scanlio=3109891 scanpio=35343
corder=3

jnvar=0 refcost=11423886 refpages=1797 reftotpages=3583 ordercol[0]=3
ordercol[1]=1

1 - 0 -

NEW PLAN (total cost = 3368612): ====================C

varno=1 (telephone_info) indexid=0 ()
path=0x1b35f9000 pathtype=sclause
method=NESTED ITERATION
scanthreads=1
outerrows=1 rows=167809 joinsel=1.000000 scanpgs=138273
data_prefetch=YES data_iosize=16 data_bufreplace=MRU
scanlio=138273 scanpio=133841
corder=1

varno=0 (trunk_group_info) indexid=1 (trunk_group_info_x)
path=0x1b35f8800 pathtype=join
method=REFORMATTING
scanthreads=1
outerrows=167809 rows=50307 joinsel=0.012922 jnpgs_per_scan=2
data_prefetch=NO data_iosize=2 data_bufreplace=LRU
scanlio=335618 scanpio=2
corder=1

jnvar=1 refcost=11656 refpages=2 reftotpages=1 ordercol[0]=1
ordercol[1]=3

NEW PLAN (total cost = 3357370):========================D

varno=1 (telephone_info) indexid=0 ()
path=0x1b35f9000 pathtype=sclause
method=NESTED ITERATION
scanthreads=1
outerrows=1 rows=167809 joinsel=1.000000 scanpgs=138273
data_prefetch=YES data_iosize=16 data_bufreplace=MRU
scanlio=138273 scanpio=133841
corder=1

varno=0 (trunk_group_info) indexid=1 (trunk_group_info_x)
path=0x1b35f8800 pathtype=join
method=NESTED ITERATION
scanthreads=1
outerrows=167809 rows=50307 joinsel=0.012922 jnpgs_per_scan=2
data_prefetch=NO data_iosize=2 data_bufreplace=LRU
scanlio=335618 scanpio=25
corder=1

jnvar=1 refcost=11656 refpages=2 reftotpages=1 ordercol[0]=1
ordercol[1]=3

TOTAL # PERMUTATIONS: 2

TOTAL # PLANS CONSIDERED: 6

CACHE USED BY THIS PLAN:

CacheID = 0: (2K) 23 (4K) 0 (8K) 0 (16K) 133841

FINAL PLAN (total cost = 3357370):

varno=1 (telephone_info) indexid=0 ()
path=0x1b35f9000 pathtype=sclause
method=NESTED ITERATION
scanthreads=1
outerrows=1 rows=167809 joinsel=1.000000 scanpgs=138273
data_prefetch=YES data_iosize=16 data_bufreplace=LRU
scanlio=138273 scanpio=133841
corder=1

varno=0 (trunk_group_info) indexid=1 (trunk_group_info_x)
path=0x1b35f8800 pathtype=join
method=NESTED ITERATION
scanthreads=1
outerrows=167809 rows=50307 joinsel=0.012922 jnpgs_per_scan=2
data_prefetch=NO data_iosize=2 data_bufreplace=LRU
scanlio=335618 scanpio=25
corder=1

jnvar=1 refcost=11656 refpages=2 reftotpages=1 ordercol[0]=1
ordercol[1]=3

SUBSTITUTING VARIABLE 1

SUBSTITUTING VARIABLE 0

QUERY PLAN FOR STATEMENT 1 (at line 1).

STEP 1
The type of query is SELECT.

FROM TABLE
telephone_info
c
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
trunk_group_info
b
Nested iteration.
Using Clustered Index.
Index : trunk_group_info_x
Forward scan.
Positioning by key.
Keys are:
trunk_id ASC
=================================
the version is Adaptive Server Enterprise/12.5.0.3
my question is why sybase select telephone_info(table scan) then join
trunk_group_info (use trunk_group_info_x)
I think The fast way is
select trunk_group_info(table scan or use pan_1),then join table
telephone_info(use PK_TELEPHONE_IN)
and
why total cost of A or B is larger than C or D

Google