6
Sequence Matching and Annotation (BLAST)

This chapter describes table functions included with ODM that permit you to perform similarity searches against nucleotide and amino acid sequence data stored in an Oracle database. You can use the table functions described in this chapter for ad hoc searches or you can embed them in applications. The inclusion of these table functions in ODM positions Oracle as a platform for bioinformatics.

This chapter discusses the following topics:

• NCBI BLAST
• Using ODM BLAST

6.1 NCBI BLAST

The National Center for Biotechnology Information (NCBI) implemented one of the commonly used versions of the Basic Local Alignment Search Tool (BLAST).

Sequence alignments provide a way to compare new sequences with previously characterized sequences. Both functional and evolutionary information can be inferred from well-designed queries and alignments. BLAST provides a method for searching of both nucleotide and protein databases. Since the BLAST algorithm detects local alignments, regions of similarity embedded in otherwise unrelated sequences can be detected.

The BLAST algorithm searches nucleotide and amino acid query sequences against databases of nucleotide and amino acid sequences. Based on the nature of the query and the database sequences, NCBI BLAST provides the following variants:

• BLASTP compares an amino acid query sequence against an amino acid sequence database.
• BLASTN compares a nucleotide query sequence against a nucleotide sequence database.
• BLASTX compares a nucleotide query sequence translated along all six reading frames (both strands) against a amino acid sequence database.
• TBLASTN compares an amino acid query sequence against a nucleotide sequence database translated along all six reading frames (both strands).
• TBLASTX compares the six-frame translations of a nucleotide query sequence against the six-frame translations of a nucleotide sequence database.

For more information about NCBI BLAST, see the NCBI BLAST Home Page at http://www.ncbi.nlm.nih.gov/BLAST/.

The table functions described in this chapter implement several of the variants of NCBI BLAST version 2.0.

6.2 Using ODM BLAST

This section contains several examples of using the ODM BLAST table functions to perform searches on nucleotide or amino acid sequences.

Most table function parameters have defaults. The defaults were carefully chosen so that users who have limited experience with BLAST will obtain good results.

6.2.1 Using BLASTN_MATCH to Search DNA Sequences

The BLAST table functions accept the CLOB (Character Large OBject) data type as the query sequence. It is not possible to construct a CLOB in an ad hoc SQL query. One way to construct a CLOB is to create a table and insert the query sequence into the table. Another option is to construct a CLOB using the programmatic interface if the BLAST query is part of a larger program. Suppose that the table query_db has the schema (sequence_id VARCHAR2(32), sequence CLOB). The following SQL query inserts the query sequence into query_db:

INSERT INTO query_db VALUES ('1', 'AGCTTTTCATTCTGACTGCAACGGGCAATATGTCTCTGT');

Suppose that the table GENE_DB stores DNA sequences. Suppose that GENE_DB has attributes seq_id, publication date, modification date, organism, and sequence, among other attributes. There is no required schema for the table that stores the sequences. The only requirement is that the table contain an identifier and the sequence and any number of other optional attributes.

The portion of the database to be used for the search can be specified using SQL. The full power of SQL can be used to perform more sophisticated selections.

6.2.1.1 Searching for Good Matches in DNA Sequences

The following query does a BLAST search of the given query sequence against the human genome and returns the seq_id, score, and expect value of matches that score > 25:

SELECT t.t_seq_id, t.score, t.expect
FROM TABLE (
    BLASTN_MATCH (
       (SELECT sequence FROM query_db WHERE sequence_id = '1'),
       CURSOR (SELECT seq_id, sequence FROM GENE_DB
        WHERE organism = 'human'),
       1,
       -1,
       0,
       0,
       10,
       0,
       0,
       0,
       0,
       11,
       0,
       0)
) t WHERE t.score > 25;

Note: The parameter value of 0 invokes the default values in most cases. See the syntax for details.

6.2.1.2 Searching DNA Sequences Published After a Certain Date

The following query does the BLAST search against all sequences published after Jan 01, 2000:

SELECT t.t_seq_id, t.score, t.expect
FROM TABLE (
    BLASTN_MATCH (
       (SELECT sequence FROM query_db WHERE sequence_id = '1'),
       CURSOR (SELECT seq_id, sequence FROM GENE_DB
        WHERE publication_date > '01-JAN-2000'),
       1,
       -1,
       0,
       0,
       10,
       0,
       0,
       0,
       0,
       11,
       0,
       0)
) t WHERE t.score > 25;

