Home » Developer & Programmer » Designer » Converting to Column Oriented Architecture (Oracle 10g)
icon5.gif  Converting to Column Oriented Architecture [message #530813] Thu, 10 November 2011 14:47
eeriehunk
Messages: 16
Registered: May 2009
Junior Member
Hello All,
This is more a generic question towards Row-Based vs Column-Oriented Architecture and how to convert to a Columnar structure, so kindly bare with me on the literature.
I do like to give an scenario on why this tweaked my curiosity.

I work a lot on developing queries for reports which require lot of aggregation function like SUM.
Unfortunately I cant pre-aggregated them as the input Filters from the UI side can change.

For example: Say I have 5 filters (City, Gender, age, group, company_code)on the UI. The user can select any number or combinations of filters. Not necessary that a value has to be added to each filter. So, say the user selected, City = Omaha, Gender = M and Company_code = 2.
Based on the above I formulate the following query :
select Year, month, day, sum(visits), sum(cost), sum(visitors)
from visitors_tab
where city = 'Omaha' and gender = 'M' and Cmp_code = 2
group by Year, month, day;

Only the where clause changes based on the Filters selected. say if age is selected as 20+ then you will see another addition to where clause as "and age > 20".

I have very slow query performance so I was considering Column Oriented Architecture VS Row architecture as the table has 60 columns and I am only aggregation say 10 of them.

Question: How to convert a regular table to a column style.
Will that improve performance. If so how.

And guys, I have already tried partitions on Cmp_code with city and gender local bitmap index, and it improved performance for a 50 sec query to 25 sec. But I need a query that runs less then 10 sec.

I did research about Column Oriented ..but they explain what they are but could not find a way to convert a regular table to columnar structure tables and test performance.

Thank you,
Aj
Previous Topic: Database Design
Next Topic: How to export relationships from exiting database
Goto Forum:
  


Current Time: Thu Mar 28 12:44:28 CDT 2024