Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite
1

Just like the question asks; is it possible to transparently partition a table vertically, by using virtual partitions? I'd imagine if it was possible, that columns would just be stored in separate table spaces or some such.

In my specific environment, we have an application server that batch writes a large amount of data every 15 minutes or so. There is way more data than our reporting applications need. The primary fact table has roughly 50 columns. We use about 10 of them.

Is there any scenario (even if virtual vertical partitions do not exist), that such a scheme could improve performance? I'd imagine that having your data split across multiple tablespaces (and therefore potentially multiple disks) would improve the seek times.

share|improve this question
At least it is possible to write lob data to different tablespaces. – miracle173 Apr 6 '12 at 22:53

3 Answers

up vote 3 down vote accepted

I'd imagine that having your data split across multiple tablespaces (and therefore potentially multiple disks) would improve the seek times.

Your whole premise behind the question is faulty. You do not need to split your data between tablespaces to spread the i/o across more disks, you need to increase the number of disks in your RAID10 or (better still) ASM array. You will get less performance gain, less space efficiency and far more maintenance trying to manually tune the i/o like you are suggesting.

ASM beats RAID10 primarily because it understands the data being written to it - so for example it can vary the stripe size between data blocks and logs.

share|improve this answer
Then is there any real point of doing traditional vertical partitioning? – Josh Smeaton Apr 4 '12 at 11:08
Yes it can be helpful if it reduces the the size of the data-set you are scannning (ie you are only scanning a subset of the partitions). In certain unusual cases it is even necessary as most RDBMSs have a limit on the number of columns allowed (eg Oracle allows 1000) – Jack Douglas♦ Apr 4 '12 at 11:15
The point is to separate sets of columns into different blocks not into different tablespaces - apart from that your logic is sound. As @Phil says there is no way of doing this 'virtually' (aside from the fact that Oracle automatically does a similar thing if you have more than 256 columns) – Jack Douglas♦ Apr 4 '12 at 11:20
up vote 0 down vote

Afaik, there is no way you could split a part of columns of a table into another tablespace, except for LOB storage, where LOB data can be stored in separate tablespace.

As for performance - number of columns does not affect select statements as long as these columns don't appear in them.

On the other hand, splitting the table into partitions and proper indexing has significant impact on performance, which becomes more obvious with growing size of the table. (You did not describe your data, so noone can advise how exactly you should partition the table.)

Virtual partitioning means partitioning by an expression, which is stored as table metadata and does not appear in table itself. See documentation.

share|improve this answer
I was mainly thinking of the idea as theoretical, not exactly as a solution to a problem we're having. I wondered if the concept was actualised - defining a set of columns that are written to separate tablespaces rather than rows (horizontal partitioning..) – Josh Smeaton Apr 4 '12 at 7:45
It's not possible. You can, however, do something similar in Exadata by using Hybrid Column Compression. oracle.com/technetwork/middleware/bi-foundation/… – Phil Apr 4 '12 at 7:48
@Phil: What is the similarity between Hybrid Column Compression and the concept of writing columns of the same table to different tablespaces? – miracle173 Apr 6 '12 at 22:51
up vote 0 down vote

You could do this by splitting the table into two, putting the 10 columns that you report on (along with the primary key) in one table (and a separate tablespace) & the other 40 in another. You can then present a view that joins the two together as the primary table. This probably won't be doable without application changes though. To insert, use an INSERT ALL query or an INSTEAD OF trigger on the first table plus an INSERT on the second table.

Whilst technically possible, I'd question whether this is needed. You'd be better off wide striping over all of the disks (ASM does SAME - use it if possible) to increase the number of spindles & IOPS available, rather than effectively putting some disks out of action because they hold data that isn't used for reporting.

share|improve this answer
The tables (and the applications writing the data) are provided by a vendor and can not be changed. That's why I was wondering if the concept of a 'virtual partition' exists. – Josh Smeaton Apr 4 '12 at 7:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.