| Method of Tuning Database - row reduction [message #480029] |
Wed, 20 October 2010 13:40  |
palhello
Messages: 15
Registered: November 2008
|
Junior Member |
|
|
There is a simple way to increase the performance of a query by reducing the row-size of the table it hits. I used it in the past by dividing the table into smaller parts and querying respective smaller table in each query.
Does anyone know what is this method called ? just forgot the method and can't recall it. Anyone knows what this type of row-reduction optimization is called ?
|
|
|
|
|
|
|
|
| Re: Method of Tuning Database - row reduction [message #480053 is a reply to message #480040] |
Wed, 20 October 2010 17:49  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I call it "vertically partitioning" a table. There are rational reasons to do this, but improving query performance is typically not one of them unless you have rows that exceed 1 database block in size.
The reason is that Oracle reads and writes BLOCKS, not ROWS. When you read a row via an index, it has to read the entire block regardless of how big the row.
For very big tables, shorter rows may improve Full Table Scans, but these benefits will quickly be lost the first time you need to Full Scan with one of those migrated columns and perform a monolithic join operation.
If you have a performance problem, it is likely that there are other causes.
Ross Leishman
|
|
|
|
|
|
|
|
| Re: Method of Tuning Database - row reduction [message #483181 is a reply to message #483144] |
Thu, 18 November 2010 23:34 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Um, now you're talking about fewer rows - before you were talking about fewer columns (reduced row width).
Having the rows of a table grouped into segments that can be scanned independently of the the entire table is called PARTITIONING. Oracle supports it natively.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
). But ofcourse, this is not ideal for most systems and depends upon how the system is built.