Performance Issue Of FTS Against Hash Partitioned Table With A High Column Number
Last updated on FEBRUARY 02, 2017
Applies to:Oracle Database - Enterprise Edition - Version 22.214.171.124 to 126.96.36.199 [Release 11.2]
Information in this document applies to any platform.
Working with partitioned table, which is around 1.2GB in size and around 150000 blocks in all partitions.
Following select with simple where condition takes 35 minutes to complete:
This FTS scan has 4837392 consistent gets, which is much more that around 150000 blocks which table has.
Also physical reads 3253414 blocks, which means 25 GB of data, is around 21 times higher than whole table size.
This happens using a table with a high column number, for example, here the table has column number 183 of 422 columns.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms