Posted on 2012-08-30
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,
             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,
            titles_type .name,
            order by 5,1,2
Question by:cookiejar
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    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.

    Author Comment

    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.
    LVL 76

    Expert Comment

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

    Have you checked the docs?

    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
    LVL 76

    Accepted Solution

    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.

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now