How to Generate Globally Unique Identifier on the Fly

Problem

How do we generate an identifier that is globally unique across databases and machines? A globally unique identifier is very useful. For example, if we mark a credit card transaction uniquely, we trace it in different stages including authorization, settlement, dispute etc.

Solution

In the earlier post Five Ways of Creating Unique Record Identifier For Oracle Tables we mentioned unique identifiers in Oracle can be created by using: 1. Oracle pseudocolumn rowid; 2. Oracle rownum; 3. row_number() function; 4. Sequence; 5. sys_guid().
The simplest solution to globally unique identifier is to call sys_guid() whenever we need to. It does not depend on a separate structure like sequence. Other mechanisms include rowid, rownum and row_number() are only guaranteed to be unique for a single table. I have seen a self-made globally unique identifier generator does not work 100% of the time.
The following query uses sys_guid function to generate globally unique identifier for every row in EMP table. Sys_guid returns 16 byte "raw" type.

SQL> select sys_guid() as gid, EMPNO, ENAME from EMP;

GID                                   EMPNO ENAME
-------------------------------- ---------- ----------
EB567FE077DC5427E040D00AD17F0769       7839 KING
EB567FE077DD5427E040D00AD17F0769       7698 BLAKE
EB567FE077DE5427E040D00AD17F0769       7782 CLARK
EB567FE077DF5427E040D00AD17F0769       7566 JONES
EB567FE077E05427E040D00AD17F0769       7788 SCOTT
EB567FE077E15427E040D00AD17F0769       7902 FORD
EB567FE077E25427E040D00AD17F0769       7369 SMITH
EB567FE077E35427E040D00AD17F0769       7499 ALLEN
EB567FE077E45427E040D00AD17F0769       7521 WARD
EB567FE077E55427E040D00AD17F0769       7654 MARTIN
EB567FE077E65427E040D00AD17F0769       7844 TURNER
EB567FE077E75427E040D00AD17F0769       7876 ADAMS
EB567FE077E85427E040D00AD17F0769       7900 JAMES
EB567FE077E95427E040D00AD17F0769       7934 MILLER

14 rows selected.

Oracle SQLPLUS Client Installation on Windows Troubleshooting

Problem

I wanted to install Oracle Instant Client On Windows for my laptop, a 32 bit system running Windows 7. I went to the Instant Client Downloads for Microsoft Windows (32-bit) and downloaded two zip files, instantclient-basic-nt-12.1.0.1.0.zip and instantclient-sqlplus-nt-12.1.0.1.0.zip. I unzipped them under the same directory. When I ran sqlplus, nothing happened.

$
$ sqlplus myuser/mypasswrod@192.168.1.5:1521/xe
$

Solution

Under the directory that I unzipped the downloaded files, I found a number of executable file such as adrci.exe in addition to sqlplus.exe. I ran adrci.exe and got the following error message.

$adric.exe 
"error while loading shared libraries: MSVCR100.dll"

After doing some research about MSVCR100.dll on the internet, I went to Microsoft Download Center: Microsoft Visual C++ 2010 Redistributable Package (x86). Once I downloaded and install the package, I can run sqlplus and connect to the database server without issue.

$ sqlplus myuser/mypasswrod@192.168.1.5:1521/xe

SQL*Plus: Release 12.1.0.1.0 Production on Sat Nov 16 13:07:04 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL>

I have written a newer post about the installation Oracle SQLPLUS Client Installation on Windows 64.

Add Line Number to Text File

Problem

We have the following text file.

$ cat ads_log_small.csv
ADS_ID,DEVICE_OS,NUM_IMPRESSION,NUM_CONVERSION
 32, Android, 1, 0
 32, Android, 1, 0
 32, Android, 1, 0
 32, Android, 2, 0
 32, Android, 1, 0
 32, Android, 2, 0
 32, Android, 1, 0
 32, Android, 3, 0
 32, Android, 2, 0

If we load records in the text file into a database table, the original order of records in the file is lost. In the post SAS data set vs. relational database table, we mentioned that,Unlike SAS data set, a database table is a set where records no order (unless we explicitly sort them by keys). If we want to preserve the original sequence of the record, how do we add a line number to the original file?

Solution

We can use Linux (or Cygwin on Windows) command awk to add a line number to the file.

$ cat ads_log_small.csv | awk '{if (NR==1) print "LINE_NUMBER" ,",",$0;    else print NR-1,",",$0}'
LINE_NUMBER , ADS_ID,DEVICE_OS,NUM_IMPRESSION,NUM_CONVERSION
1 ,  32, Android, 1, 0
2 ,  32, Android, 1, 0
3 ,  32, Android, 1, 0
4 ,  32, Android, 2, 0
5 ,  32, Android, 1, 0
6 ,  32, Android, 2, 0
7 ,  32, Android, 1, 0
8 ,  32, Android, 3, 0
9 ,  32, Android, 2, 0

We can write the output to a new file.

$ cat ads_log_small.csv | awk '{if (NR==1) print "LINE_NUMBER" ,",",$0;    else print NR-1,",",$0}' > ads_log_new.csv

How to Find Out the Cutoff Value for Certain Percentile

Problem

We want to find out what answers to questions like:
What is the salary that top 1 percent of people are making?
What is the credit score that the lowest 15% have?

What is the cutoff predictive risky score that will raise alerts on the riskiest 0.1% of credit card transaction?

Solution

This problem can be solved using the same approach as described in Find the cutoff value for the top n records. We can always convert the percentile to the top-n-records (or bottom-n-records) by multiplying the size of the table and the percentile. However, there is a simpler way to do this. We can use Oracle function percentile_disc() to return the cutoff value given a certain percentile and ordering. Let's use a table that contains 100 random numbers as an example.
SQL> select * from TBL_100_RND where rownum <10;

NUM
-.58413539
1.31513578
2.16901993
.596910933
.208190221
.179899195
-.63028597
.525443855
-.52158424

The following query returns the cutoff value (v) for the top 10%.
SQL> select percentile_disc(0.1) within group(order by num desc) as cutoff from TBL_100_RND;

cutoff
1.13239998

The following query verifies that the cutoff value 1.13239998 indeed produces 10% of the records.
SQL> select count(1) from TBL_100_RND where num>=1.13239998;

COUNT(1)
10

How to Change Delimiters of Text File

Problem

A text file may contain many fields separated by delimiters. The delimiters could be tab, pipe(|), comma, etc. The following is an example of such a file. It has 4 fields separated by pipe (|). Some utilities only correctly recognize certain type of delimiters. How do we change the delimiter from one type to another, e.g., from pipe (|) to comma(,)?

$ head ads_log.txt
ADS_ID|DEVICE_OS|NUM_IMPRESSION|NUM_CONVERSION
 32| Android| 1| 0
 32| Android| 1| 0
 32| Android| 1| 0
 32| Android| 2| 0
 32| Android| 1| 0
 32| Android| 2| 0
 32| Android| 1| 0
 32| Android| 3| 0
 32| Android| 2| 0

Solution

We can use an editor such as notepad to do character replacement. However, if the file size is very big, using editor to do the work is very slow. A better way would be using Unix/Linux command tr.If we are running Windows system, we can install free cygwin which simulates Unix.

$ cat ads_log.txt | tr '|' ','
ADS_ID,DEVICE_OS,NUM_IMPRESSION,NUM_CONVERSION
 32, Android, 1, 0
 32, Android, 1, 0
 32, Android, 1, 0
 32, Android, 2, 0
 32, Android, 1, 0
 32, Android, 2, 0
 32, Android, 1, 0
 32, Android, 3, 0
 32, Android, 2, 0

We can write the output to the resulting file ads_log.txt.

$ cat ads_log.txt | tr '|' ',' > ads_log2.txt

Caculate Time Elapsed Between Two Dates

Problem

We want to find out how many seconds (or hours or days) elapsed between two time stamps. In building predictive models, variables like time since last credit card transaction, length of employment, etc., could be very useful in predicting fraudulent credit card transaction or claims. In the following example, we have a transaction begin and end time in our table. We want to find out how many minutes it takes to process a transaction.
SQL> select txn_id, to_char(txn_begin, 'YYYYMMDDHH24:MI:SS') txn_begin, to_char(txn_end, 'YYYYMMDDHH24:MI:SS') text_end from tbl_txn_small where ro wnum <=1;

TXN_ID	TXN_BEGIN	TEXT_END
1001	2013110509:21:03	2013110509:27:01

Solution

The days elapsed between two time stamps can be easily done by subtracting the end time from the beginning time. To convert the elapsed days in minutes or seconds we simple multiple it by 24X60 or 24X60X60.

SQL> select txn_id, to_char(txn_begin, 'YYYYMMDDHH24:MI:SS') txn_begin, to_char(txn_end, 'YYYYMMDDHH24:MI:SS') text_end, (txn_end-txn_begin)*24 hou rs_elpased, (txn_end-txn_begin)*24*60 minutes_elapsed, (txn_end-txn_begin)*24*60*60 sec_elapsed from tbl_txn_small where rownum <=1;

TXN_ID	TXN_BEGIN	TEXT_END	HOURS_ELPASED	MINUTES_ELAPSED	SEC_ELAPSED
1001	2013110509:21:03	2013110509:27:01	.099444444	5.96666667	358

"Fix" a Text File that Is in Unix Format

Problem

We expect a text file to be in the format similar to the following.

ADS_ID,DEVICE_OS,NUM_IMPRESSION,NUM_CONVERSION
 32, Android, 1, 0
 32, Android, 1, 0
 32, Android, 1, 0
 32, Android, 2, 0
 32, Android, 1, 0
 32, Android, 2, 0
 32, Android, 1, 0
 32, Android, 3, 0
 32, Android, 2, 0

However, when we open it in Microsoft Notepad, it look likes strange as shown below. The records are all in one line. How do we fix the text file that look "broken" in notepad?

Solution

This is most likely due to that the file is stored in Unix format instead of Windows.The line breaker for Unix text file is one character 10 ('\n' or '0A' in hexadecimal). While Windows text file uses two characters as the line breakers 10 and 14('\n\r', or '0A0D'). We can find out if a text file is in Unix or Windows format using the Linux command "od" (on Windows computer we can install free cygwin that allows us the use those Linux commands like "od")
$ cat ads_log_small.txt | od -c

0000000   A   D   S   _   I   D   ,   D   E   V   I   C   E   _   O   S
0000020   ,   N   U   M   _   I   M   P   R   E   S   S   I   O   N   ,
0000040   N   U   M   _   C   O   N   V   E   R   S   I   O   N  \n
0000060   3   2   ,       A   n   d   r   o   i   d   ,       1   ,
0000100   0  \n       3   2   ,       A   n   d   r   o   i   d   ,
0000120   1   ,       0  \n       3   2   ,       A   n   d   r   o   i
0000140   d   ,       1   ,       0  \n       3   2   ,       A   n   d
0000160   r   o   i   d   ,       2   ,       0  \n       3   2   ,
0000200   A   n   d   r   o   i   d   ,       1   ,       0  \n       3
0000220   2   ,       A   n   d   r   o   i   d   ,       2   ,       0
0000240  \n       3   2   ,       A   n   d   r   o   i   d   ,       1
0000260   ,       0  \n       3   2   ,       A   n   d   r   o   i   d
0000300   ,       3   ,       0  \n       3   2   ,       A   n   d   r
0000320   o   i   d   ,       2   ,       0  \n
0000332 