You can obtain other attributes of the matching sequence by joining the BLAST result with the original sequence table as follows:

SELECT t.t_seq_id, t.score, t.expect, g.publication_date, g.organism
FROM GENE_DB g, TABLE (
    BLASTN_MATCH (
       (SELECT sequence FROM query_db WHERE sequence_id = '1'),
       CURSOR (SELECT seq_id, sequence FROM GENE_DB
        WHERE publication_date > '01-JAN-2000'),
       1,
       -1,
       0,
       0,
       10,
       0,
       0,
       0,
       0,
       11,
       0,
       0)
) t WHERE t.t_seq_id = g.seq_id AND t.score > 25;

6.2.2 Using BLASTP_MATCH to Search Protein Sequences

Suppose that the table PROT_DB stores protein sequences. Insert the protein query sequence to be used for the search into query_db.

6.2.2.1 Searching for Good Matches in Protein Sequences

The following query does a BLASTP search of the given query sequence against protein sequences in PROT_DB and returns the identifier, score, name, and expect value of matches that score > 25:

SELECT t.t_seq_id, t.score, t.expect, p.name
FROM PROT_DB p, TABLE(
       BLASTP_MATCH (
         (SELECT sequence FROM query_db WHERE sequence_id = '2'),
         CURSOR(SELECT seq_id, sequence FROM PROT_DB),
         1,
         -1,
         0,
         0,
         'BLOSUM62',
         10,
         0,
         0,
         0,
         0,
         0)
       )t WHERE t.t_seq_id = p.seq_id AND t.score > 25
          ORDER BY t.expect;

6.2.3 Using BLASTN_ALIGN to Search and Align DNA Sequences

Suppose that the table GENE_DB stores DNA sequences. Suppose that GENE_DB has attributes seq_id, publication date, modification date, organism, and sequence among other attributes.

6.2.3.1 Searching and Aligning for Good Matches in DNA Sequences

The following query does a BLAST search and alignment of the given query sequence against the human genes and returns the publication_date, organism, and the alignment attributes of matching sequences that score > 25 and where more than 50% of the sequence is conserved in the match:

SELECT t.t_seq_id, t.alignment_length, t.pct_identity,
       t.q_seq_start, t.q_seq_end, t.t_seq_start, t.t_seq_end,
       t.score, t.expect, g.publication_date, g.organism
FROM GENE_DB g, TABLE (
    BLASTN_ALIGN (
       (SELECT sequence FROM query_db WHERE sequence_id = '1'),
       CURSOR (SELECT seq_id, sequence FROM GENE_DB
        WHERE publication_date > '01-JAN-2000'),
       1,
       -1,
       0,
       0,
       10,
       0,
       0,
       0,
       0,
       11,
       0,
       0)
) t WHERE t.t_seq_id = g.seq_id AND t.score > 25
    AND t.pct_identity > 50;

You can use BLASTP_ALIGN and TBLAST_ALIGN in a similar way.

6.2.4 Output of the Table Function

The output of a BLAST query is a table; the output table is described as the output table for the specific query.

Here are two examples of queries and the resulting output tables.

Query 1 is as follows:

select T_SEQ_ID AS seq_id, score, EXPECT as evalue
  from TABLE(
       BLASTP_MATCH (
         (select sequence from query_db),
         CURSOR(SELECT seq_id, seq_data
                FROM swissprot
                WHERE organism = 'Homo sapiens (Human)'),
         1,
         -1,
         0,
         0,
         'BLOSUM62',
         10,
         0,
         0,
         0,
         0,
         0)
       ); 

The output for query 1 is as follows:

SEQ_ID        SCORE     EVALUE
-------- ----------     ----------
P31946          205     5.8977E-18
Q04917          198     3.8228E-17
P31947          169     8.8130E-14
P27348          198     3.8228E-17
P58107           49     7.24297332

Query 2 is as follows:

select T_SEQ_ID AS seq_id, ALIGNMENT_LENGTH as len,
       Q_SEQ_START as q_strt, Q_SEQ_END as q_end, Q_FRAME, T_SEQ_START as t_
