More time to retrieve data from DB


Login or Register for Dates, Times and to Reply

 
Thread Tools Search this Thread
# 1  
More time to retrieve data from DB

Hi All,

It takes around one hour to retrieve 3 lakhs data from DB. I feel this can be still more reduced, please help me in improvising the below code, to get it retrieve faster, atleast 30 to 45 minutes.

Code:
sqlplus -s ${OCAU_DB_UNAME}/${OCAU_DB_UPSWD}@${OCAU_DB_NAME} > /apps/data/filedata.txt 2> /apps/log/FileErrorlog.txt << EOF

Code:
set head off 
SET pagesize 0
SET LINESIZE 500
SET LONGCHUNKSIZE 500
SET LONG 320000000
SET RECSEP OFF
set SERVEROUTPUT off ECHO off
SET FEEDBACK OFF
whenever OSERROR EXIT 9
whenever SQLERROR EXIT sql.SQLCODE
SELECT FILEDATA FROM OCAU.FILEUPLOAD_OU WHERE FILESTATUS = 'T' AND FILETYPE = 'ACC' ORDER BY CREATEDATE ASC 
/
exit;
EOF 

I guess LONGCHUNKSIZE and LONG makes the trick? Please suggest solution for this
# 2  
dont you have any index created for the FILESTATUS & FILETYPE ?
# 3  
Sorry i forget to mention, FILEDATA column is the CLOB data. So 3 lakh records which i said before number of lines in this CLOB data not the number of rows in DB (it is just 9 rows). But still indexing may give betterment?

---------- Post updated at 12:24 PM ---------- Previous update was at 10:02 AM ----------

Any suggestions friends. Need to solve fast.
# 4  
Not much knowledge on LOB datatypes. If possible drop the order by clause and try it, would save some time.
# 5  
I have dropped order by but no improvement.. any other suggestion?
Login or Register for Dates, Times and to Reply

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #644
Difficulty: Easy
The first web browser was called WorldWideWeb.
True or False?

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Compare columns of two files and retrieve data

Hi guys, I need your help. I have two files: file1 1 3 5 file2 1,XX 2,AA 3,BB 4,CC 5,DD I would like to compare the first column and where they are equal to write that output in a new file: 1,XX 3,BB (7 Replies)
Discussion started by: apenkov
7 Replies

2. Shell Programming and Scripting

PERL: retrieve the data based on regular expression

Hi Friends i have a code below sample $text contains the values test1 PIC X test1 PIC XX test1 PIC XXX test1 PIC X(8) test1 PIC X(12) test1 PIC X test1 X(8) test1 PIC X VALUE 'N'. $text =~ /^\d{6} +(\d{2}) +(+) +PIC +(+)(\((\d+)\)(V(+)| +(COMP\-3).|\.)|( +(COMP\-3).|... (4 Replies)
Discussion started by: i150371485
4 Replies

3. Shell Programming and Scripting

Sed for select and retrieve data

I would like to recover the data from 3 text tags. These three markers are located between the tags specific location <tag1> and </tag1> knowing that they are in many places. In File.txt: <tag2>txt2</tag2> <tag3>txt3</tag3> <tag4>txt4</tag4> .... <tag1> <tag2>txt2</tag2>... (3 Replies)
Discussion started by: Amad
3 Replies

4. UNIX for Dummies Questions & Answers

Retrieve data from Remote machine

Hello Please I ask if it is possible to recover data that is stored on a remote machine that I access via ssh on a usb ? if so, how? Thank you so much (5 Replies)
Discussion started by: chercheur857
5 Replies

5. Shell Programming and Scripting

Help to retrieve data from two files matching a string

Hello Experts, I have come back to this forum after a while now, since require a better way to get my result.. My query is as below.. I have 3 files -- 1 Input file, 2 Data files .. Based on the input file, data has to be retreived matching from two files which has one common key.. For EX:... (4 Replies)
Discussion started by: shaliniyadav
4 Replies

6. UNIX for Dummies Questions & Answers

How to compare two columns and retrieve data

I am a newbie to Unix and slowly learning it. I have a large data set with 8 different columns. I want to compare two columns and retrieve data if the two columns have similar number. I have attached the example. There are two columns (S-Contig and N-Contig). I want to retrieve the data from... (7 Replies)
Discussion started by: bjorngill
7 Replies

7. Shell Programming and Scripting

How to retrieve data using awk command

I have a txt file with below data (textfile1.txt) select col1, col2 from Schema_Name.Table_Name1 select * from Schema_Name.Table_Name2 select col1, col2, col3 from Schema_Name.Table_Name3 select col1 from Schema_Name.Table_Name4 My output should look like Table_Name1 Table_Name2... (5 Replies)
Discussion started by: prasad4004
5 Replies

8. UNIX for Advanced & Expert Users

Retrieve data and redirect to a file

How to write a shell script to retrieve datas from database after that this database are redirect to a excell sheet and then i got a mail that gives details about the database with the column name and data.. I m using oracle 9i... Thanks, Anup Das (2 Replies)
Discussion started by: anupdas
2 Replies

9. Shell Programming and Scripting

Retrieve data from a file

Hello guys I want to retrieve two data from a file, like this: bash-2.03$ cat numtest 123456 123457 bash-2.03$ more ./test_num #!/bin/bash num1= num2= cnt=1 while read x do num${cnt}=$x cnt=$(($cnt+1)) done <$1 echo $num1 "\n" $num2 But when i executed this script, error... (2 Replies)
Discussion started by: tpltp
2 Replies

Featured Tech Videos