Tim Gorman on database independence.
This thread on oracle-l began as a discussion on "Views being built on top of Views which are on top of Views". Sooner or later, remarks on Application Design, "Where the Logic should be" and on development platforms/languages ("Java" the PITA for many DBAs, even if loved by some DBAs ?) and "Database Independence" *had* to come in !.
I am an Oracle Database Specialist in Singapore.
Please note that this site uses cookies.
28 March, 2009
24 March, 2009
Columnar Databases
A recent discusssion thread on forums.oracle.com about columnar databases led me to run this very simple (unpretentious) simulation.
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.
A tabular comparison of the specific queries :
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.
.
.
.
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.
.
.
.
20 March, 2009
Materialized View on Prebuilt Table
As a follow up to my previous post on Materialized Views and Tables (in response to a comment), here I show a Materialized View on a PreBuilt Table. Once the MV is defined, I cannot execute DML on the PreBuilt Table. I *can* refresh the MV, resulting in the Table being refreshed.
The MV definition has to be the same as the underlying table, else I get an ORA-12060 error. Once the MV is built, any attempt at DML on the table returns an ORA-01732 error.
However, if I drop the MV, I can query the underlying table (still called "MY_OBJECTS") and execute DML on it :
The Table MY_OBJECTS still exists and shows the data as was present when it was refreshed as an MV, with the additional row from an INSERT as well.
However, I can no longer refresh it as a Materialized View :
Thus, an MV can be built on an underlying Table. Once the MV is built, manipulation is by way of MV Refresh and not directly on the Table. If the MV is dropped, it is only the MV definition that "goes away", the Table persists even with data.
(This could aleo be used as a way to "incrementally build a table").
.
.
SQL> connect test_user/test_user
Connected.
SQL> select count(*) from user_objects
2 /
COUNT(*)
----------
2
SQL> create table my_objects as select object_name, object_type, created from user_objects;
Table created.
SQL> select object_name, object_type, created from my_objects;
OBJECT_NAME OBJECT_TYPE CREATED
------------------------------ ------------------- ---------
SOURCE_TB_1 TABLE 16-MAR-09
MV_2 TABLE 16-MAR-09
MY_OBJECTS TABLE 20-MAR-09
SQL> select object_name, object_type, created from user_objects;
OBJECT_NAME OBJECT_TYPE CREATED
------------------------------ ------------------- ---------
SOURCE_TB_1 TABLE 16-MAR-09
MV_2 TABLE 16-MAR-09
MY_OBJECTS TABLE 20-MAR-09
SQL> create materialized view my_objects on prebuilt table as select * from user_objects;
create materialized view my_objects on prebuilt table as select * from user_objects
*
ERROR at line 1:
ORA-12060: shape of prebuilt table does not match definition query
SQL> create materialized view my_objects on prebuilt table as select object_name, object_type, created from user_objects;
Materialized view created.
SQL> select object_name, object_type, created from my_objects;
OBJECT_NAME OBJECT_TYPE CREATED
------------------------------ ------------------- ---------
SOURCE_TB_1 TABLE 16-MAR-09
MV_2 TABLE 16-MAR-09
MY_OBJECTS TABLE 20-MAR-09
SQL> create sequence a_sequence start with 1 increment by 1;
create sequence a_sequence start with 1 increment by 1
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> create table another_table (col_1 varchar2(5));
Table created.
SQL> select object_name, object_type, created from user_objects;
OBJECT_NAME OBJECT_TYPE CREATED
------------------------------ ------------------- ---------
SOURCE_TB_1 TABLE 16-MAR-09
MV_2 TABLE 16-MAR-09
MY_OBJECTS MATERIALIZED VIEW 20-MAR-09
MY_OBJECTS TABLE 20-MAR-09
ANOTHER_TABLE TABLE 20-MAR-09
SQL> select object_name, object_type, created from my_objects;
OBJECT_NAME OBJECT_TYPE CREATED
------------------------------ ------------------- ---------
SOURCE_TB_1 TABLE 16-MAR-09
MV_2 TABLE 16-MAR-09
MY_OBJECTS TABLE 20-MAR-09
SQL> insert into my_objects values ('A_DUMMY','TABLE',sysdate);
insert into my_objects values ('A_DUMMY','TABLE',sysdate)
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
SQL> exec dbms_mview.refresh('MY_OBJECTS','C');
PL/SQL procedure successfully completed.
SQL> select object_name, object_type, created from my_objects;
OBJECT_NAME OBJECT_TYPE CREATED
------------------------------ ------------------- ---------
SOURCE_TB_1 TABLE 16-MAR-09
MV_2 TABLE 16-MAR-09
MY_OBJECTS MATERIALIZED VIEW 20-MAR-09
MY_OBJECTS TABLE 20-MAR-09
ANOTHER_TABLE TABLE 20-MAR-09
SQL>
The MV definition has to be the same as the underlying table, else I get an ORA-12060 error. Once the MV is built, any attempt at DML on the table returns an ORA-01732 error.
However, if I drop the MV, I can query the underlying table (still called "MY_OBJECTS") and execute DML on it :
SQL> drop materialized view my_objects ;
Materialized view dropped.
SQL> insert into my_objects values ('A_DUMMY','TABLE',sysdate);
1 row created.
SQL> select object_name, object_type , created from my_objects;
OBJECT_NAME OBJECT_TYPE CREATED
------------------------------ ------------------- ---------
SOURCE_TB_1 TABLE 16-MAR-09
MV_2 TABLE 16-MAR-09
MY_OBJECTS MATERIALIZED VIEW 20-MAR-09
MY_OBJECTS TABLE 20-MAR-09
ANOTHER_TABLE TABLE 20-MAR-09
A_DUMMY TABLE 20-MAR-09
6 rows selected.
SQL> select object_name, object_type, created from user_objects;
OBJECT_NAME OBJECT_TYPE CREATED
------------------------------ ------------------- ---------
SOURCE_TB_1 TABLE 16-MAR-09
MV_2 TABLE 16-MAR-09
MY_OBJECTS TABLE 20-MAR-09
ANOTHER_TABLE TABLE 20-MAR-09
SQL>
The Table MY_OBJECTS still exists and shows the data as was present when it was refreshed as an MV, with the additional row from an INSERT as well.
However, I can no longer refresh it as a Materialized View :
SQL> exec dbms_mview.refresh('MY_OBJECTS','C');
BEGIN dbms_mview.refresh('MY_OBJECTS','C'); END;
*
ERROR at line 1:
ORA-23401: materialized view "TEST_USER"."MY_OBJECTS" does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2251
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2457
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2426
ORA-06512: at line 1
SQL>
Thus, an MV can be built on an underlying Table. Once the MV is built, manipulation is by way of MV Refresh and not directly on the Table. If the MV is dropped, it is only the MV definition that "goes away", the Table persists even with data.
(This could aleo be used as a way to "incrementally build a table").
.
.
Subscribe to:
Posts (Atom)