Sponsored Content
Top Forums Shell Programming and Scripting urgent help : want to check data in oracle from flate file Post 302317332 by colemar on Monday 18th of May 2009 05:36:00 PM
Old 05-18-2009
Executing a delete statemet for each row to be deleted leaves no choice to Oracle besides separately locate each single row (hopefully using a suitable index). We are in fact emulating a join method named nested loop.
There is a break even point where the number of rows to delete is large enough (in respect to the number of rows in the table) that an hash join is more efficient. This point is about 1% of the number of rows in the table, more or less depending on the average size of the rows.

To give to the Oracle optimizer the option to use an hash join, we must execute a single delete statement for all the rows to be deleted. This means that the rows from the input file must be loaded into a table (say SOURCE), so that it can be joined to the target TABLE.
We better also tell the Oracle optimizer about the number of rows in both the tables so that it can choose the best join method. This can be accomplished by collecting statistics using a procedure in the system package DBMS_STATS.

Code:
{ echo "var f4 varchar2\nconnect ${user}/${password}@${servicename}"
  echo "create table SOURCE (ID varchar2(99));"
  while read f1 f2 f3 f4 f5 f6 f7 f8
  do
    echo "exec :f4 := '$f4'\ninsert into SOURCE (ID) values (:f4);"
  done
  echo "delete TABLE where F4 in (select ID from SOURCE);"
  echo "drop table SOURCE;\nquit"
} | sqlplus -s /nolog

The commit statement is implied by the drop statement.
There is no performance advantage in doing a commit after every insert (this is just a myth); the converse is true, as a matter of fact every commit forces Oracle to flush the redo log buffer.

If ID is the primary key for the table SOURCE then the delete statement can be formulated differently, and this form most of the times is executed by Oracle using a more efficient plan:
Code:
delete (
  select null -- any value would do
  from TABLE a, SOURCE b
  where a.F4 = b.ID);

If ID is not the primary key for the table SOURCE, Oracle will object to this statement with a rather obscure error message about "non key preserved table". Worse, since F4 is the primary key for TABLE, Oracle will assume that you want to delete from SOURCE instead! You better make sure to have a primary key on ID and to put table TABLE first in the from list. Caveat emptor.
Of course to provide the primary key on ID the create statement above has to be enhanced.
More on this subject: http://asktom.oracle.com/pls/asktom/...:5050838809412

Instead of loading the file into a normal table (heap table), we can create a sort of virtual table whose contents is taken on demand from the external file.
This kind of table is the external table. The SQL code is more involved and some administrative burden is required to make the thing possible, therefore I am omitting a code sample.

Last edited by colemar; 05-18-2009 at 07:36 PM..
 

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

urgent-extracting block data from flat file using shell script

Hi, I want to extract block of data from flat file. the data will be like this start of log One two three end of log i want all data between start of log to end of log i.e One two three to be copied to another file. This particular block may appear multiple times in same file. I... (4 Replies)
Discussion started by: shirish_cd
4 Replies

2. Shell Programming and Scripting

load a data from text file into a oracle table

Hi all, I have a data like, 0,R001,2,D this wants to be loaded into a oracle database table. Pl let me know how this has to be done. Thanks in advance (2 Replies)
Discussion started by: raji35
2 Replies

3. Shell Programming and Scripting

Loading Oracle data to unix in flat file

Hi I would like to know how to load oracle data to unix flat file using a shell script. Can we use sqlldr to import data from oracle, if so can anyone help me with it. (2 Replies)
Discussion started by: raghav288
2 Replies

4. Shell Programming and Scripting

Load data from a flat file to oracle.

I have a flat file with records like Header 123 James Williams Finance2000 124 Pete Pete HR 1500 125 PatrickHeather Engg 3000 Footer The structure is: Eno:4 characters Name:8 characters Surname : 9 characters Dept:7 characters Sal:4characters These are sample... (1 Reply)
Discussion started by: Shivdatta
1 Replies

5. Shell Programming and Scripting

urgent<parsing data from a excel file>

Hi all, I wud like to get ur assistance in retrieving lines containing l1.My excel dataset contains around 8000 lines.I converted it into a text tab delimiter file and got the lines containing l1,My output is a list of lines containing l1 saved in a outfile.Some of d lines from my outfile s... (5 Replies)
Discussion started by: sayee
5 Replies

6. Shell Programming and Scripting

Need Urgent Help from UNIX gurus to get specific data from a file

Hi, I have a file monitor.txt as below... # Times are converted to local time from GMT. # Local Timezone: EST (GMT -05:00) PARAM1 { TIME 30; CC1 "xxxxx"; CC2 "xxxxx"; CC3 "xxxxx"; CC4 "xxxxx"; } PARAM2 { 4061 :... (3 Replies)
Discussion started by: zaq1xsw2
3 Replies

7. Shell Programming and Scripting

Connection to Oracle data and dump text file

Hi for all! I need for my job one shell script very concrete about connection to oracle databases and writing to text file. Well I tell you: 1.- I must read a file as parameter from linux interface: > example.sh fileToRead 2.-I must read line to line in "fileToRead" and take a... (9 Replies)
Discussion started by: wien996
9 Replies

8. Shell Programming and Scripting

Data is available or not in a flat file generated by Oracle

Hello, please help me an the below issue. i need to check whether data is available or not in a flat file generated by oracle (sometimes sql didn't any records) to overcome this. without opening flat file. Thanks....... (1 Reply)
Discussion started by: mahesh1987
1 Replies

9. Shell Programming and Scripting

Urgent: File Check in remote server

Hi Experts, I need a script in which I need to scp a file /home/chandan/abc.txt to a remote server using scp. Then I need to check whether scp is successful or not. How am I going to write this code? My Server Name: myserver File Name On My Server: /home/chandan/abc.txt Destination Server... (1 Reply)
Discussion started by: ChandanN
1 Replies
PQLIST(1)							      pqlist								 PQLIST(1)

NAME
pqlist - List available NetWare print queues SYNOPSIS
pqlist [ -h ] [ -S server ] [ -U user name ] [ -P password | -n ] [ -C ] [ pattern ] DESCRIPTION
pqlist lists all the NetWare print queues available to you on some server. If you are already connected to some server, this one is used. If pqlist does not print to a tty, the decorative header line is not printed, so that you can count the printing queue available on your server by doing pqlist -S server | wc -l pqlist looks up the file $HOME/.nwclient to find a file server, a user name and possibly a password. See nwclient(5) for more information. Please note that the access permissions of .nwclient MUST be 600, for security reasons. OPTIONS
pattern pattern is used to list only selected queues. You can use wildcards in the pattern, but you have to be careful to prevent shell inter- pretation of wildcards like '*'. -h -h is used to print out a short help text. -S server server is the name of the server you want to use. -U user name If the user name your NetWare administrator gave to you differs from your unix user-id, you should use -U to tell the server about your NetWare user name. -P password You may want to give the password required by the server on the command line. You should be careful about using passwords in scripts. -n -n should be given to mount shares which do not require a password to log in. If neither -n nor -P are given, pqlist prompts for a password. -C By default, passwords are converted to uppercase before they are sent to the server, because most servers require this. You can turn off this conversion by -C. SEE ALSO
nwclient(5), nprint(1), slist(1), ncpmount(8), ncpumount(8) CREDITS
pqlist was written by Volker Lendecke (lendecke@math.uni-goettingen.de) pqlist 01/10/1996 PQLIST(1)
All times are GMT -4. The time now is 04:51 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy