Insert taking too much of time. - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 23

Thread: Insert taking too much of time.

  1. #11
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    TJI:

    What platform will be doing the insert? 80K rows might take several minutes on a desk top but only seconds on a mainframe?

    What is 'too long?'
    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'

  2. #12
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi,

    I ran the Insert statement with /*+APPEND */ hint for direct-load and it took about 24 minutes for inserting 80000 rows.

    Is there any other way to speed up Insert?

    Thanks,

    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  3. #13
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    We are on NT platform.

    I need some solution ASAP.

    Thanks,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  4. #14
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    sounds like you are looking for the non-existant silver bullet init.ora parameter:
    insert_speed=REALLY_FAST

    INSERT statements on a properly tuned database execute extremely fast.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #15
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi,

    Can you pl. brief me for better Insert performance, where exactly I should look in the Database.

    Thanks,

    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  6. #16
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by marist89
    Make sure your db is tuned; i/o is distributed, no rbs contention, good buffer hit ratio, etc.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #17
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    How long did you say your query takes to complete? 1 sec? And an insert with the same select 24 minutes? This can't be true, we are looking in a wrong direction. No matter how badly "untuned" your database is, there is no way for such a difference.

    I'm sure your query doesn't return 79000 records in 1 second as you say. Yes, you might get first bucket of records to your frontend in 1 second, but the query is not finished yet, it is still fetching. And this can take a lot of time to finish, no matter how fast you get your first records on screen. I think the problem is not with your INSERT performance, it is probably with your SELECT performance. Can you post your query here together with its execution plan. Maybe we can go from there on....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #18
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi jmodic,

    Sorry for delay. I was trying combinations and doing more research before giving any feedback.

    I think you are correct.

    We use TOAD for querying the data. I think, TOAD shows the FIRST_ROWS real quick and when we scroll down the data, it fetches remaining rows.

    Both Insert and Query is taking almost the same time. So, I think there is a need to tune the SQL statement.

    I have checked the SQL statement and tried to avoid FULL table scan whereever possible.

    Another thing is I tried to use APPEND hint and alter table to make NOLOGGING=Yes but still there is no diff. in Insert performance.

    Following is the Qry. and its execution plan.

    select
    glcc.segment1 FUND_PARENT,
    glcc.segment3 PARENT_PROG_RC,
    glcc.segment14 PARENT_AWARD_YEAR,
    SUBSTR(glcc.segment12,1,2) P_PROGRAM,
    glcc.segment12 P_SUB_PROGRAM,
    trunc(gjl.effective_date) P_POST_DATE,
    substr(gjh.name,1,4) P_TRAN_CODE,
    substr(gjl.attribute6,1,4) P_REF_TRAN_CODE,
    gjl.attribute1 P_TRAN_DESCRIPTION,
    substr(gjh.name,6,6) P_DOC_NO,
    substr(gjh.name,13,2) P_LINE_NO,
    SUBSTR(gjl.attribute9,1,4) P_BFY,
    tl.hierarchy_name ALI,
    glcc.segment2 P_SAC,
    obj.parent_flex_value P_PARENT_CLASS,
    substr(hier.parent_flex_value,3) P_PARENT_CATEGORY,
    glcc.segment10||'-'||glcc.segment11 P_OBJECT_CODE,
    gjl.attribute8 P_REVENUE_SOURCE,
    gjl.attribute3 P_VENDOR_NUMBER,
    decode(gjl.accounted_dr,null,gjl.accounted_cr,0,gjl.accounted_cr,(-1*gjl.accounted_dr)) P_AMOUNT
    from
    gl_code_combinations glcc,
    gl_je_lines gjl,
    gl_je_headers gjh,
    fnd_flex_value_norm_hierarchy hier,
    obj_child_parent_description obj,
    fnd_flex_values vl,
    fnd_flex_values vl1,
    fnd_flex_value_hierarchies ffh,
    fnd_flex_hierarchies_tl tl
    where
    gjl.code_combination_id = glcc.code_combination_id
    and gjh.je_header_id = gjl.je_header_id
    and gjh.actual_flag = 'A'
    and gjh.je_source in ( '1','Manual','Spreadsheet')
    and glcc.segment10 not like 'ZZ%'
    and glcc.segment10 = obj.flex_value
    and obj.parent_flex_value between hier.child_flex_value_low and hier.child_flex_value_high
    and hier.range_attribute = 'P'
    and hier.flex_value_Set_id = bject_Value_Set_id
    and glcc.segment3 = NVL(:P_PROGRAM_RC,glcc.segment3)
    and glcc.segment14 = NVL(:P_AWARD_YEAR,glcc.segment14)
    --and glcc.segment1 = nvl(_fund,glcc.segment1)
    and glcc.segment1 = vl.flex_value
    and vl.flex_value_set_id = :fund_value_set_id
    and vl.attribute4 = decode(_federal_state,'F','F','S','S',vl.attribute4)
    --and substr(glcc.segment12,1,2) = nvl(_program_name,substr(glcc.segment12,1,2))
    --and glcc.segment12 = nvl(_subprogram,glcc.segment12)
    and trunc(gjl.effective_date) between TO_dATE(:P_from_date) and TO_DATE(:P_to_date)
    and glcc.segment2 between ffh.child_flex_value_low and ffh.child_flex_Value_high
    and ffh.flex_value_Set_id = :sac_value_Set_id
    and ffh.parent_flex_value = vl1.flex_value
    and vl1.flex_value_Set_id = :sac_value_set_id
    and vl1.structured_hierarchy_level = tl.hierarchy_id
    and tl.flex_value_set_id = :sac_value_set_id
    --and tl.hierarchy_name like '%-%'
    and decode(_parameter,'PROGRAM',substr(glcc.segment12,1,2),1) = decode(_parameter,'PROGRAM',UPPER(_value),1)
    and decode(_parameter,'SUBPROGRAM',glcc.segment12,1) = decode(_parameter,'SUBPROGRAM',UPPER(_value),1)
    and decode(_parameter,'FUND',glcc.segment1,1) = decode(_parameter,'FUND',UPPER(_value),1)
    and decode(_parameter,'ALI',tl.hierarchy_name,1) = decode(_parameter,'ALI',UPPER(_value),1)
    UNION ALL
    select
    glcc.segment1 FUND_PARENT,
    glcc.segment3 PARENT_PROG_RC,
    glcc.segment14 PARENT_AWARD_YEAR,
    SUBSTR(glcc.segment12,1,2) P_PROGRAM,
    glcc.segment12 P_SUB_PROGRAM,
    trunc(gjl.effective_date) P_POST_DATE,
    substr(gjh.name,1,4) P_TRAN_CODE,
    substr(gjl.attribute6,1,4) P_REF_TRAN_CODE,
    gjl.attribute1 P_TRAN_DESCRIPTION,
    substr(gjh.name,6,6) P_DOC_NO,
    substr(gjh.name,13,2) P_LINE_NO,
    SUBSTR(gjl.attribute9,1,4) P_BFY,
    tl.hierarchy_name ALI,
    glcc.segment2 P_SAC,
    'NULL' P_PARENT_CLASS,
    'NULL' P_PARENT_CATEGORY,
    glcc.segment10||'-'||glcc.segment11 P_OBJECT_CODE,
    gjl.attribute8 P_REVENUE_SOURCE,
    gjl.attribute3 P_VENDOR_NUMBER,
    decode(gjl.accounted_dr,null,gjl.accounted_cr,0,gjl.accounted_cr,(-1*gjl.accounted_dr)) P_AMOUNT
    from
    gl_code_combinations glcc,
    gl_je_lines gjl,
    gl_je_headers gjh,
    --fnd_flex_value_norm_hierarchy hier,
    --obj_child_parent_description obj
    fnd_flex_values vl,
    fnd_flex_values vl1,
    fnd_flex_value_hierarchies ffh,
    fnd_flex_hierarchies_tl tl
    where
    gjl.code_combination_id = glcc.code_combination_id
    and gjh.je_header_id = gjl.je_header_id
    and gjh.actual_flag = 'A'
    and gjh.je_source in ( '1','Manual','Spreadsheet')
    and glcc.segment10 = 'ZZ5'
    --and glcc.segment10 = obj.flex_value
    --and obj.parent_flex_value between hier.child_flex_value_low and hier.child_flex_value_high
    --and hier.range_attribute = 'P'
    --and hier.flex_value_Set_id = bject_Value_Set_id
    and glcc.segment3 = NVL(:P_PROGRAM_RC,glcc.segment3)
    and glcc.segment14 = NVL(:P_AWARD_YEAR,glcc.segment14)
    --and glcc.segment1 = nvl(_fund,glcc.segment1)
    and glcc.segment1 = vl.flex_value
    and vl.flex_value_set_id = :fund_value_set_id
    and vl.attribute4 = decode(_federal_state,'F','F','S','S',vl.attribute4)
    and trunc(gjl.effective_date) between TO_dATE(:P_from_date) and TO_DATE(:P_to_date)
    --and substr(glcc.segment12,1,2) = nvl(_program_name,substr(glcc.segment12,1,2))
    --and glcc.segment12 = nvl(_subprogram,glcc.segment12)
    and glcc.segment2 between ffh.child_flex_value_low and ffh.child_flex_Value_high
    and ffh.flex_value_Set_id = :sac_value_Set_id
    and ffh.parent_flex_value = vl1.flex_value
    and vl1.flex_value_Set_id = :sac_value_set_id
    and vl1.structured_hierarchy_level = tl.hierarchy_id
    and tl.flex_value_set_id = :sac_value_set_id
    --and tl.hierarchy_name like '%-%'
    and decode(_parameter,'PROGRAM',substr(glcc.segment12,1,2),1) = decode(_parameter,'PROGRAM',UPPER(_value),1)
    and decode(_parameter,'SUBPROGRAM',glcc.segment12,1) = decode(_parameter,'SUBPROGRAM',UPPER(_value),1)
    and decode(_parameter,'FUND',glcc.segment1,1) = decode(_parameter,'FUND',UPPER(_value),1)
    and decode(_parameter,'ALI',tl.hierarchy_name,1) = decode(_parameter,'ALI',UPPER(_value),1)
    UNION ALL
    select
    glcc.segment1 FUND_PARENT,
    glcc.segment3 PARENT_PROG_RC,
    glcc.segment14 PARENT_AWARD_YEAR,
    SUBSTR(glcc.segment12,1,2) P_PROGRAM,
    glcc.segment12 P_SUB_PROGRAM,
    trunc(apida.accounting_date) P_POST_DATE,
    'VBVE' P_TRAN_CODE,
    null P_REF_TRAN_CODE,
    pov.vendor_name P_TRAN_DESCRIPTION,
    substr(apia.invoice_num,1,6) P_DOC_NO,
    to_char(apida.distribution_line_number) P_LINE_NO,
    glcc.segment14 P_BFY,
    tl.hierarchy_name ALI,
    glcc.segment2 P_SAC,
    obj.parent_flex_value P_PARENT_CLASS,
    substr(hier.parent_flex_value,3) P_PARENT_CATEGORY ,
    glcc.segment10||'-'||glcc.segment11 P_OBJECT_CODE,
    null P_REVENUE_SOURCE,
    pov.num_1099 P_VENDOR_NUMBER,
    (-1*apida.amount) P_AMOUNT
    from
    gl_code_combinations glcc
    ,fnd_flex_value_norm_hierarchy hier
    ,ap_invoices_all apia
    ,ap_invoice_distributions_all apida
    ,po_vendors pov
    ,obj_child_parent_description obj
    ,fnd_flex_values vl
    ,fnd_flex_values vl1,
    fnd_flex_value_hierarchies ffh,
    fnd_flex_hierarchies_tl tl
    where
    apida.dist_code_combination_id = glcc.code_combination_id
    and apida.match_status_flag = 'A'
    and apida.posted_flag = 'Y'
    and apida.invoice_id = apia.invoice_id
    and apia.vendor_id = pov.vendor_id
    and glcc.segment10 = obj.flex_value
    and obj.parent_flex_value between hier.child_flex_value_low and hier.child_flex_value_high
    and hier.range_attribute = 'P'
    and hier.flex_value_set_id = bject_value_set_id
    and glcc.segment3 = NVL(:P_PROGRAM_RC,glcc.segment3)
    and glcc.segment14 = NVL(:P_AWARD_YEAR,glcc.segment14)
    --and glcc.segment1 = nvl(_fund,glcc.segment1)
    and glcc.segment1 = vl.flex_value
    and vl.flex_value_set_id = :fund_value_set_id
    and vl.attribute4 = decode(_federal_state,'F','F','S','S',vl.attribute4)
    --and apida.set_of_books_id =NVL( :SOB_ID,apida.set_of_books_id)
    and trunc(apida.accounting_date) between TO_DATE(:P_from_date) and TO_DATE(:P_to_date)
    --and substr(glcc.segment12,1,2) = nvl(_program_name,substr(glcc.segment12,1,2))
    --and glcc.segment12 = nvl(_subprogram,glcc.segment12)
    and glcc.segment2 between ffh.child_flex_value_low and ffh.child_flex_Value_high
    and ffh.flex_value_Set_id = :sac_value_Set_id
    and ffh.parent_flex_value = vl1.flex_value
    and vl1.flex_value_Set_id = :sac_value_set_id
    and vl1.structured_hierarchy_level = tl.hierarchy_id
    and tl.flex_value_set_id = :sac_value_set_id
    --and tl.hierarchy_name like '%-%'
    and decode(_parameter,'PROGRAM',substr(glcc.segment12,1,2),1) = decode(_parameter,'PROGRAM',UPPER(_value),1)
    and decode(_parameter,'SUBPROGRAM',glcc.segment12,1) = decode(_parameter,'SUBPROGRAM',UPPER(_value),1)
    and decode(_parameter,'FUND',glcc.segment1,1) = decode(_parameter,'FUND',UPPER(_value),1)
    and decode(_parameter,'ALI',tl.hierarchy_name,1) = decode(_parameter,'ALI',UPPER(_value),1)


    EXPLAIN PLAN
    -------------------

    SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=349 Card=3 Bytes=2993)
    UNION-ALL
    FILTER
    NESTED LOOPS (Cost=120 Card=1 Bytes=1196)
    NESTED LOOPS (Cost=119 Card=1 Bytes=1136)
    NESTED LOOPS (Cost=117 Card=1 Bytes=1073)
    NESTED LOOPS (Cost=116 Card=1 Bytes=1026)
    NESTED LOOPS (Cost=115 Card=1 Bytes=972)
    NESTED LOOPS (Cost=114 Card=1 Bytes=942)
    NESTED LOOPS (Cost=113 Card=1 Bytes=876)
    NESTED LOOPS (Cost=111 Card=1 Bytes=813)
    NESTED LOOPS (Cost=110 Card=1 Bytes=759)
    NESTED LOOPS (Cost=108 Card=1 Bytes=574)
    NESTED LOOPS (Cost=108 Card=1 Bytes=557)
    NESTED LOOPS (Cost=84 Card=1 Bytes=295)
    NESTED LOOPS (Cost=82 Card=1 Bytes=232)
    TABLE ACCESS (FULL) OF GL_CODE_COMBINATIONS (Cost=80 Card=1 Bytes=169)
    TABLE ACCESS (BY INDEX ROWID) OF FND_FLEX_VALUES (Cost=2 Card=6 Bytes=378)
    INDEX (RANGE SCAN) OF FND_FLEX_VALUES_N1 (NON-UNIQUE) (Cost=1 Card=6)
    TABLE ACCESS (BY INDEX ROWID) OF FND_FLEX_VALUES (Cost=2 Card=127 Bytes=8001)
    INDEX (RANGE SCAN) OF FND_FLEX_VALUES_N1 (NON-UNIQUE) (Cost=1 Card=127)
    TABLE ACCESS (BY INDEX ROWID) OF FND_ID_FLEX_SEGMENTS (Cost=1 Card=1 Bytes=60)
    INDEX (UNIQUE SCAN) OF FND_ID_FLEX_SEGMENTS_U1 (UNIQUE)
    TABLE ACCESS (BY INDEX ROWID) OF GL_JE_LINES (Cost=24 Card=2939 Bytes=770018)
    INDEX (RANGE SCAN) OF GL_JE_LINES_N1 (NON-UNIQUE) (Cost=3 Card=2939)
    INDEX (UNIQUE SCAN) OF FND_FLEX_VALUES_TL_U1 (UNIQUE)
    TABLE ACCESS (BY INDEX ROWID) OF GL_JE_HEADERS (Cost=2 Card=75784 Bytes=14020040)
    INDEX (UNIQUE SCAN) OF GL_JE_HEADERS_U1 (UNIQUE) (Cost=1 Card=75784)
    TABLE ACCESS (FULL) OF FND_FLEX_VALUE_NORM_HIERARCHY (Cost=1 Card=15 Bytes=810)
    TABLE ACCESS (BY INDEX ROWID) OF FND_FLEX_VALUES (Cost=2 Card=127 Bytes=8001)
    INDEX (RANGE SCAN) OF FND_FLEX_VALUES_N1 (NON-UNIQUE) (Cost=1 Card=127)
    TABLE ACCESS (BY INDEX ROWID) OF FND_ID_FLEX_SEGMENTS (Cost=1 Card=1 Bytes=60)
    INDEX (UNIQUE SCAN) OF FND_ID_FLEX_SEGMENTS_U1 (UNIQUE)
    TABLE ACCESS (BY INDEX ROWID) OF FND_FLEX_VALUES_TL (Cost=1 Card=11662 Bytes=769692)
    INDEX (UNIQUE SCAN) OF FND_FLEX_VALUES_TL_U1 (UNIQUE)
    TABLE ACCESS (FULL) OF FND_FLEX_HIERARCHIES_TL (Cost=1 Card=112 Bytes=3360)
    TABLE ACCESS (FULL) OF FND_FLEX_VALUE_NORM_HIERARCHY (Cost=1 Card=15 Bytes=810)
    TABLE ACCESS (FULL) OF FND_FLEX_VALUE_HIERARCHIES (Cost=1 Card=47 Bytes=2209)
    TABLE ACCESS (BY INDEX ROWID) OF FND_FLEX_VALUES (Cost=2 Card=508 Bytes=32004)
    INDEX (RANGE SCAN) OF FND_FLEX_VALUES_N1 (NON-UNIQUE) (Cost=1 Card=508)
    TABLE ACCESS (FULL) OF FND_FLEX_HIERARCHIES_TL (Cost=1 Card=2 Bytes=120)
    TABLE ACCESS (BY INDEX ROWID) OF FND_ID_FLEX_SEGMENTS (Cost=1 Card=1 Bytes=60)
    INDEX (UNIQUE SCAN) OF FND_ID_FLEX_SEGMENTS_U1 (UNIQUE)
    TABLE ACCESS (BY INDEX ROWID) OF FND_ID_FLEX_SEGMENTS (Cost=1 Card=1 Bytes=60)
    INDEX (UNIQUE SCAN) OF FND_ID_FLEX_SEGMENTS_U1 (UNIQUE)
    NESTED LOOPS (Cost=112 Card=1 Bytes=849)
    NESTED LOOPS (Cost=111 Card=1 Bytes=789)
    NESTED LOOPS (Cost=109 Card=1 Bytes=726)
    NESTED LOOPS (Cost=108 Card=1 Bytes=679)
    NESTED LOOPS (Cost=106 Card=1 Bytes=494)
    NESTED LOOPS (Cost=82 Card=1 Bytes=232)
    TABLE ACCESS (FULL) OF GL_CODE_COMBINATIONS (Cost=80 Card=1 Bytes=169)
    TABLE ACCESS (BY INDEX ROWID) OF FND_FLEX_VALUES (Cost=2 Card=6 Bytes=378)
    INDEX (RANGE SCAN) OF FND_FLEX_VALUES_N1 (NON-UNIQUE) (Cost=1 Card=6)
    TABLE ACCESS (BY INDEX ROWID) OF GL_JE_LINES (Cost=24 Card=2939 Bytes=770018)
    INDEX (RANGE SCAN) OF GL_JE_LINES_N1 (NON-UNIQUE) (Cost=3 Card=2939)
    TABLE ACCESS (BY INDEX ROWID) OF GL_JE_HEADERS (Cost=2 Card=75784 Bytes=14020040)
    INDEX (UNIQUE SCAN) OF GL_JE_HEADERS_U1 (UNIQUE) (Cost=1 Card=75784)
    TABLE ACCESS (FULL) OF FND_FLEX_VALUE_HIERARCHIES (Cost=1 Card=47 Bytes=2209)
    TABLE ACCESS (BY INDEX ROWID) OF FND_FLEX_VALUES (Cost=2 Card=508 Bytes=32004)
    INDEX (RANGE SCAN) OF FND_FLEX_VALUES_N1 (NON-UNIQUE) (Cost=1 Card=508)
    TABLE ACCESS (FULL) OF FND_FLEX_HIERARCHIES_TL (Cost=1 Card=2 Bytes=120)
    FILTER
    NESTED LOOPS (Cost=117 Card=1 Bytes=948)
    NESTED LOOPS (Cost=116 Card=1 Bytes=888)
    NESTED LOOPS (Cost=114 Card=1 Bytes=825)
    NESTED LOOPS (Cost=113 Card=1 Bytes=778)
    NESTED LOOPS (Cost=112 Card=1 Bytes=724)
    NESTED LOOPS (Cost=111 Card=1 Bytes=694)
    NESTED LOOPS (Cost=110 Card=1 Bytes=628)
    NESTED LOOPS (Cost=108 Card=1 Bytes=565)
    NESTED LOOPS (Cost=88 Card=20 Bytes=10220)
    NESTED LOOPS (Cost=87 Card=1 Bytes=439)
    NESTED LOOPS (Cost=86 Card=1 Bytes=386)
    NESTED LOOPS (Cost=86 Card=1 Bytes=369)
    NESTED LOOPS (Cost=84 Card=1 Bytes=306)
    NESTED LOOPS (Cost=82 Card=1 Bytes=232)
    TABLE ACCESS (FULL) OF GL_CODE_COMBINATIONS (Cost=80 Card=1 Bytes=169)
    TABLE ACCESS (BY INDEX ROWID) OF FND_FLEX_VALUES (Cost=2 Card=6 Bytes=378)
    INDEX (RANGE SCAN) OF FND_FLEX_VALUES_N1 (NON-UNIQUE) (Cost=1 Card=6)
    TABLE ACCESS (BY INDEX ROWID) OF AP_INVOICE_DISTRIBUTIONS_ALL (Cost=2 Card=22 Bytes=1628)
    INDEX (RANGE SCAN) OF AP_INVOICE_DISTRIBUTIONS_N3 (NON-UNIQUE) (Cost=1 Card=22)
    TABLE ACCESS (BY INDEX ROWID) OF FND_FLEX_VALUES (Cost=2 Card=127 Bytes=8001)
    INDEX (RANGE SCAN) OF FND_FLEX_VALUES_N1 (NON-UNIQUE) (Cost=1 Card=127)
    TABLE ACCESS (BY INDEX ROWID) OF FND_ID_FLEX_SEGMENTS (Cost=1 Card=1 Bytes=60)
    INDEX (UNIQUE SCAN) OF FND_ID_FLEX_SEGMENTS_U1 (UNIQUE)
    INDEX (UNIQUE SCAN) OF FND_FLEX_VALUES_TL_U1 (UNIQUE)
    TABLE ACCESS (BY INDEX ROWID) OF AP_INVOICES_ALL (Cost=1 Card=27877 Bytes=1477481)
    INDEX (UNIQUE SCAN) OF AP_INVOICES_U1 (UNIQUE)
    TABLE ACCESS (BY INDEX ROWID) OF PO_VENDORS (Cost=1 Card=2000 Bytes=144000)
    INDEX (UNIQUE SCAN) OF PO_VENDORS_U1 (UNIQUE)
    TABLE ACCESS (FULL) OF FND_FLEX_VALUE_NORM_HIERARCHY (Cost=1 Card=15 Bytes=810)
    TABLE ACCESS (BY INDEX ROWID) OF FND_FLEX_VALUES (Cost=2 Card=127 Bytes=8001)
    INDEX (RANGE SCAN) OF FND_FLEX_VALUES_N1 (NON-UNIQUE) (Cost=1 Card=127)
    TABLE ACCESS (BY INDEX ROWID) OF FND_ID_FLEX_SEGMENTS (Cost=1 Card=1 Bytes=60)
    INDEX (UNIQUE SCAN) OF FND_ID_FLEX_SEGMENTS_U1 (UNIQUE)
    TABLE ACCESS (BY INDEX ROWID) OF FND_FLEX_VALUES_TL (Cost=1 Card=11662 Bytes=769692)
    INDEX (UNIQUE SCAN) OF FND_FLEX_VALUES_TL_U1 (UNIQUE)
    TABLE ACCESS (FULL) OF FND_FLEX_HIERARCHIES_TL (Cost=1 Card=112 Bytes=3360)
    TABLE ACCESS (FULL) OF FND_FLEX_VALUE_NORM_HIERARCHY (Cost=1 Card=15 Bytes=810)
    TABLE ACCESS (FULL) OF FND_FLEX_VALUE_HIERARCHIES (Cost=1 Card=47 Bytes=2209)
    TABLE ACCESS (BY INDEX ROWID) OF FND_FLEX_VALUES (Cost=2 Card=508 Bytes=32004)
    INDEX (RANGE SCAN) OF FND_FLEX_VALUES_N1 (NON-UNIQUE) (Cost=1 Card=508)
    TABLE ACCESS (FULL) OF FND_FLEX_HIERARCHIES_TL (Cost=1 Card=2 Bytes=120)
    TABLE ACCESS (BY INDEX ROWID) OF FND_ID_FLEX_SEGMENTS (Cost=1 Card=1 Bytes=60)
    INDEX (UNIQUE SCAN) OF FND_ID_FLEX_SEGMENTS_U1 (UNIQUE)
    TABLE ACCESS (BY INDEX ROWID) OF FND_ID_FLEX_SEGMENTS (Cost=1 Card=1 Bytes=60)
    INDEX (UNIQUE SCAN) OF FND_ID_FLEX_SEGMENTS_U1 (UNIQUE)



    Now, can you pl. give some hint on accelerating INSERT operation with above qry.

    Thanks,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  9. #19
    Join Date
    Jan 2001
    Posts
    3,131

    Talking

    WOW, how do you get those kewl smilies into your database?
    Is that considered a BLOB?
    MH

  10. #20
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    MH,

    No, I don't know but they are Bind variables with Colon sign and I copied whole thing from TOAD so, may be those colon signs are converted into Kewl smiles in this forum software.

    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width