Recently found two very strange question, has not find reasonable explanation, there is no master to look at,

1. There is a package, inside put a lot of procedure. Once the site of the project using the call package name. The name of the process approach run 1 day not out of the results, the session also has not died, then changed to begin the package name. The process of the end; Calls out the results soon. Don't know what the reason, the code itself is no problem, at the scene of the other projects has been call well,


2. There is a SQL: insert into tablea nologging select XXXX from XXX. Run for a long time has been the temporary tablespace maxed, error exit,
But only more than 6000 lines, found out the result set table also rebuilt, no problem, then try the create table tableb as select XXXX from XXX. Quickly out of the results, and then use the insert into tablea nologging select * from tableb soon end, also don't know what is going on,



The above two situations, we had not, is there any way?

CodePudding user response:

Oracle can for statistical data on the table, can carry on some "intelligent", decided to use the index such as, but this smart actually very sb, often can cause sometimes good sometimes bad
the condition of theNot a good way to solve, and the current situation.don encountered something check analysis

CodePudding user response:

Your first question, I ignored, it is suggested that call the begin and end executes each track, see what's waiting for events,

Second question, ctas are indeed faster than insert the select,
Probably many reasons, not necessarily accurate, nor authority:
1, insert a DML statement to several undo and redo data generated for each row, and ctas is a DDL, involve the undo and redo data, is too much small,
2, if the original table: index, constraint conditions, and so on, will check them in each of the insert, this should estimate time is not good, but not ctas,
3, insert into Mytable values for each row data, and Mytable fields do match, if the type is not consistent, need to make implicit conversion, and ctas does not involve,
4, insert into if didn't append option, will find available patches, but ctas does not involve,