We see that the line breaker is a single character \n. We can fix the by converting it to Windows format using Linux (or cyswin on Windows) command unix2dos.

$ unix2dos ads_log_small.txt
unix2dos: converting file ads_log_small.txt to DOS format ...

We run "od" command again. It shows that the line breakers are two characters \r\n. Now notepad can display the file correctly. We can convert a Widows text file back into a Unix file using dos2unix command.

$ cat ads_log_small.txt | od -c

0000000   A   D   S   _   I   D   ,   D   E   V   I   C   E   _   O   S
0000020   ,   N   U   M   _   I   M   P   R   E   S   S   I   O   N   ,
0000040   N   U   M   _   C   O   N   V   E   R   S   I   O   N  \r  \n
0000060       3   2   ,       A   n   d   r   o   i   d   ,       1   ,
0000100       0  \r  \n       3   2   ,       A   n   d   r   o   i   d
0000120   ,       1   ,       0  \r  \n       3   2   ,       A   n   d
0000140   r   o   i   d   ,       1   ,       0  \r  \n       3   2   ,
0000160       A   n   d   r   o   i   d   ,       2   ,       0  \r  \n
0000200       3   2   ,       A   n   d   r   o   i   d   ,       1   ,
0000220       0  \r  \n       3   2   ,       A   n   d   r   o   i   d
0000240   ,       2   ,       0  \r  \n       3   2   ,       A   n   d
0000260   r   o   i   d   ,       1   ,       0  \r  \n       3   2   ,
0000300       A   n   d   r   o   i   d   ,       3   ,       0  \r  \n
0000320       3   2   ,       A   n   d   r   o   i   d   ,       2   ,
0000340       0  \r  \n
0000344

Unlike Notepad, Microsoft WordPa is able to display text files in both Unix and Windows formats correctly.

Five Ways of Loading Text Files Into Oracle Database

Problem

Often we need to load text files, such as comma delimited files, into Oracle databases as tables.

Solution

The following are five ways to do it, i.e., external table, SQL loader, SQL insert, Oracle SQL Developer Import Data function and Oracle Apex Load Data. Actually, the real options are only the first three, external table, SQL loader, and SQL insert. SQL Developer and Apex use of the three options to import files. Of course, there are more than five ways to do it. For example, we can also use third party tools such ETL utilities, Microsoft Access, R etc. to load data into the database through ODBC connections.

External Table

We have described how to define external table in the database that points to text files in post Analyze Text Files in Real Time Using SQL Without Loading Them into Database. Once a external table is defined, we can simply define a permanent in database table using CTAS "create table as select". For example, we create a database table for the external TBL_DATA1_EXT as the following.

SQL> create table tbl_data1_real as select * from TBL_DATA1_EXT;

The above SQL statement creates a table of the same format as the external table and physically load the data into it. This is my favorite way of loading data. Using this approach, I am able to perform some analyze on the external tables and make sure them look right before I load them.

SQL Loader

We follow the three steps to use SQL loader to import data: 1.create a table; 2. Write a control file; and 3. run sqlldr to load the file.

Step 1. Create table using SQL.
create table tbl_data1_real
(
ads_id number,
device_os varchar2(32),
num_impression number,
num_click number
);
Step 2. Compile a control file like the following.
load data
infile 'c:\\projects\\log\\ads_log.csv'
append
into table TBL_DATA1_REAL
fields terminated by '|'
OPTIONALLY ENCLOSED BY '"' AND '"'
trailing nullcols
( ADS_ID ,
DEVICE_OS,
NUM_IMPRESSION,
NUM_CLICK
)
Step 3. Load the file into the table.
$ sqlldr user/password@localhost:1521/xe CONTROL=ads_log2.ctl skip=1
SQL*Loader: Release 11.2.0.1.0 - Production on Tue Nov 12 07:04:25 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 64
Commit point reached - logical record count 128
Commit point reached - logical record count 192
Commit point reached - logical record count 256
Commit point reached - logical record count 320
Commit point reached - logical record count 384
......................

SQL Insert

Please see my post A Quick Way to Import Spreadsheet Into a Relational Database. We simply generate SQL insert statements for our data and run them. It is a great way o quickly import text files of small size.

Oracle SQL Developer Import Data

Oracle SQL Developer is a free development tool. We first create the destination table using SQL statement mentioned in the above section SQL Loader. Within SQL Developer, we select the table can right click to select Import Data.
There are three options to load the data, Insert Scripts, Staging External Table, and SQL Loader Utility as shown below. We can pick the one we like.

Oracle Apex Text File Import

With Apex 4.2, we open the web browser and log onto the admin account for the workspace. We then go to SQL Workshop/Utilities/Data Workshp. From there, we can use the data load function.

Conclusions

We described five ways of importing text files into Oracle database tables. My favorite way is Oracle external table because I can run SQL queries against the files to validate them before they are physically imported. Of course, there are more than five ways to do it. For example, we can also use third party tools such ETL utilities, Microsoft Access, R etc. to load data into the database through ODBC connections.

More on How to Find the Most Important Variables for a Predictive Model

Problem

To make a predictive model, we need independent variables as inputs and a single variable as the target. Typically, both independent and target variables are stored in a single table. Often there are many independent variables, say 50 or 200 of them, such as age, sex, annual income, credit limits, and transaction variables etc. How do we select a small number of variables that are most predicative of the target variable and use them to build a model that is robust?

Solution

In the early post Find the Most Important Variables In Predictive Models, we described that there is a drawback to justify the importance of variable individually. Ideally we should take a set of variables as a whole into consideration. One of the good approaches is Oracle’s Attribute Importance model.

I built a credit card transaction data that contains column is_fraud as the target, id as the unique record identifier and other independent variables that I want to analyze. The data set is v_training_set. I write the following PL/SQL script to build an attribute importance model.

begin
DBMS_DATA_MINING.CREATE_MODEL(
model_name => 'VAR_IMPORTANCE',
mining_function => DBMS_DATA_MINING.ATTRIBUTE_IMPORTANCE,
data_table_name => 'v_training_set',
case_id_column_name => 'id',
target_column_name => 'is_fraud');
END;

When the attribute importance model,VAR_IMPORTANCE, is done, all independent, i.e., all variables except target and record identifiers, are assigned an importance value. The higher the value of a variable, the more important it is in predicting the target. We can review our result using the following SQL. (I deliberately masked the independent variable names because I think fraud detection is an sensitive matter and we do not want to give away too much information to fraudsters who might be reading this blog post.)

SQL> select attribute_name, IMPORTANCE_VALUE, rank from TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_AI('VAR_IMPORTANCE')) order by rank;

ATTRIBUTE_NAME	IMPORTANCE_VALUE	RANK
VAR__EMV___	.024118198	1
VAR__TER___	.014412195	2
VAR__RET___	.013569008	3
VAR__PT____	.008679484	4
VAR__TRA___	.008009003	5
VAR__TER___	.007207152	6
VAR__CHK___	.006322795	7
VAR__NRT___	.00591138	8
VAR__NRT___	.005564262	9
VAR__MSG___	.005332518	10
VAR__TRA___	.004457798	11
VAR__NRT___	.00409855	12
VAR__PIN___	.003852347	13
VAR__PRO___	.001177829	14
VAR__RES___	.000911889	15
VAR__TER___	.000767663	16
VAR__FIL___	.000448031	17
VAR__ACC___	.000172331	18
VAR__PRM___	.00009502	19
VAR__AUT___	.00009502	19
VAR__FRW___	.00009502	19
VAR__AUT___	.000092784	20
VAR__ACC___	.000012229	21
VAR__PT____	.000003078	22
VAR__ACC___	-6.946E-06	23
VAR__TRA___	-.02799773	24
VAR__TER___	-.16968261	25
VAR__TRA___	-.39228472	26
VAR__TIE___	-.57372309	27

Conclusion

Oracle’s Attribute Importance function ranks variables based on their importance in predicting the target. It is a great tool for selecting a small number of input variables out of many before we build a predictive model.

Analyze Text Files in Real Time Using SQL Without Loading Them into Database

Problems

A log file that records the number of impressions and number of clicks for advertisements are constantly updated (growing). The file looks like the following:

ADS_ID|DEVICE_OS|NUM_IMPRESSION|NUM_CONVERSION
32| Android| 1| 0
32| Android| 1| 0
32| Android| 1| 0
32| Android| 2| 0
32| Android| 1| 0
32| Android| 2| 0
32| Android| 1| 0
32| Android| 3| 0
32| Android| 2| 0

We want to generate real time reports about the summarized performance of advertisements, such as the click through rate by ads_id, click through rate by operation system and ads_id, etc. The reports should reflect the real time changes in the log file.

Solutions

One of the best solutions is to use an Oracle external table and view. There are two tasks to be performed.

Task 1. Define an external table on the log file. An external table is just a pointer to the location of file and definition of its format. The file itself is not loaded into the database as a permanent table. Once the external table is defined, we can query it using SQL just like regular table. The data is read by Oracle on the fly. Thus any changes in the file will be reflected on the query result.

Task 2. Define views to summarize the external table and produce reports. Since views just store the process logic and only produce the output when we query it, the content of views always reflects the latest information in the external table which in turn captures the changes in the log file.

Task 1.Define an external table.

Step 1. If not yet, we need to assign "create any directory" privilege to current user.

Log in as the system user and run the following command under sqlplus.

SQL> grant create any directory to current_user;

Step 2. We create directory. It is assumed that the Oracle database server has the access to the directory where the log file is located.

Log in as the current user and create an directory.

SQL> create directory dir_files as '/home/log/data';

Step 3. We define the external table that points to the log file.

create table tbl_data1_ext
(
ads_id number,
operation_sys varchar2(32),
num_impression number,
num_click number
)
organization external
( type oracle_loader
default directory dir_files
access parameters
( records delimited by newline
skip 1
fields terminated by '|'
missing field values are null
)
location('ads_log.txt')
);
Once it is done, we can verify if the file is define correctly.
SQL> select count(1) from TBL_DATA1_EXT;

COUNT(1)
165201

We use Linux command wc to count the number of lines in the file. The text file has one more line which is the header. The header was skipped when we define the external table.

$ wc -l ads_log.txt
165202 ads_log.txt

Task 2. Create views. Once we have the external table, we can create views to summarized it.

SQL> create view v_ctr_for_ads as select ads_id, sum(NUM_IMPRESSION) num_impression, sum(NUM_CLICK) NUM_CLICK, sum(NUM_CLICK)/sum(NUM_IMPRESSION) clr from TBL_DATA1_EXT group by ads_id;
View created.

SQL> create view v_ctr_for_ads_os as select ads_id, OPERATION_SYS, sum(NUM_IMPRESSION) num_impression, sum(NUM_CLICK) NUM_CLICK, sum(NUM_CLICK)/su m(NUM_IMPRESSION) clr from TBL_DATA1_EXT group by OPERATION_SYS, ads_id;
View created.

