I rookie question for the first time, may not clear problem description, please Daniel understanding, PL/SQL, run the following code requirement is according to the field under CC the same string coding, 295000 data, run for 30 minutes for the first time I stopped, the second run more than 70 minutes haven't end or stops behind the PL/SQL is submitted to the ora03113 communication channel end of file, say the archive log is full, according to the online tutorial to modify, but I'm still trying to figure out what is the problem, this statement
Update LCA_ coding t set t.d LBH=(select a. H the from (select the objectid, row_number () over (partition by CC order by CC) as BH from LCA_ code) where a a.o bjectid=t.o bjectid)

CodePudding user response:

What is your specific needs, describe clearly ~
In addition, if the local database, it is recommended that close the archive mode, as follows:

 SQL> Shutdown immediate; 
The Database closed. 
The Database dismounted. 
ORACLE instance shut down. 
SQL> Startup the mount; 
ORACLE instance started. 

The Total System Global Area 3423965184 bytes 
Fixed Size of 2180544 bytes 
Variable Size of 2013268544 bytes 
The Database Buffers 1392508928 bytes 
Redo Buffers 16007168 bytes 
The Database mounted. 
SQL> The alter database noarchivelog; 

The Database altered. 

SQL> Archive log list; 
The Database log mode No Archive mode 
Automatic archival Disabled 
Archive destination USE_DB_RECOVERY_FILE_DEST 
The Oldest online log sequence 228 
The Current log sequence 230 
SQL> 

CodePudding user response:

The subquery data, write a list into a try;

CodePudding user response:

TLBH nvarchar (255), according to the type of CC coding sequence is OVER (partion by order by), PL/SQL60 multiple records will soon run out, but to run 295000 records, PL/SQL to run more than 70 minutes is not OVER yet, before doing the assignment of 275000 records in the form of CC field empty processing, oracle, as long as 27 s same to 1 minute 2 s in python, and python also deal with only 1 points, 7 s article 29.5 W data, I think the oracle performance should be superior than python, I don't know what situation has been run don't stop, you can see I do the same?
CC TLBH
SS 00001
SS 00002
AA 00001
AA 00002
BB 00001
BB 00002
BB 00003.

CodePudding user response:

If one of the few data quickly, and a large amount of data would be extremely slow, is about to consider is your SQL statements to

For example, is not likely formed in the course of the subquery card flute product, lead to increased amount of data into several dozen times

Archive logs, you can close if you confirm the library no problem a file, a log backups can be somewhere else

CodePudding user response:

Why want to UPDATE, CREATE TABLE AS directly and then kill of class, the new TABLE don't rename is ok

CodePudding user response:

The objectid indexed, can try on the column of indexed,

CodePudding user response:

Have to consider this is the cartesian product of the problem, but the specific mechanism to explain a Daniel, would be in addition to the create table to produce a new table, I tried to merge into, but does not conform to my intention, it's just one small function, if can the original table to update the best, I am a batch update a few fields

CodePudding user response:

Then you can according to the moderator,

CodePudding user response:

What does the execution plan say?

CodePudding user response:

Objectid with the rowid try, but the effect estimates will not be too obvious, full table update and time is spent number on the generation of bh,

CodePudding user response:

Get a process, select a. H the from (select the objectid, row_number () over (partition by CC order by CC) as BH from LCA_ coding
The SQL results an update of a line,
290000 data window sort also need the appearance of a 3, 4 seconds, you did one time each record, oracle, how to handle,

CodePudding user response:

This statement in the essentially ran 29.5 W record 2 seconds all updated is the issue of Oracle and essentially mechanism? Or do I write the statement in PLSQL