strt,
       T_SEQ_END as t_end, T_FRAME, score, EXPECT as evalue
  from TABLE(
       BLASTP_ALIGN (
         (select sequence from query_db),
         CURSOR(SELECT seq_id, seq_data
                FROM swissprot
                WHERE organism = 'Homo sapiens (Human)' AND
                      creation_date > '01-Jan-90'),
         1,
         -1,
         0,
         0,
         'BLOSUM62',
         10,
         0,
         0,
         0,
         0,
         0)
       );  

The output for Query 2 is as follows:

SEQ_ID    LEN Q_STRT Q_END Q_FRAME T_STRT T_END T_FRAME   SCORE  EVALUE
-------- ---- ------ ----- ------- ------ ----- ------- ------- ----------
P31946     50      0    50       0     13    63       0     205  5.1694E-18
Q04917     50      0    50       0     12    62       0     198  3.3507E-17
P31947     50      0    50       0     12    62       0     169  7.7247E-14
P27348     50      0    50       0     12    62       0     198  3.3507E-17
P58107     21     30    51       0    792   813       0      49  6.34857645

6.2.5 Sample Data for BLAST

We provide a few sample datasets and queries to test if the BLAST functions work correctly after ODM is installed.

The DM_USER schema contains the following sequence data tables.

SQL> describe SWISSPROT;
Name                                    Null?    Type
--------------------------------------- -------  -------------
SEQ_ID                                          VARCHAR2(32)
CREATION_DATE                                   DATE
ORGANISM                                        VARCHAR2(256)
SEQ_DATA                                        CLOB

SQL> describe prot_db;
Name                                     Null?    Type
---------------------------------------- -------  -------------
SEQ_ID                                            VARCHAR2(32)
SEQ_DATA                                          CLOB

SQL> describe ECOLI10;
Name                                      Null?    Type
----------------------------------------- -------- ---------------
    SEQ_ID                                         VARCHAR2(32)
    SEQ_DATA                                       CLOB

Table 6-1 displays genetic codes and associated names.

Table 6-1  Table of Genetic Codes

There are several public domain sequence databases available. One of them is the SwissProt database, which is a highly curated collection of protein sequences. The SwissProt database can be downloaded from

ftp://ftp.ebi.ac.uk/pub/databases/swissprot/release/sprot40.dat

In addition to the raw sequence data, the SwissProt database contains several other attributes of the sequence including organism, date published, date modified, published literature references, annotations, etc. BLAST requires only the sequence identifier and the sequence data to be stored to perform searches.

Depending on the needs of your specific application, different sets of these attributes may be important. Therefore, the database schema required to store the data needs to be appropriately designed. You can use a scripting language to parse the required fields from the SwissProt data and format the fields so that they can be loaded into an Oracle database.

The following Perl script outputs the sequence identifier, creation_date, organism, and sequence data in the required format for sqlldr is given below. (sqlldr is a program to load data into an Oracle database.)

#!/bin/perl
#swissprot.pl < input > output
#Input: protein db as provided by SWISSPROT
#
my $string = "";
my $indicator = "";
$sq = 0;
$ac = 0;

while(<>)
{
    #chop;
    if ( /^\/\// ) {
      print "\n";
      $sq = 0;
      $ac = 0;
      next;
    }
    if ($sq == 1) {
        @words = split;
        foreach $word (@words) {
          print "$word";
        }
        next;
    }
    if( /^AC(\s+)(\w+);/ ) {
      if ($ac == 0) {
        $indicator = $2;
        print "$indicator|";
        $sq = 0;
        $dt = 0;
        $ac = 1;
        next;
      }
    }
    if ( /^OS(\s+)(.*)\./ ) {
        $organism = $2;
        print "$organism|";
        next;
    }
    if ( /^DT(\s+)(\S+)/ ) {
        if ($dt == 0) {
           print "$2|";
           $dt = 1;
        }
    }
    if ( /^SQ(\s+)/ ) {
        $sq = "1";
        next;
    }
}

Follow these steps to download, parse, and save the SwissProt data in an Oracle database:

1. Download SwisProt data to the file sprot40.dat.
2. Save the perl script in a file named swissprot.pl, type the command

   swissprot.pl sprot40.dat > sprot_formatted.txt
   
   

   This command will read the SwissProt data stored in sprot40.dat, format it, and write it out to sprot_formatted.txt.

3. In order to load the data using sqlldr, you must create a table to hold the data and a control file. Create the table swissprot using the following SQL statement:

   create table swissprot (SEQ_ID VARCHAR2(32), CREATION_DATE DATE,
   ORGANISM VARCHAR2(256), SEQ_DATA CLOB);
   

   Create a control file named sprot.ctl with the following contents:

   LOAD DATA
   INFILE sprot40_formatted.txt
   INTO TABLE swissprot
   REPLACE
   FIELDS TERMINATED BY '|'
   TRAILING NULLCOLS
   (
   seq_id,
   creation_date,
   organism,
   seq_data char(100000)
   )
   

4. Finally, use the following command to load the data:

   sqlldr userid=<user_name>/<passwd> control=sprot.ctl log=sprot.log
   direct=TRUE data=sprot40_formatted.txt
   
   

The SwisProt data is now stored in the Oracle table swissprot.

Summary of BLAST Table Functions

The BLAST functionality is available as built-in table functions; these table functions can be used in the FROM clause of a SQL query.

Table 6-2  BLAST Table Functions

BLASTN_MATCH Table Function

This table function performs a BLASTN search of the given nucleotide sequence against the selected portion of the nucleotide database. The database can be selected using a standard SQL select and passed into the function as a cursor. It accepts the standard BLAST parameters that are mentioned below. The match returns the identifier of the matched (target) sequence (t_seq_id) (for example, the NCBI accession number), the score of the match, and the expect value.

Syntax

function BLASTN_MATCH (
  query_seq CLOB,
  seqdb_cursor REF CURSOR,
  subsequence_from NUMBER default 1,
  subsequence_to NUMBER default -1,
  filter_low_complexity BOOLEAN default false,
  mask_lower_case BOOLEAN default false,
  expect_value NUMBER default 10,
  open_gap_cost NUMBER default 5,
  extend_gap_cost NUMBER default 2,
  mismatch_cost NUMBER default -3,
  match_reward NUMBER default 1,
  word_size NUMBER default 11,
  xdropoff NUMBER default 30,
  final_x_dropoff NUMBER default 50)
  return table of row (t_seq_id VARCHAR2, score NUMBER, expect NUMBER);

Parameters

Table 6-3 describes the input parameters for BLASTN_MATCH; Table 6-4, the output parameters.

Table 6-3 Input Parameters for BLASTN_MATCH Table Function

query_seq

seqdb_cursor

subsequence_from

subsequence_to

filter_low_complexity

mask_lower_case

expect_value

open_gap_cost

extend_gap_cost

mismatch_cost

match_reward

word_size

xdropoff

final_x_dropoff

Table 6-4 Output Parameters for BLASTN_MATCH Table Function

t_seq_id

score

expect

BLASTP_MATCH Table Function

This table function performs a BLASTP search of the given amino acid sequence against the portion of the selected protein database. The database can be selected using a standard SQL select and passed into the function as a cursor. We also accept the standard BLAST parameters that are mentioned below. The match returns the identifier of the matched (target) sequence (t_seq_id) (for example, the NCBI accession number), the score of the match, and the expect value.

Syntax

function BLASTP_MATCH (
  query_seq CLOB,
  seqdb_cursor REF CURSOR,
  subsequence_from NUMBER default 1,
  subsequence_to NUMBER default -1,
  filter_low_complexity BOOLEAN default false,
  mask_lower_case BOOLEAN default false,
  sub_matrix VARCHAR2 default 'BLOSUM62',
  expect_value NUMBER default 10,
  open_gap_cost NUMBER default 11,
  extend_gap_cost NUMBER default 1,
  word_size NUMBER default 3,
  x_dropoff NUMBER default 15,
  final_x_dropoff NUMBER default 25)
  return table of row (t_seq_id VARCHAR2, score NUMBER, expect NUMBER);

Parameters

Table 6-5 describes the input parameters for BLASTN_MATCH; Table 6-6, the output parameters.

Table 6-5 Input Parameters for BLASTP_MATCH Table Function

query_seq

seqdb_cursor

subsequence_from

subsequence_to

filter_low_complexity

mask_lower_case

sub_matrix

expect_value

open_gap_cost

extend_gap_cost

