ASE Database forum - P&T question

Author

P&T question

Ollie

2005-10-27, 8:21 am

Does a mismatch datatype in a where clause on a select
statement cause a tablescan? I've always believe this is the
case, but all scenerios I've created proof otherwise.

Ollie.

Mark A. Parsons

2005-10-27, 8:21 am

Can a datatype mismatch cause a table scan? yes

Will a datatype mismatch *always* cause a table scan? no

It usually comes down to a question of what datatypes are in question
and where they fall in the datatype hierarchy.

Now, with 15.0 ... doesn't a lot of the datatype mismatch issues go
away, ie, ASE does a better job of implicitly converting datatypes?

Ollie wrote:

> Does a mismatch datatype in a where clause on a select
> statement cause a tablescan? I've always believe this is the
> case, but all scenerios I've created proof otherwise.
>
> Ollie.

Jeffrey R. Garbus

2005-10-27, 8:21 am

It used to be a big problem (many major releases ago); lately, I've found it
very hard to MAKE it table scan unnecessarily on a datatype mismatch.

Jeff

<Ollie> wrote in message news:434ab1c4.33d5.1681692777@sybase.com...
> Does a mismatch datatype in a where clause on a select
> statement cause a tablescan? I've always believe this is the
> case, but all scenerios I've created proof otherwise.
>
> Ollie.

Ollie

2005-10-27, 8:21 am

I am trying to convince developers in my shop to avoid
matching char with varchar so the optimizer doesn't pick a
tablescan, but I can't proof this in any of the test
scenerios I've come-up with so far. The index is been used
in all situations.

Do u think you can come-up with a test to support the "yes"
argument? I myself has run into the situation between, but I
can't come-up with one now. Or maybe this situations doesn't
exist anymore.

Ollie[color=darkred]

> Can a datatype mismatch cause a table scan? yes
>
> Will a datatype mismatch *always* cause a table scan? no
>
> It usually comes down to a question of what datatypes are
> in question and where they fall in the datatype
> hierarchy.
>
> Now, with 15.0 ... doesn't a lot of the datatype mismatch
> issues go away, ie, ASE does a better job of implicitly
> converting datatypes?
>
> Ollie wrote:
>

Mark A. Parsons

2005-10-27, 8:21 am

char/varchar may be an 'old' issue, I'm unable to re-create an issue at
the moment ...

Where I've seen problems lately (ASE 12.x) is with datatype mismatches
between numbers .... int vs tinyint ... int vs numeric ... numeric(x) vs
numeric(y) ... etc.

The following shows the problem with int vs tinyint:

ASE version: 12.5.3 ESD #3 (Solaris)
------------------------------------

create table t2
(a tinyint not null
,b int not null
,c char(1500) not null) -- force one page per record
go

declare @a tinyint
select @a = 0
while @a < 255
begin
insert t2 values (@a, @a, 'dummy')
select @a = @a + 1
end
go

create index idx1 on t2(a)
create index idx2 on t2(b)
go

sp_spaceused t2,1
go

index_name size reserved unused
-------------------- ---------- ---------- ----------
t2 0 KB 526 KB 16 KB
idx1 6 KB 32 KB 26 KB
idx2 6 KB 32 KB 26 KB

(1 row affected)
name rowtotal reserved data
index_size unused
-------------------- ----------- --------------- ---------------
--------------- ---------------
t2 255 590 KB 510 KB 12 KB
68 KB
(return status = 0)

set showplan on
set statistics io on
go

declare @a int,
@b tinyint
select @a = 5,
@b = 5

-- trying to access 'tinyint' column with 'int' variable

select * from t2 where a = @a

-- trying to access 'int' column with 'tinyint' variable

select * from t2 where b = @b
go

QUERY PLAN FOR STATEMENT 3 (at line 5).

STEP 1
The type of query is SELECT.

FROM TABLE
t2
Nested iteration.
Table Scan. <-- problem using index on 'tinyint'
<-- column when @variable is 'int'
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

QUERY PLAN FOR STATEMENT 4 (at line 6).

STEP 1
The type of query is SELECT.

FROM TABLE
t2
Nested iteration.
Index : idx2 <-- no problem using index
<-- on 'int' column when
<-- @variable is 'tinyint'
Forward scan.
Positioning by key.
Keys are:
b ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

table scan ==> 256 lio's
====================
====
Table: t2 scan count 1, logical reads: (regular=256 apf=0 total=256),
physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Total writes for this command: 0

index is used ==> 3 lio's
====================
=====
Table: t2 scan count 1, logical reads: (regular=3 apf=0 total=3),
physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Total writes for this command: 0

The issue comes down to one of where do the various datatypes show up in
the datatype hierarchy.

Ollie wrote:
[color=darkred]
> I am trying to convince developers in my shop to avoid
> matching char with varchar so the optimizer doesn't pick a
> tablescan, but I can't proof this in any of the test
> scenerios I've come-up with so far. The index is been used
> in all situations.
>
> Do u think you can come-up with a test to support the "yes"
> argument? I myself has run into the situation between, but I
> can't come-up with one now. Or maybe this situations doesn't
> exist anymore.
>
> Ollie
>

Sponsored Links