Flux RSS des billets

DotMG's joblog

Work hard at whatever you do! (Ecc. 9. 10a)
Optimizing SQL with CAST
Publié le 18 Mar 2013, 3:41 pm dans iseries
I've run a should-be-simple SQL statement joining two large tables.
select * from bad_customer a, customers b
where a.CUSTID < 1000 and a.REGION=b.REGION and a.DEPTNO = b.DEPTNO

Table a has index on CUSTID field, table b has an index on fields REGION, DEPTNO and ZIP

Logically, no matter how big the table customers is, this statement should be performed very quickly, as fields REGION and DEPTNO are the first and second index of an index of the table customers. However, it took an eternity.

I've run the statement in STRSQL to see why this takes so long, I saw that an access path was being created for the table customers. Then, it took me 1 day to figure out the cause. On the table bad_customer, REGION was declared decimal(5, 0) although its value is always less than 10000. On the table customers however, the definition of the field REGION was decimal(4, 0). Due to this difference of field definition, the logical index could not be used to access table customers.

Replacing a.REGION with CAST(a.REGION as decimal(4, 0)) solved the issue.

select * from bad_customer a, customers b
where a.CUSTID < 1000 and 
cast(a.REGION as decimal(4, 0))=b.REGION and a.DEPTNO = b.DEPTNO
Les commentaires sont fermés pour ce billet.