The user can look at views the get the summary information about the log file in real time.
SQL> select * from v_ctr_for_ads where num_impression>100 order by clr desc;

ADS_ID	NUM_IMPRESSION	NUM_CLICK	CLR
32	4116	5	.001215

SQL> select * from v_ctr_for_ads_os where num_impression>100 order by clr desc;

ADS_ID	OPERATION_SYS	NUM_IMPRESSION	NUM_CLICK	CLR
32	Android	3360	5	.001488
32		756	0	0

Conclusions

With Oracle external tables that are just pointers to the location of text files and definition of file formats, we can perform SQL queries against text files without loading them into the database as physical database tables. It is a great solution when we want to repeatedly analyze text files that are constantly changing. Combining external tables and views, we can get query results that reflect the most recent content of text files. This solution is also very "clean" since there is no permanent database tables created.

Insert Records Into an Oracle Table and Rollback

After we insert records into a table, we can rollback and undo the changes. However, if we run any DDL queries afterwards such as "create table", "create view", "dbms_stats.gather_table_stats" etc., the data inserted are committed and can not be rollback. The following are some examples.

SQL> create table tbl_a (num number);
Table created.

SQL> insert into tbl_a values(1);
1 row created.

SQL> select * from tbl_a;

NUM
1

SQL> commit;
Commit complete.

We insert a new record and can rollback.

SQL> insert into tbl_a values(2);
1 row created.

SQL> select * from tbl_a;

NUM
1
2

SQL> rollback;
Rollback complete.

SQL> select * from tbl_a;

NUM
1

We insert another new record.

SQL> insert into tbl_a values(2);
1 row created.

We create a view on the table.

SQL> create view v_tbl_a as select * from tbl_a;
View created.

Because of "create view", data inserted is committed and can not be rolled back.

SQL> rollback;
Rollback complete.

SQL> select * from tbl_a;

NUM
1
2

We insert another new value.

SQL> insert into tbl_a values(3);
1 row created.

We run dbms_stats.gather_table_stats(). The data inserted can not be rolled back.

SQL> exec dbms_stats.gather_table_stats(null,'tbl_a');
PL/SQL procedure successfully completed.

SQL> rollback;
Rollback complete.

SQL> select * from tbl_a;

NUM
1
2
3

We insert another new value.

SQL> insert into tbl_a values(4);
1 row created.

We create a new table. Because "create table" is a DDL statement, we can not roll back the inserted data even if it is inserted into a different table.

SQL> create table tbl_xb (value varchar2(32));
Table created.

SQL> rollback;
Rollback complete.

SQL> select * from tbl_a;

NUM
1
2
3
4

We insert another new value.

SQL> insert into tbl_a values(5);
1 row created.

We run dbms_stats.gather_table_stats() on tbl_xb and we can not roll back the inserted data even if it is inserted into a different table.

SQL> exec dbms_stats.gather_table_stats(null,'tbl_xb');
PL/SQL procedure successfully completed.

SQL> rollback;
Rollback complete.

SQL> select * from tbl_a;

NUM
1
2
3
4
5

We insert another new value.

SQL> insert into tbl_a values(6);
1 row created.

We create a view on tbl_xb. As a result, data inserted into tbl_a is committed and can not be rolled back.

SQL> create view view_tbl_xb as select * from tbl_xb;
View created.

SQL> rollback;
Rollback complete.

SQL> select * from tbl_a;

NUM
1
2
3
4
5
6

6 rows selected.

Find the cutoff value for the top n records

It is a very common task to find the cutoff value to get the top N records . The following are some of the examples:
1. Find the cutoff salary for the top 10 employees in a company.
2. Find a cutoff score for a risk model that generates alerts for the top 100 riskiest transactions.

We use the following table that has 20 records as an example.

SQL> select id, num from TBL_20 order by id;

ID	NUM
1	-.650222
2	-1.465297
3	-.689485
4	-1.547403
5	-1.791099
6	-1.270857
7	.988116
8	1.246141
9	.643606
10	-.515888
11	-.713859
12	-.587674
13	-1.634403
14	1.285847
15	-.08049
16	.231295
17	-.66065
18	.422664
19	-.134565
20	-1.773186

20 rows selected.

If we want to find out the cutoff value for the largest 5th column "num", we first use function row_number() to generate rank and then select the num that has a rank of 5.

SQL> with tbl as (select a.*, row_number() over(order by num desc) rnk from tbl_20 a) select num from tbl where rnk=5;

NUM
.422664

To verify that .422664 is indeed the cutoff value for the top 5 records, we run the following query.

SQL> select * from tbl_20 where num>=.422664 order by num desc;

NUM	ID
1.285847	14
1.246141	8
.988116	7
.643606	9
.422664	18

It is a good practice to always verify our results using another query. That way, the chance of making mistakes is greatly reduced.

Create a partitioned table from an existing table

Partitioned tables are extremely powerful to manage large data. We can combine "create table as select" and "partition by" to build a new partitioned table based on an existing table.

For example, we have a transaction table that includes account_number and transaction date. We can create a partitioned table that has one partition for each day.

SQL> create table tbl_txn_par_by_day partition by range(txn_date) interval(numtodsinterval(1,'day')) (partition p0 values less than (to_date('20131 001','YYYYMMDD'))) as select * from tbl_txn;
Table created.

Or we can create a partitioned table that has 20 partitions based on the hash value of account numbers.

SQL> create table tbl_txn_par_by_acct_num partition by hash(account_number) partitions 20 as select * from tbl_txn;
Table created.

We can find out the partition names.

SQL> select table_name, partition_name, high_value from user_tab_partitions where table_name='TBL_TXN_PAR_BY_DAY';

