From what I remember of my reading about Sybase IQA a few years ago, data in such databases is stored as columns, rather than rows. Accessing a single column in it's entirety (ie, all the values of that column in that table) is much faster as very many values fit into a datablock -- the total I/O required for the column is singificantly reduced.
Thus, aggregations (SUM, AVERAGE) of values in the column are very quick.
Obviously, the downside is that multi-column queries and joins across multiple tables are likely to degrade very quickly.
Here is my simple simulation comparing a "regular" table with 2 single column "tables" holding only the desired columns for specific queries and nothing else.
SQL> @Simulate_Columnar_Database
SQL> spool Simulate_Columnar_Database
SQL>
SQL>
SQL> set SQLPrompt ''
set SQLContinue ''
set feedback off
REM Simulating Columnar Database
REM Such databases offer faster operations on single columns
REM Each column's data is stored separately
REM I want to get the average unit_price, the average_invoice_amount and the total sales_amount
REM Assuming that I am "loading" data from the source INVOICES_TABLE into my datawarehouse's target tables
REM -- INVOICES_TABLE has 15million rows for 42 different product_codes
REM I am using a Tablespace on an ASM Disk Group to avoid Linux FileSystem Caching
REM Also, my DB_CACHE_SIZE is small, so the autotrace should show high Physical Reads
REM My ETL loads data into 3 tables, 1 regular and 2 columnar
REM -- (although I am using a Table INVOICES_TABLE, we shall assume that the data is
REM coming from external files, via an ETL)
REM
REM ################################## Setup the Regular Table
REM Regular Table
drop table regular_table_sales purge;
create table regular_table_sales
2 (invoice_id number not null,
3 item_id number not null,
4 unit_price number not null,
5 sale_quantity number not null,
6 invoice_amount number not null,
7 description varchar2(32)
8 ) tablespace ASM_TBS ;
insert /*+ APPEND */ into regular_table_sales
2 select invoice_number, product_code, item_price, invoice_quantity, item_price*invoice_quantity,description from invoices_table;
commit;
REM ################################## Setup the Columnar Tables
REM Columnar "Table" for UNIT_PRICE
drop table columnar_table_unit_price purge;
create table columnar_table_unit_price (unit_price number not null) tablespace ASM_TBS;
insert /*+ APPEND */ into columnar_table_unit_price
2 select item_price from invoices_table;
commit;
REM Columnar "Table" for INVOICE_AMOUNT
drop table columnar_table_invoice_amounts purge;
create table columnar_table_invoice_amounts (invoice_amount number not null) tablespace ASM_TBS;
insert /*+ APPEND */ into columnar_table_invoice_amounts
2 select item_price*invoice_quantity from invoices_table;
commit;
REM ##############################################################################################
REM **********************************************************************************************
REM ##############################################################################################
REM ######################## Computations from the Regular Table
rem --- we don't need the Execution Plans as all our queries are FullTableScans
set timing on
select count(*) from regular_table_sales;
COUNT(*)
----------
15048693
Elapsed: 00:00:27.97
set autotrace on statistics
select avg(unit_price) from regular_table_sales;
AVG(UNIT_PRICE)
---------------
23.4853066
Elapsed: 00:00:21.31
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
85006 consistent gets
84967 physical reads
0 redo size
541 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
select avg(invoice_amount) from regular_table_sales;
AVG(INVOICE_AMOUNT)
-------------------
1163.06765
Elapsed: 00:00:07.69
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
85006 consistent gets
84967 physical reads
0 redo size
545 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
select sum(invoice_amount) from regular_table_sales;
SUM(INVOICE_AMOUNT)
-------------------
1.7503E+10
Elapsed: 00:00:07.00
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
85006 consistent gets
84950 physical reads
0 redo size
531 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
set timing off
set autotrace off
REM ######################## Computations from the Columnar Tables
rem --- we don't need the Execution Plans as all our queries are FullTableScans
set timing on
select count(*) from columnar_table_unit_price;
COUNT(*)
----------
15048693
Elapsed: 00:00:08.07
select count(*) from columnar_table_invoice_amounts;
COUNT(*)
----------
15048693
Elapsed: 00:00:08.39
set autotrace on statistics
select avg(unit_price) from columnar_table_unit_price;
AVG(UNIT_PRICE)
---------------
23.4853066
Elapsed: 00:00:04.27
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
22999 consistent gets
22969 physical reads
0 redo size
541 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
select avg(invoice_amount) from columnar_table_invoice_amounts;
AVG(INVOICE_AMOUNT)
-------------------
1163.06765
Elapsed: 00:00:03.28
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
23001 consistent gets
22975 physical reads
0 redo size
545 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
select sum(invoice_amount) from columnar_table_invoice_amounts;
SUM(INVOICE_AMOUNT)
-------------------
1.7503E+10
Elapsed: 00:00:02.82
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
23001 consistent gets
22954 physical reads
0 redo size
531 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
set timing off
set autotrace off
REM ######################## An Alternative to the Columnar Tables -- Indexes !
rem
create index rts_unit_price_ndx on regular_table_sales (unit_price) tablespace asm_tbs;
create index rts_invoice_amt_ndx on regular_table_sales (invoice_amount) tablespace asm_tbs;
exec dbms_stats.gather_table_stats('','REGULAR_TABLE_SALES',cascade=>TRUE);
set timing on
set autotrace on
select avg(unit_price) from regular_table_sales;
AVG(UNIT_PRICE)
---------------
23.4853066
Elapsed: 00:00:17.69
Execution Plan
----------------------------------------------------------
Plan hash value: 3888224904
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 5812 (6)| 00:01:10 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | INDEX FAST FULL SCAN| RTS_UNIT_PRICE_NDX | 15M| 42M| 5812 (6)| 00:01:10 |
--------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
29428 consistent gets
29405 physical reads
0 redo size
541 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
select avg(invoice_amount) from regular_table_sales;
AVG(INVOICE_AMOUNT)
-------------------
1163.06765
Elapsed: 00:00:11.53
Execution Plan
----------------------------------------------------------
Plan hash value: 193857525
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 5935 (6)| 00:01:12 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | INDEX FAST FULL SCAN| RTS_INVOICE_AMT_NDX | 15M| 57M| 5935 (6)| 00:01:12 |
---------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
31210 consistent gets
31192 physical reads
0 redo size
545 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
select sum(invoice_amount) from regular_table_sales;
SUM(INVOICE_AMOUNT)
-------------------
1.7503E+10
Elapsed: 00:00:04.37
Execution Plan
----------------------------------------------------------
Plan hash value: 193857525
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 5935 (6)| 00:01:12 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | INDEX FAST FULL SCAN| RTS_INVOICE_AMT_NDX | 15M| 57M| 5935 (6)| 00:01:12 |
---------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
31210 consistent gets
31192 physical reads
0 redo size
531 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
set timing off
set autotrace off
spool off
A tabular comparison of the specific queries :
Comparison by Execution Time
Query RegularTable ColumnarTable Index
AVG(UNIT_PRICE) 21.31sec 04.27sec 17.69sec
AVG(INVOICE_AMOUNT) 07.69sec 03.28sec 11.53sec
SUM(INVOICE_AMOUNT) 07.00sec 02.82sec 04.37sec
Comparison by Buffer Gets
Query RegularTable ColumnarTable Index
AVG(UNIT_PRICE) 85,006 blocks 22,999 blocks 29,428 blocks
AVG(INVOICE_AMOUNT) 85,006 blocks 23,001 blocks 31,210 blocks
SUM(INVOICE_AMOUNT) 85,006 blocks 23,001 blocks 31,210 blocks
Thus, for simple, single-column queries that access all occurrences (ie "all rows") of the column in the table, a Columnar design does perform better.
But I wonder if options like Parallel Query are available.
.
.
.
3 comments:
I think you missed the point of column based storage. In your example you still store a column of data across many rows. You would need to create many columns (one for each row in the source table) and store and entire column in a row. perhaps on a small table you could simulate this. Otherwise I do not see a way to simulate it in Oracle.
In my contribution in the forums thread I was talking of "skinny tables". Tables that, themselves, behave as if they were indexes on the columns.
And that is the simulation I presented. If a very high proportion of your queries were summaries (aggregation) on only 1 particular column in a relational table (which, otherwise has N -- 2 to tens - columns), then it would be simpler to "extract" that 1 column as a separate table.
Further in that thread, David Aldridge refers to using Materialized Views or bitmapped and compressed B-Tree indexes. As does Jonathan Lewis explain how a bitmapped index on every column would be used.
These are all different "simulations" or "representations".
I found this blog entry while Googling "oracle" and "columnar database".
I think Larry had better get on the ball with this one or he will be left at the station. SAP is moving strongly in this direction.
Column based storage and query allows a 90% data reduction and a thousandfold faster queries (partly because of the data reduction).
This isn't magic, it's just a more efficient way of storing the data. This allows more data to be kept in memory. When you combine the faster nature of columnar analysis with "in memory" queries, you get fantastically faster results.
Post a Comment