Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the matters needing attention in using Oracle Nologging+Append". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Check to see if the created table is a nologging user_tables.logging field.

If there is an index on the directly loaded table, Oracle does not process the indexed data the way it loads the data, but it also needs to maintain an index, which is expensive and generates a lot of redo.

So when using direct loading, it is usually for tables with a very large amount of data. If there are indexes in these tables, it will have a great performance impact, so you can consider dropping the index disable or drop first, and then re-establishing the index after loading the data.

Create an index to specify nologging:

Create index idx_object_id on idx_test (object_id) nologging

The disadvantage of using insert append is that it only inserts blocks above the high watermark and ignores free blocks below the high watermark, so append raises the high watermark.

Insert append adds a "6" type Exclusive lock to the table, that is, an exclusive lock, which is a table-level lock, which blocks all DML statements on the table. Therefore, it should be used cautiously in the upload environment where the business is running.

Test:

SYS@prod > insert / * + APPEND*/ into test select * from test

View locked objects in other sessions:

SYS@prod > select object_id,session_id,locked_mode from v$locked_object

OBJECT_ID SESSION_ID LOCKED_MODE

88905 125 6

Other errors about Append:

ORA-12938 error

If the transaction loaded by the direct path is not committed or rolled back, the modified table (report ORA-12838: cannot read/modify an object after modifying it in parallel) cannot be accessed by the same session, but not by the same session.

SYS@prod > insert / * + APPEND*/ into test select * from test

5566720 rows created.

SYS@prod > select count (*) from test

Select count (*) from test

*

ERROR at line 1:

ORA-12838: cannot read/modify an object after modifying it in parallel

This is the end of the content of "what are the considerations for using Oracle Nologging+Append". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.