We have 2 fact tables in our dataware house.  One for transactions of type V, another for 
transactions of type M.  Each table is partitioned by day and there are 90 days online.  
There are 145 Million rows of type V, 133 million of type M.  They are stuctured in 
exactly the same way (same partition key, same indexes, etc). The only real difference is 
that the row size of the table of type V is larger (203 bytes) than the row size of the 
table of type M (141 bytes). We have a query to look for transactions of type V for 
customer X. Performance is good. We have an "identical" query to look for transactions of 
type M for customer X. Performance is horrible.  The difference between the two is the 
query against V is using an INDEX RANGE SCAN.  The query against M will only use an INDEX 
FULL SCAN.  What could be the reason?


Well, we went back and forth on this - tried hints, tried everything.  Spent a while 
scratching our heads.  Finally - I asked them to email me the 10053 trace files.  What I 
discovered was the index was not only not being range scanned for the query against "M", 
it wasn't even being considered. That was a great clue - there was something that was 
precluding this index from being used in that fashion.  I immediately asked for the 
CREATE TABLE and CREATE INDEX statements - something I should have asked for in the 
beginning, I was over analyzing the problem and didn't rule out "simple things" first.  
When I got them - I discovered that the indexed column was a NUMBER(12) in the V table 
and a VARCHAR2(12) in the M table.  It was a simple datatype conversion that was 
precluding the index from being used in a range scan!  They were joining these V and M 
tables to some other table by this column - when it was a NUMBER, the index could be 
range scanned, when it was a VARCHAR2 - it could not.  In the end - what was assumed to 
be an "optimizer problem" turned out to be an implementation mistake - the column was 
supposed to be a NUMBER, they implemented wrong.  Fixing that issue solved the problem 
immediately.  I'm not sure if I looked at the CREATE TABLES that I would have caught it 
right away myself - it was something they overlooked easily enough and it was their data! 
 The 10053 trace certainly helped here by showing that access path was not even 
considered.
ITPub博客

one lesson learn from tom for oracle

最新文章
