Saturday, October 27, 2012
Materialized view is one of the most useful features for data processing in Oracle. We would suggest that materialized views be used when possible. To create a materialized view, we use "create materialized view as select.. ", just as when we create a view or table. For example, the following SQL script creates a materialized view that calculates the following bank card summary information for each retailer terminal: the number of transactions, amount of transactions and number of unique cards. create materialized view mv_retailer_terminal_sts as select terminal_id , retailer_id , terminal_city, terminal_state , terminal_country, count(1) number_of_txn , sum(tran_amt) amount_of_transaction, count(distinct card_key) number_of_cards from credit_card_txn_detail a group by terminal_id , retailer_id , terminal_city, terminal_state , terminal_country; The materialized view captures a snapshot of the "from " tables. It can be refreshed later manually on demand or based on a defined schedule. For example, we can refresh a materialized view manually using the following command. execute DBMS_MVIEW.REFRESH('mv_retailer_terminal_sts','C'); ('C') means complete refresh. One of the advantages of using materialized view is that the SQL query used to produce it can be retrieved using the following query: select query from user_mviews where mview_name=upper('mv_retailer_terminal_sts'); The difference between a view and a materialized view is that a materialized view creates a physical table. Thus a materialized view is faster to query than a view. Thus we suggest that materialized views be used when possible in replacement of tables.
Sunday, October 21, 2012
Oracle function dbms_random.normal generates a standard normal distribution number with zero mean and one standard deviation. SQL> select dbms_random.normal from dual; NORMAL ---------- .781195382 The following query generates 6,000 random numbers using dbms_random.normal and then calculate their mean and standard deviation.We can see that their mean is close to zero and standard deviation close to one. SQL> select avg(v), stddev(v), count(1) from (select dbms_random.normal v from V_6K_OBS); AVG(V) STDDEV(V) COUNT(1) ---------- ---------- ---------- -.00110804 1.00954138 6000
Wednesday, October 03, 2012
The earlier post Random Sampling using SQL uses dbms_random.random to generate random numbers. A better function is dbms_random.value which generates uniformly distributed random number greater than or equal to 0 and less than 1. We can rewrite random sampling scripts as the following. Step 1 . Create random numbers. create table tbl_app_id_w_rnd as select app_id, dbms_random.value rnd from CELL_PHONE_SERVICE_APPS; Step 2. Create a view that contains 30% of the records. create view v_data_testing as select a.* from CELL_PHONE_SERVICE_APPS a, tbl_app_id_w_rnd b where a.app_id=b.app_id and rnd <=0.3;