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博客 ” ，链接：http://blog.itpub.net/3637/viewspace-765938/，如需转载，请注明出处，否则将追究法律责任。