word_size

x_dropoff

final_x_dropoff

Table 6-6 Output Parameters for BLASTP_MATCH Table Function

t_seq_id

score

expect

TBLAST_MATCH Table Function

This table function performs BLAST searches involving translations of either the query sequence or the database of sequences. The available options are:

• BLASTX: The query nucleotide sequence is translated and compared against a protein database.
• TBLASTN: The query amino acid sequence is compared against a translated nucleotide database.
• TBLASTX: The query nucleotide sequence is translated and compared against a translated nucleotide database.

The database can be selected using a standard SQL select and passed into the function as a cursor. We also accept the standard BLAST parameters that are mentioned below. The match returns the identifier of the matched (target) sequence (t_seq_id) (for example, the NCBI accession number), the score of the match, and the expect value.

Syntax

function TBLAST_MATCH (
  query_seq CLOB,
  seqdb_cursor REF CURSOR,
  subsequence_from NUMBER default 1,
  subsequence_to NUMBER default -1,
  translation_type VARCHAR2 default 'BLASTX',
  genetic_code NUMBER default 1,
  filter_low_complexity BOOLEAN default false,
  mask_lower_case BOOLEAN default false,
  sub_matrix VARCHAR2 default 'BLOSUM62',
  expect_value NUMBER default 10,
  open_gap_cost NUMBER default 11,
  extend_gap_cost NUMBER default 1,
  word_size NUMBER default 3,
  x_dropoff NUMBER default 15,
  final_x_dropoff NUMBER default 25)
  return table of row (t_seq_id VARCHAR2, score NUMBER, expect NUMBER);

Parameters

Table 6-7 describes the input parameters for TBLAST_MATCH; Table 6-8, the output parameters.

Table 6-7 Input Parameters for TBLAST_MATCH Table Function

query_seq

seqdb_cursor

subsequence_from

subsequence_to

translation_type

genetic_code

filter_low_complexity

mask_lower_case

sub_matrix

expect_value

open_gap_cost

extend_gap_cost

word_size

x_dropoff

final_x_dropoff

Table 6-8 Output Parameters for TBLAST_MATCH Table Function

t_seq_id

score

expect

BLASTN_ALIGN Table Function

This table function performs a BLASTN alignment of the given nucleotide sequence against the selected portion of the nucleotide database. The database can be selected using a standard SQL select and passed into the function as a cursor. It accepts the standard BLAST parameters that are mentioned below.

BLASTN_MATCH returns only the score and expect value of the match. It does not return information about the alignment. BLASTN_MATCH is typically used when you want to follow up a BLAST search with a Smith-Waterman alignment.

BLASTN_ALIGN does the BLAST alignment and returns the information about the alignment.

Syntax

function BLASTN_ALIGN (
  query_seq CLOB,
  seqdb_cursor REF CURSOR,
  subsequence_from NUMBER default 1,
  subsequence_to NUMBER default -1,
  filter_low_complexity BOOLEAN default false,
  mask_lower_case BOOLEAN default false,
  expect_value NUMBER default 10,
  open_gap_cost NUMBER default 5,
  extend_gap_cost NUMBER default 2,
  mismatch_cost NUMBER default -3,
  match_reward NUMBER default 1,
  word_size NUMBER default 11,
  xdropoff NUMBER default 30,
  final_x_dropoff NUMBER default 50)
  return table of row ( 
    t_seq_id VARCHAR2,
    pct_identity NUMBER,
    alignment_length NUMBER,
    mismatches NUMBER,
    positives NUMBER,
    gap_openings NUMBER,
    gap_list [Table of NUMBER],
    q_seq_start NUMBER,
    q_frame NUMBER,
    q_seq_end NUMBER,
    t_seq_start NUMBER,
    t_seq_end NUMBER,
    t_frame NUMBER,   
    score NUMBER, 
    expect NUMBER);

Parameters

Table 6-9 describes the input parameters for BLASTN_ALIGN; Table 6-10, the output parameters.

Table 6-9 Input Parameters for BLASTN_ALIGN Table Function

query_seq

seqdb_cursor

subsequence_from

subsequence_to

filter_low_complexity

mask_lower_case

expect_value

open_gap_cost

extend_gap_cost

mismatch_cost

match_reward

word_size

xdropoff

final_x_dropoff

