Sponsored Content
Top Forums Shell Programming and Scripting SQL select all but not if it is already in an other column Post 302394875 by radoulov on Saturday 13th of February 2010 07:53:17 AM
Old 02-13-2010
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:

Code:
show create table sc;


Last edited by radoulov; 02-13-2010 at 12:53 PM..
 

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Pipe SQL select statement results to script

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)
Discussion started by: houtakker
6 Replies

2. Shell Programming and Scripting

formatting the sql select result

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)
Discussion started by: kotasateesh
2 Replies

3. Web Development

SQL Select inside Insert

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)
Discussion started by: Ikon
3 Replies

4. Shell Programming and Scripting

SQL Select and awk

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)
Discussion started by: habuzahra
6 Replies

5. Shell Programming and Scripting

Select SQL Queries Option

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)
Discussion started by: killboy
2 Replies

6. Shell Programming and Scripting

using SELECT sql statement in shell script

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)
Discussion started by: hcclnoodles
4 Replies

7. Programming

I'm going crazy with SQL select. Please help!

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)
Discussion started by: Lord Spectre
7 Replies

8. Programming

[Solved] SQL SELECT REPLACE

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)
Discussion started by: sonia102
5 Replies

9. Programming

Sql select replace

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)
Discussion started by: siya@
2 Replies

10. Shell Programming and Scripting

Parse SQL text and only format first SELECT statement.

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
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)
All times are GMT -4. The time now is 04:47 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy