Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Query question

Posted on 2007-04-03
4
Medium Priority
?
794 Views
Last Modified: 2012-06-27
I have a query like below..
Insert /*+ append */ into sales select * from  temp_sales.

What is /*+ Append */ ? when do you use it?   is it some kind of index hint?
0
Comment
Question by:jung1975
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18846136
/*+ APPEND */ is an oracle hint, that tells oracle to add the data above the high water mark, and does not try to find empty space in the already used blocks of the table.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 600 total points
ID: 18846149
when to use it: usually, when you load lots of rows to the table, ie from another table.
using it will avoid the overhead of trying to fit in few rows each time into places in blocks where rows have been deleted previously in the table.
will not give any performance boost on tables that are empty, or where all blocks are full.
0
 
LVL 16

Assisted Solution

by:MohanKNair
MohanKNair earned 200 total points
ID: 18846451
Append hint can be used to bypass UNDO operations while doing insert
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 1200 total points
ID: 18846476
just to put it with some sample data:

table1
-------
row1             - existing
row2             - deleted
row3             - deleted ( though row2 and row3 are deleted from table, this space can be reused for inserts but this is an used space )
row4             - existing. This is the last row in the table till now.  So any space beyond this is unused space from here till the allocated space of the object.

if you use this hint /*+ append */, the insert command will start from the unused space after row4 and it will continue...meaning if not sufficient it will consume space from the tablespace.

If this hint is not used, then oracle might try to use the free space ( already used space of row 2 and row 3 ) for the new inserts which we are doing and if the record length of the data which we are inserting is matching with the free space ( already used space of row2 and row3 ).

let us say, deleted records had many fields had null and new inserts have data for all fields and so oracle will just try and free space ( used space ) will not be sufficient and so it proceeds after row4 which is like using /*+ append */ hint.

As angelIII said, for tables which are complete empty ( meaning tables which are already truncated or just got created ) or tables which are completely full ( with no holes in the data blocks ) will not have any impact whether u use this hint or not.

Thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question