In order to speed up the query you should consider adding an index (or indexes).
What index you should add, depends on your data: you should start by indexing the most selective column(s) used in the where clause.
I would start with something like this:
Code:
create index sc_kwr_dt_i on sc(kwroot, data_12);
create index sc_ksc on sc (kwsearched);
You should consider indexing only subset of the data, if that subset is selective.
You should also consider the space needed for your indexes and their impact on the DML operations.
If the query is slow after indexing (some of) the columns in the where clause, post the output of the following statement:
Code:
explain select data_12 FROM sc ...
I would verify the performance of both versions (in vs outer join) after creating the indexe(s).
Could you post also the output of the following command:
Hello
I would like to perform a select from a oracle table and return those values to my shell script
For example:
site=head -1 $infile | cut -c1-15 | awk '{printf "s%", $0}
sqlplus -s /nolog |& #Open pipe to sql
select col1, col2, col3, col4
from oracle_table
where col5 =... (6 Replies)
Hi,
I have about 12 columns and 15 rows to be retrived from sybase isql command through unix. But when i output the sql into a file and see it, the formatting is going for a toss. can someone please suggest how can i get the result correctly in the output file ?
Thanks,
Sateesh (2 Replies)
I have following.
.
.
.
$userid = 2
.
$query = "select username from users where userid = ".$userid.";";
.
$username = $line;
$data="Some Data Here";
.
$query = "insert into logger (username, data) valuse ($username, $data);";
.
I would like to not have 2 database calls. (3 Replies)
Dear All ,
I have file1.txt contain values like the following:
----------
23
24
25
and I have shell script which has the following :
more file1.txt | awk '{print "select 'DUMP',CODE1||'|'||CODE2||'|'||CODE3 from CODE where CODE1='" $1 "';"}' > file2.sql
all I need is to have the... (6 Replies)
count.sh#!/bin/ksh
SQL1=`sqlplus -s usr/pwd @count.sql $1 $2 $3`
SQL2=`sqlplus -s usr/pwd @selectall.sql $1 $2 $3`
LIST="Count Select_All"
select i in $LIST
do
if
then
echo $SQL1
elif
then
echo $SQL2
fi
done (2 Replies)
Hi there
I have a database on a remote box and i have been using shell script to insert data into it for example, i could have a script that did this
SN=123456
n=server1
m=x4140
sql="UPDATE main SET hostname='$n',model='$m' WHERE serial='$SN';"
echo $sql |/usr/sfw/bin/mysql -h... (4 Replies)
Hello people,
as wrote in title I'm going crazy with a "complex" SQL select.
This is the seelct:
select T_ADDRESS, T_MCC,T_MNC,T_MSIN,T_IM_MNC, COUNT(*) FROM TABLETEST
WHERE T_MCC=123 AND (T_MNC=11 OR T_MNC=01)
GROUP BY T_ADDRESS,T_MCC,T_MNC,T_MSIN,T_IM_MNC HAVING count(*) > 5;This select... (7 Replies)
Hi All,
I had a query related to sql select update replace command.
i have a table named clusters and it looks like this
name model characteristics
sample1.1 +123 parent
sample1.2 -456 clone
sample1.3 +122 ... (5 Replies)
Hi All,
I had a query related to sql select replace command.
i have a table named clusters and it looks like this
Code:
name characteristics
sample 1.1 parent
sample 1.2 ... (2 Replies)
Hi Forum.
Need your expertise on the following question.
I have the following file which I would like to parse, find first block of SELECT statment and concatenate all input fields as 1 field (~ delimited):
Old File:
SELECT /*+ USE_HASH(CCOMM ICAR IMAP IAS IP IMAS IMPS IAP SPCA) */
... (5 Replies)
Discussion started by: pchang
5 Replies
LEARN ABOUT DEBIAN
bio::index::swissprot
Bio::Index::Swissprot(3pm) User Contributed Perl Documentation Bio::Index::Swissprot(3pm)NAME
Bio::Index::Swissprot - Interface for indexing one or more Swissprot files.
SYNOPSIS
# Make an index for one or more Swissprot files:
use Bio::Index::Swissprot;
use strict;
my $index_file_name = shift;
my $inx = Bio::Index::Swissprot->new(
-filename => $index_file_name,
-write_flag => 1);
$inx->make_index(@ARGV);
# Print out several sequences present in the index in Genbank
# format:
use Bio::Index::Swissprot;
use Bio::SeqIO;
use strict;
my $out = Bio::SeqIO->new( -format => 'genbank',
-fh => *STDOUT );
my $index_file_name = shift;
my $inx = Bio::Index::Swissprot->new(-filename => $index_file_name);
foreach my $id (@ARGV) {
my $seq = $inx->fetch($id); # Returns a Bio::Seq object
$out->write_seq($seq);
}
# alternatively
my ($id, $acc);
my $seq1 = $inx->get_Seq_by_id($id);
my $seq2 = $inx->get_Seq_by_acc($acc);
DESCRIPTION
By default the index that is created uses the AC and ID identifiers as keys. This module inherits functions for managing dbm files from
Bio::Index::Abstract.pm, and provides the basic functionality for indexing Swissprot files and retrieving Sequence objects from them. For
best results 'use strict'.
You can also set or customize the unique key used to retrieve by writing your own function and calling the id_parser() method. For
example:
$inx->id_parser(&get_id);
# make the index
$inx->make_index($index_file_name);
# here is where the retrieval key is specified
sub get_id {
my $line = shift;
$line =~ /^KWs+([A-Z]+)/i;
$1;
}
FEED_BACK
Mailing Lists
User feedback is an integral part of the evolution of this and other Bioperl modules. Send your comments and suggestions preferably to one
of the Bioperl mailing lists. Your participation is much appreciated.
bioperl-l@bioperl.org - General discussion
http://bioperl.org/wiki/Mailing_lists - About the mailing lists
Support
Please direct usage questions or support issues to the mailing list:
bioperl-l@bioperl.org
rather than to the module maintainer directly. Many experienced and reponsive experts will be able look at the problem and quickly address
it. Please include a thorough description of the problem with code and data examples if at all possible.
Reporting Bugs
Report bugs to the Bioperl bug tracking system to help us keep track the bugs and their resolution. Bug reports can be submitted via the
web:
https://redmine.open-bio.org/projects/bioperl/
AUTHOR - Ewan Birney
Also lorenz@ist.org, bosborne at alum.mit.edu
APPENDIX
The rest of the documentation details each of the object methods. Internal methods are usually preceded with a _
_index_file
Title : _index_file
Usage : $index->_index_file( $file_name, $i )
Function: Specialist function to index Swissprot format files.
Is provided with a filename and an integer
by make_index in its SUPER class.
Example :
Returns :
Args :
id_parser
Title : id_parser
Usage : $index->id_parser( CODE )
Function: Stores or returns the code used by record_id to
parse the ID for record from a string.
Returns &default_id_parser (see below) if not
set. An entry will be added to
the index for each string in the list returned.
Example : $index->id_parser( &my_id_parser )
Returns : ref to CODE if called without arguments
Args : CODE
default_id_parser
Title : default_id_parser
Usage : $id = default_id_parser( $line )
Function: The default parser for Swissprot.pm
Returns $1 from applying the regexp /^IDs*(S+)/
or /^ACs+([A-Z0-9]+)/ to the current line.
Returns : ID string
Args : a line string
_file_format
Title : _file_format
Usage : Internal function for indexing system
Function: Provides file format for this database
Example :
Returns :
Args :
perl v5.14.2 2012-03-02 Bio::Index::Swissprot(3pm)