Table 6-10 Output Parameters for BLASTN_ALIGN Table Function

t_seq_id

pct_identity

alignment_length

mismatches

positives

gap_openings

gap_list

q_seq_start,
q_seq_end 

q_frame

t_seq_start,
t_seq_end

t_frame

expect

score

BLASTP_ALIGN Table Function

This table function performs a BLASTP alignment of the given amino acid sequences against the selected portion of the protein database. The database can be selected using a standard SQL select and passed into the function as a cursor. You can also use the standard BLAST parameters that are mentioned below.

BLASTP_MATCH function returns only the score and expect value of the match. It does not return information about the alignment. The BLASTP_MATCH function will typically be used where the user wants to follow up a BLAST search with a full FASTA or Smith-Waterman alignment.

The BLASTP_ALIGN function does the BLAST alignment and returns the information about the alignment. The schema of the returned alignment is the same as that of BLASTN_ALIGN.

Syntax

function SYS_BLASTP_ALIGN (
  query_seq CLOB,
  seqdb_cursor REF CURSOR,
  subsequence_from NUMBER default 1,
  subsequence_to NUMBER default -1,
  filter_low_complexity BOOLEAN default false,
  mask_lower_case BOOLEAN default false,
  sub_matrix VARCHAR2 default 'BLOSUM62',
  expect_value NUMBER default 10,
  open_gap_cost NUMBER default 11,
  extend_gap_cost NUMBER default 1,
  word_size NUMBER default 3,
  x_dropoff NUMBER default 15,
  final_x_dropoff NUMBER default 25)
 return table of row ( 
    t_seq_id VARCHAR2,
    pct_identity NUMBER,
    alignment_length NUMBER,
    mismatches NUMBER,
    positives NUMBER,
    gap_openings NUMBER,
    gap_list [Table of NUMBER],
    q_seq_start NUMBER,
    q_frame NUMBER,
    q_seq_end NUMBER,
    t_seq_start NUMBER,
    t_seq_end NUMBER,
    t_frame NUMBER,   
    score NUMBER, 
    expect NUMBER);

Parameters

Table 6-11 describes the input parameters for BLASTP_ALIGN; Table 6-12, the output parameters.

Table 6-11 Input Parameters for BLASTP_ALIGN Table Function

query_seq

seqdb_cursor

subsequence_from

subsequence_to

filter_low_complexity

mask_lower_case

sub_matrix

expect_value

Table 6-12 Output Parameters for BLASTP_ALIGN Table Function

TBLAST_ALIGN Table Function

This table function performs BLAST alignments involving translations of either the query sequence or the database of sequences. The available translation options are BLASTX, TBLASTN, and TBLASTX. The schema of the returned alignment is the same as that of BLASTN_ALIGN and BLASTP_ALIGN.

Syntax

function TBLAST_ALIGN (
  query_seq CLOB,
  seqdb_cursor REF CURSOR,
  subsequence_from NUMBER default 1,
  subsequence_to NUMBER default 0,
  translation_type VARCHAR2 default 'BLASTX',
  genetic_code NUMBER default 1,
  filter_low_complexity BOOLEAN default false,
  mask_lower_case BOOLEAN default false,
  sub_matrix VARCHAR2 default 'BLOSUM62',
  expect_value NUMBER default 10,
  open_gap_cost NUMBER default 11,
  extend_gap_cost NUMBER default 1,
  word_size NUMBER default 3,
  x_dropoff NUMBER default 15,
  final_x_dropoff NUMBER default 25)
 return table of row ( 
    t_seq_id VARCHAR2,
    pct_identity NUMBER,
    alignment_length NUMBER,
    mismatches NUMBER,
    positives NUMBER,
    gap_openings NUMBER,
    gap_list [Table of NUMBER],
    q_seq_start NUMBER,
    q_frame NUMBER,
    q_seq_end NUMBER,
    t_seq_start NUMBER,
    t_seq_end NUMBER,
    t_frame NUMBER,   
    score NUMBER, 
    expect NUMBER);

Parameters

Table 6-13 describes the input parameters for TBLAST_ALIGN; Table 6-14, the output parameters.

Table 6-13 Input Parameters for TBLAST_ALIGN Table Function

Table 6-14 Output Parameters for TBLAST_ALIGN Table Function