vertical partitioning
ankit sood, December 18, 2003 - 5:53 am UTC
I have not any Document on this vertical partitioning.
Can you provide me the same.
December 18, 2003 - 11:14 am UTC
umm, it is just putting some columns in table 1, some in table 2.
A veritical "slice"
only "some of the columns"
whereas a horizontal slice would be "some of the rows".
Vertical Partitioning in Oracle
Reader, March 23, 2005 - 5:48 pm UTC
I think I can relate to the users that come here looking for the vertical paritioning. The two table solution is more of logical design stuff....
Q1. Does Oracle 9.2.x provides support for vertical partitioning?
While I am sure there will be many who will recommend two table approach, ability to partition a very wide table vertically has benefits in some scenarios.
March 23, 2005 - 6:29 pm UTC
All releases do
it is called "two tables"
the two tables is PHYSICAL design, the single table is the logical design (and probably the LOGICL physical implementation as well)
or -- be more specific as to the exact needs here and what you are trying to accomplish.
Vertical partitioning
kura, May 22, 2006 - 2:59 pm UTC
I think vertical partitioning would be useful for pretty much the same reasons as current/horizontal partitioning:
1. performance - much more related data in a block; perhaps much less I/O in DW (if you have very wide tables but a single query uses only narrow slices of them); lower cost of full table scan (becomes full vertical partition scan).
2. maintenance - smaller pieces to manage; for example I guess that gathering stats on a column should be much faster (if a single block contains, say, five times more values for the given column then gathering stats on this column should be about five times faster, right?)
Using two tables is not transparent - requires changes in the code. Using indexes or materialized views requires additional space and maintenance.
Implementing vertical partitioning would require some thinking through about locks (locking a row in a few places) and probably other issues but I'm sure that Oracle staff could solve them. :-)
Moreover, Oracle could be the first RDBMS to support piece-of-row-level-locking. It would be very dangerous and probably useless but what a great feature for marketing! ;->
How to decide?
A reader, November 24, 2009 - 4:55 am UTC
Hi Tom,
If we have a table with average row size of around 4K, with around 800 columns(db block size is 8k - 10gR1). What is the criteria of selecting vertical partitioning? The table is being used for analytics so user may select all columns, which we can expose through view.
Thanks
November 24, 2009 - 11:45 am UTC
... The table is being used for analytics so user may select all columns...
that'll be one of the key considerations. You say "may", but based on your knowledge of the data and the analysis performed by the end users - do they - do they pick all 800 or is there some smaller subset they generally pick from and then a set that they sometimes use - but not often?
Also, do you full scan frequently - if you do not, if you use an index to access the rows - probably vertical partitioning is not something to consider.
If you full scan frequently AND there is a small subset of "almost always accessed" columns and a set of "infrequently accessed columns" then vertical partitioning might make sense.
see
https://www.oracle.com/technetwork/issue-archive/2009/09-mar/o29asktom-098473.html "Wide Load Storage" heading
Vertical Partitioning
A reader, November 24, 2009 - 12:25 pm UTC
Hi Tom,
Thanks.
If we have a view (exposed to users) on vertical partitioned table, and user select columns from one table (most frequently used vertical table), do Oracle still need to join tables to get the result set?
Thanks
November 24, 2009 - 12:32 pm UTC
why don't you take a peek at that article I pointed to... All will be revealed there.