[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ORACLE APPEND HINT 11g

Posted on 2012-08-30
4
Medium Priority
?
981 Views
Last Modified: 2012-09-18
Is there a benefit of using APPEND HINT?

I have a select statement that returns 30,000+ rows. I would like to insert into table.

If there is a benefit, what would the syntax be?  
For example, my select statment is:
SELECT  rating,
             title_versions.titles,
             title_versions.mtl_Titles_id,
             cat_career_groups.name career_group,
             o.organization,
             SUBSTR(display_order,5) || ' ' ||  SUB_TASK_VERSIONS.description title_order
       FROM title_versions          
      JOIN  title_types    ON title_versions.titles_type_id   =  titles_type .mdm_Titles_type_id
         AND titles_type .name = 'QPT'
      JOIN  mtl_accreditations      ON title_versions.mtl_accreditation_id     =  mtl_accreditations.mtl_accreditation_id
      JOIN  mtl_accred_versions   ON mtl_accred_versions.mtl_accreditation_id     =   mtl_accreditations.mtl_accreditation_id
      JOIN  cat_career_groups  ON cat_career_groups.mdm_career_group_id = mtl_accred_versions.mdm_career_group_id
      JOIN  org_unit_vw o             ON o.ind_titles_type _id                           =   org_keywords.org_ind_titles_type _id
      JOIN a3_ots    ON a3_ots.RATING =  rating_lookup.rating  
       AND o.organization = a3_ots.organization
         AND NOT EXISTS (select titles_name from a3_ots ots WHERE   rating_lookup.rating   =   ots.rating
                                                                                     AND   o.organization =  ots.organization
                                                                                     AND   ots.titles_name = title_versions.NAME)
GROUP BY o.organization,
            title_versions.name,
            titles_type .name,
            cat_career_groups.name,
            rating,
            title_versions.mtl_Titles_id,
            display_order,
             sub_task_versions.description
            order by 5,1,2
0
Comment
Question by:cookiejar
  • 3
4 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38352614
The append hint bypasses the buffer cache and writes above the high water mark.

It will 'almost' always be faster but can waste space in the table.

For example:  Load up 1 million rows. then delete them.  Then insert append.  The table still has all the space allocated from the 1 million rows and the new rows.  It will not reuse the deleted space.
0
 

Author Comment

by:cookiejar
ID: 38352618
I  use truncate table then  insert. Does this combination cause unused space?

Can anyone give me an example of the Append Hint using the sample select statement I posted.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38352647
>>Does this combination cause unused space?

Have you checked the docs?

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10007.htm#SQLRF01707

Deallocates all space used by the removed rows except that specified by the MINEXTENTS storage parameter

>>Can anyone give me an example of the Append Hint using the sample select statement I posted.

Really just like any other hint.

insert /*+ APPEND */ into table_name(col1, col2, col3 ... the rest of the columns by name)
SELECT -- the rest of your select
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1500 total points
ID: 38352663
Now that I think about it:  Since you truncate the table and there is no free space to begin with, I don't think the append hint will save you any time.

I would need to test it to see.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month20 days, 7 hours left to enroll

868 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