TABLE_NAME	PARTITION_NAME	HIGH_VALUE
TBL_TXN_PAR_BY_DAY	P0	TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TBL_TXN_PAR_BY_DAY	SYS_P45	TO_DATE(' 2013-10-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

We run SQL query against specific partitions.

SQL> select count(1) from TBL_TXN_PAR_BY_DAY partition(SYS_P45);

COUNT(1)
100125

Using partition tables, I was able to perform complex analysis on 50 million bank card transactions, including fuzzy matching multiple tables,etc., on a $600 desktop PC.

Categorical Variables in Logistic Regression

In the old post Build Predictive Models Using PL/SQL, we showed how to call DBMS_DATA_MINING.CREATE_MODEL() function to build a logistic regression model.

The input data set should contain (and only contain) the following columns:
1. a unique case id;
2. the target variable;
3. independent variables used in the model. All variables other than the case id and target variables will be used as the input variables to the model.

We can easily construct the input data set using view based on a data table. In the view, we specify case id, target variable and independent variables that we desire in the select part of the SQL.

Independent variables are either numeric or character types. Character types, such as state name or male/female, are categorical variables. Oracle models automatically treat the most frequent categorical value as the reference class and assign it a weight of zero. This is very convenient. For example, we built a model that use transaction code as one of the input variables as mentioned in post Logistic Regression Model Implemented in SQL. Take a look at the piece of SQL code below. It converts the txn_code into weight derived from a logistic regression model. substr((TXN_CODE),1,18) is to only take the first 18 characters of txn_code (just in case the txn_code is too long). nvl() is to treat missing value as a blank. txn_code 'XX' will receive a weight of -.070935, NULL or blank value a weight of -.330585. If there is a new txn_code in production that is unseen in the training data set, say 'ZZZ', it will receive a default weight of 0 which is the weight for the most frequent txn_code. This makes sense as we can assume that the unseen code share the weight of historically most common codes. Thus, the model can produce a score (that is reasonable) under any circumstance. This example also shows that it is important to design the model that can handle unseen situations after it is deployed.

decode(nvl(substr((TXN_CODE1),1,18),' '),
'XX',-.070935,
'57',-.192319,
'1',-.053794,
'81',-.010813,
'NR',-.079628,
'PD',-.102987,
'P',-1.388433,
'Z6',-.106081,
'01',-1.1528,
'Z4',-.004237,
'T1',.697737,
'AK',-.490381,
'U2',.063712,
'NK',.054354,
'PR',.205336,
'51',-.286213,
'N',.075582,
' ',-.330585,
0)

The above SQL code that converts values to weights is not necessary normally. Instead, we use the model mining object and prediction_probability function. I took this approach was simply that the database administers of the production databases were unaware of Oracle mining objects and felt not comfortable using them. Thus, to be able to deploy our predictive models into production systems, data miners need to flexible. I have seen to many good models built in labs that never got deployed.

A Quick Way to Import Spreadsheet Into a Relational Database

I just made a youtube video to show readers how to do this. We often need to import data from Excel spreadsheet, such as the one shown below, into a database.

A quick way that works for any databases is to simply generate SQL insert statements for those rows using formula similar to: Formula: =CONCATENATE("insert into tbl_dataset values(",A2,",","'",B2,"');") as shown in column C of the picture below.

Formula: =CONCATENATE("insert into tbl_dataset values(",A2,",","'",B2,"');")

We create a destination table. Then we copy those "insert into " statements from Excel spreadsheet and paste them into SQL client tool such as SQL Developer or SQLPLUS to run them. Do not forget to commit the inserts.This approach has advantages: 1. It works for any relational databases as the insert statements are standard SQL (if not can adjust the spreadsheet formula slightly). 2. It does not require any data import tools. All we need are Excel spreadsheet and a SQL client to run the create table and insert statements.

SQL> create table tbl_dataset (col1 number, col2 varchar2(8));
Table created.

Run the following insert statements. If there are many lines, we can put them in a script file and run the script file.

insert into tbl_dataset values(1,'A');
insert into tbl_dataset values(2,'B');
insert into tbl_dataset values(3,'C');
insert into tbl_dataset values(4,'D');
insert into tbl_dataset values(5,'E');
insert into tbl_dataset values(6,'F');
insert into tbl_dataset values(7,'G');
insert into tbl_dataset values(8,'H');
insert into tbl_dataset values(9,'I');

SQL> insert into tbl_dataset values(1,'A');
1 row created.
SQL> insert into tbl_dataset values(2,'B');
1 row created.
SQL> insert into tbl_dataset values(3,'C');
1 row created.
SQL> insert into tbl_dataset values(4,'D');
1 row created.
SQL> insert into tbl_dataset values(5,'E');
1 row created.
SQL> insert into tbl_dataset values(6,'F');
1 row created.
SQL> insert into tbl_dataset values(7,'G');
1 row created.
SQL> insert into tbl_dataset values(8,'H');
1 row created.
SQL> insert into tbl_dataset values(9,'I');
1 row created.

Do not forget to commit the changes.

SQL> commit;
Commit complete.

Data are imported into the database.

SQL> select * from tbl_dataset;

COL1	COL2
1	A
2	B
3	C
4	D
5	E
6	F
7	G
8	H
9	I

9 rows selected.

Generate SQL Create Table/View Queries for Existing Tables/Views

Sometimes, it is useful to keep a copy of the DDL statements, i.e., create table/view, for all or some of the tables/views so that we can recreate them. Function dbms_metadata.get_ddl() can be used here.

SQL> select dbms_metadata.get_ddl('VIEW','V_6K_OBS') from dual;

DBMS_METADATA.GET_DDL('VIEW','V_6K_OBS')

CREATE OR REPLACE FORCE VIEW "BDM"."V_6K_OBS" ("TABLE_NAME", "COLUMN_NAME", " DATA_TYPE", "DATA_TYPE_MOD", "DATA_TYPE_OWNER", "DATA_LENGTH", "DATA_PRECISION", "DATA_SCALE", "NULLABLE", "COLUMN_ID", "DEFAULT_LENGTH", "DATA_DEFAULT", "NUM_D ISTINCT", "LOW_VALUE", "HIGH_VALUE", "DENSITY", "NUM_NULLS", "NUM_BUCKETS", "LAS T_ANALYZED", "SAMPLE_SIZE", "CHARACTER_SET_NAME", "CHAR_COL_DECL_LENGTH", "GLOBA L_STATS", "USER_STATS", "AVG_COL_LEN", "CHAR_LENGTH", "CHAR_USED", "V80_FMT_IMAG E", "DATA_UPGRADED", "HISTOGRAM") AS select "TABLE_NAME","COLUMN_NAME","DATA_ TYPE","DATA_TYPE_MOD","DATA_TYPE_OWNER","DATA_LENGTH","DATA_PRECISION","DATA_SCA LE","NULLABLE","COLUMN_ID","DEFAULT_LENGTH","DATA_DEFAULT","NUM_DISTINCT","LOW_V ALUE","HIGH_VALUE","DENSITY","NUM_NULLS","NUM_BUCKETS","LAST_ANALYZED","SAMPLE_S IZE","CHARACTER_SET_NAME","CHAR_COL_DECL_LENGTH","GLOBAL_STATS","USER_STATS","AV G_COL_LEN","CHAR_LENGTH","CHAR_USED","V80_FMT_IMAGE","DATA_UPGRADED","HISTOGRAM" from user_tab_columns where rownum <=6000

SQL> select dbms_metadata.get_ddl('TABLE','MV_UNIVAR_STS') from dual;

DBMS_METADATA.GET_DDL('TABLE','MV_UNIVAR_STS')

CREATE TABLE "BDM"."MV_UNIVAR_STS" ( "FILENAME" VARCHAR2(32), "C" NUMBE R, "TOT" NUMBER, "TOT_DIS" NUMBER, "MI_VAL" VARCHAR2(512), "MX_VAL" VARC HAR2(512) ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MA XTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "BDM"

Generate SQL Queries "Automatically"

We do not have to write every SQL query manually. It is very efficient to generate SQL statements "automatically" using queries. Of course, we can use SQL queries to generate of statements in other programming languages such C/C++. This was what I did when I took the Computer Software Engineering course in the university. In a number of projects, I used SQL queries to generate large quantity of C++ code for many object classes in neat format automatically. I earned a good grade.

For example, the following query generates a number of queries that calculate the number of records and the average values for table names beginning with "TBL" and column data type is number.

SQL> select 'select '||''''||table_name||''''||','||''''||column_name||''''||', count(*), avg('||column_name||') avg_value from '|| table_name||';' from user_tab_columns where table_name like 'TBL%' and data_type = 'NUMBER' and column_name like '%AMT1';

select 'TBL_FRAUDDETAIL_HIST','MD_TRAN_AMT1', count(*), avg(MD_TRAN_AMT1) avg_value from TBL_FRAUDDETAIL_HIST;

select 'TBL_MATCHED_SO_FAR1124','MD_TRAN_AMT1', count(*), avg(MD_TRAN_AMT1) avg_value from TBL_MATCHED_SO_FAR1124;

select 'TBL_MATCHED_SO_FAR1201','MD_TRAN_AMT1', count(*), avg(MD_TRAN_AMT1) avg_value from TBL_MATCHED_SO_FAR1201;

select 'TBL_MATCHED_SO_FAR1226','MD_TRAN_AMT1', count(*), avg(MD_TRAN_AMT1) avg_value from TBL_MATCHED_SO_FAR1226;

select 'TBL_TXN_4_POC1','MD_TRAN_AMT1', count(*), avg(MD_TRAN_AMT1) avg_value from TBL_TXN_4_POC1;

select 'TBL_TXN_4_POC2','MD_TRAN_AMT1', count(*), avg(MD_TRAN_AMT1) avg_value from TBL_TXN_4_POC2;

select 'TBL_TXN_4_POC3','MD_TRAN_AMT1', count(*), avg(MD_TRAN_AMT1) avg_value from TBL_TXN_4_POC3;

select 'TBL_TXN_4_POC4','MD_TRAN_AMT1', count(*), avg(MD_TRAN_AMT1) avg_value from TBL_TXN_4_POC4;

select 'TBL_TXN_4_POC5','MD_TRAN_AMT1', count(*), avg(MD_TRAN_AMT1) avg_value from TBL_TXN_4_POC5;

select 'TBL_TXN_4_POC6','MD_TRAN_AMT1', count(*), avg(MD_TRAN_AMT1) avg_value from TBL_TXN_4_POC6;

select 'TBL_TXN_FOR_POC_EXT','MD_TRAN_AMT1', count(*), avg(MD_TRAN_AMT1) avg_value from TBL_TXN_FOR_POC_EXT;

select 'TBL_TXN_FOR_POC_EXT2','MD_TRAN_AMT1', count(*), avg(MD_TRAN_AMT1) avg_value from TBL_TXN_FOR_POC_EXT2;

12 rows selected.

The following query generates queries to count the distinctive values for all table names starting with "DEMO" and data type is character.

SQL> select 'select '||''''||table_name||''''||','||''''||column_name||''''||', count(distinct '||column_name||') from '||table_name||';' from use r_tab_columns where table_name like 'DEMO%' and data_type like 'VAR%';

select 'DEMO_CUSTOMERS_LOCAL','CUST_FIRST_NAME', count(distinct CUST_FIRST_NAME) from DEMO_CUSTOMERS_LOCAL;

select 'DEMO_CUSTOMERS_LOCAL','CUST_LAST_NAME', count(distinct CUST_LAST_NAME) from DEMO_CUSTOMERS_LOCAL;

select 'DEMO_CUSTOMERS_LOCAL','CUST_STREET_ADDRESS1', count(distinct CUST_STREET_ADDRESS1) from DEMO_CUSTOMERS_LOCAL;

select 'DEMO_CUSTOMERS_LOCAL','CUST_STREET_ADDRESS2', count(distinct CUST_STREET_ADDRESS2) from DEMO_CUSTOMERS_LOCAL;

select 'DEMO_CUSTOMERS_LOCAL','CUST_CITY', count(distinct CUST_CITY) from DEMO_CUSTOMERS_LOCAL;

select 'DEMO_CUSTOMERS_LOCAL','CUST_STATE', count(distinct CUST_STATE) from DEMO_CUSTOMERS_LOCAL;

select 'DEMO_CUSTOMERS_LOCAL','CUST_POSTAL_CODE', count(distinct CUST_POSTAL_CODE) from DEMO_CUSTOMERS_LOCAL;

select 'DEMO_CUSTOMERS_LOCAL','PHONE_NUMBER1', count(distinct PHONE_NUMBER1) from DEMO_CUSTOMERS_LOCAL;

select 'DEMO_CUSTOMERS_LOCAL','PHONE_NUMBER2', count(distinct PHONE_NUMBER2) from DEMO_CUSTOMERS_LOCAL;

select 'DEMO_CUSTOMERS_LOCAL','CUST_EMAIL', count(distinct CUST_EMAIL) from DEMO_CUSTOMERS_LOCAL;

10 rows selected.

Create Database Link to DB on Amazon EC2 Instance

From a database, we can create a database link to another remote database such as one on Amazon EC2 virtual server as shown below. Here ec2-12-34-567-899.compute-1.amazonaws.com is the amazon EC2 Linux instance's Public DNS.

SQL> create database link dl_aws_ec2 connect to prod_DB identified by PWDXXX using '(description=(address=(protocol= TCP)(host=ec2-12-34-567-899.compute-1.amazonaws.com)(port=1521)) (connect_data=(sid=XE)))';
Database link created.

SQL> select count(1) from user_tables@dl_aws_ec2;

COUNT(1)
15

Just like we query any databases, we can see a few tables with names starting with the word "DEMO", count the number of records, and if we want, make a local copy of the tables.

SQL> select table_name from user_tables@dl_aws_ec2 where table_name like 'DEMO%' and rownum <5 order by table_name;

TABLE_NAME
DEMO_CUSTOMERS
DEMO_ORDERS
DEMO_ORDER_ITEMS
DEMO_PAGE_HIERARCHY

SQL> select count(*) from DEMO_CUSTOMERS@dl_aws_ec2;

COUNT(*)
7

SQL> create table DEMO_CUSTOMERS_LOCAL as select * from DEMO_CUSTOMERS@dl_aws_ec2;
Table created.

Trim Function- Remove Leading and Trailing Blanks

Leading and trailing banks can be removed by Oracle trim function as shown below.

If we look at the lengths of the original and trimmed string (columns 4 and 5), we notice that the fourth record has 4 banks in the original string. However, the string is replaced with a NULL (length zero). If it is desirable that we want to keep one blank for the record, we can use NVL function to replace the NULL with a single blank.

In a project, I used the query similar to the following and fixed the data fields in debit card transaction.

nvl(substr( trim(SD_TERM_NAME_LOC),1,18),' ')

What I did was to first remove leading and trailing banks, then extract the first 18 characters (in case the terminal name is too long). In the case that the terminal name is all blanks or NULL, I replace them with a single blank. This data preparation step is necessary before we build a predictive model.

Some Observations on NULL Value Handling in Oracle SQL

We need to be aware of how NULL/missing values are handled in SQL query so that we will not be surprised by query results that appear "wrong". This is descried using the following simple table as an example. The fifth record has a NULL value.

SQL> select id, value from tbl_data order by id;

ID	VALUE
1	-1
2	0
3	1
4	2
5

If we calculate the total number of records in the table, number of records with values>=0 and values <0, they are 5, 3 and 1, respectively, as shown below. As we can see, the number of records for values >=0 (3) plus that <0 (1) is less than the total number of records (5). This is because NULL values appear in the SQL where clause exclude records from the consideration.

SQL> select count(*) from tbl_data;

COUNT(1)
5

SQL> select count(*) from tbl_data where value>=0;

COUNT(1)
3

SQL> select count(*) from tbl_data where value<0;

COUNT(1)
1

A better way to calcluate this kind of statisitcs is to use "case when" instead of where clause as shown below.

SQL> select count(*) total, sum(case when value>=0 then 1 else 0 end) non_negative, sum(case when value<0 then 1 else 0 end) negative, sum(case whe n value is null then 1 else 0 end) n_missing from tbl_data;

TOTAL	NON_NEGATIVE	NEGATIVE	N_MISSING
5	3	1	1

Or even better, we combine "case when" with "group by" to calculate the statistics. "Group by" is one of my favorites as it gives the complete picture (including NULL values) about the data.

SQL> select v, count(*) from (select case when value>=0 then 'non-negative' when value <0 then 'negative' else 'missing' end v from tbl_data) group by v;

V	COUNT(1)
negative	1
non-negative	3
missing	1

In summary, if we are aware of how NULL values are handles in the database, we will not be surprised by query results that appear "wrong".