Results 1 to 8 of 8
  1. #1
    Join Date
    May 2004
    Posts
    42

    Unanswered: Serious Problem Of Optimizer

    Hi
    I have a table NAD_ADDRESS which contains 30 Millions of data. and index is upon the column tr_thoroughfare_number. Whenever i am issuing the following statement

    "SELECT /*+ INDEX */ tr_sub_prem_key,
    tr_address_quality, best_address_line_1, best_address_line_2, best_address_line_3,
    best_address_line_4, best_postcode, tr_org_name,tr_po_box, tr_sub_premise,tr_sub_premise_ind,
    tr_premise_name,tr_premise_name_ind,tr_thoroughfar e_number,tr_depend_thoroughfare,tr_thoroughfare,tr _thoroughfare_ind,
    tr_double_depend_locality,tr_locality,tr_locality_ ind,tr_town,tr_town_ind,tr_county,tr_county_ind,tr _postal_outcode,
    tr_postal_incode, tr_delivery_point, tr_fail_level, tr_return_code, tr_country_name,
    cherished_name FROM NAD_ADDRESS WHERE r_thoroughfare_number='10'",

    at that time it is taking around 500 milliseconds and table is being scanned Index Wise.

    But whenever i am trying to insert the output into a temporary table then it is taking around 10 minutes. I mean when i am issuing the following statement it is taking around 10 minutes. See in temporary table no indexes are there.

    "INSERT INTO TAB2(tr_sub_prem_key, tr_address_quality,
    best_address_line_1, best_address_line_2,best_address_line_3,
    best_address_line_4,postcode, tr_org_name,tr_po_box, tr_sub_premise,tr_sub_premise_ind,
    tr_premise_name,tr_premise_name_ind,tr_thoroughfar e_number,tr_depend_thoroughfare,
    tr_thoroughfare,tr_thoroughfare_ind, tr_double_depend_locality,tr_locality,tr_locality_ ind,
    tr_town,tr_town_ind,tr_county,tr_county_ind,
    tr_postal_outcode,tr_postal_incode, tr_delivery_point, tr_fail_level,
    tr_return_code, tr_country_name,cherished_name) SELECT /*+ INDEX */ tr_sub_prem_key,
    tr_address_quality, best_address_line_1, best_address_line_2, best_address_line_3,
    best_address_line_4, best_postcode, tr_org_name,tr_po_box, tr_sub_premise,tr_sub_premise_ind,
    tr_premise_name,tr_premise_name_ind,tr_thoroughfar e_number,tr_depend_thoroughfare,tr_thoroughfare,tr _thoroughfare_ind,
    tr_double_depend_locality,tr_locality,tr_locality_ ind,tr_town,tr_town_ind,tr_county,tr_county_ind,tr _postal_outcode,
    tr_postal_incode, tr_delivery_point, tr_fail_level, tr_return_code, tr_country_name,
    cherished_name FROM NAD_ADDRESS WHERE tr_thoroughfare_number='10' "

    Can anybody help me out in reducing the time for the above statement.

    In our database the Optimizer Mode is set to CHOOSE.

    Thanks & Regards
    Rasmi Ranjan Moharathy

  2. #2
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Hi,

    Make sure that all the foreign keys for the table TAB2 are index.
    Can yoy please post the explain for the query?


    Thanks,
    Pagnint
    (No need to search web before posting new question)

  3. #3
    Join Date
    May 2004
    Posts
    42

    Problmes With Optimizer

    Hi
    Table TAB2 does not have any Index. Neither foreign key nor primary keys are there. Whenever i am going to insert. Here is the explain plan for the query.

    ------------------------------------------------------------------------------------------
    | Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
    ------------------------------------------------------------------------------------------
    | INSERT STATEMENT | | 2K| 482K| 2183 | | |
    | TABLE ACCESS BY INDEX ROW|NAD_ADDRESS | 2K| 482K| 2183 | | |
    | INDEX RANGE SCAN |NAD_ADDRESS_TR_THGH | 2K| | 13 | | |
    ------------------------------------------------------------------------------------------

    Query was like this...

    INSERT INTO TAB2(tr_sub_prem_key, tr_address_quality,
    best_address_line_1, best_address_line_2,best_address_line_3,
    best_address_line_4,postcode, tr_org_name,tr_po_box, tr_sub_premise,tr_sub_premise_ind,
    tr_premise_name,tr_premise_name_ind,tr_thoroughfar e_number,tr_depend_thoroughfare,
    tr_thoroughfare,tr_thoroughfare_ind, tr_double_depend_locality,tr_locality,tr_locality_ ind,
    tr_town,tr_town_ind,tr_county,tr_county_ind,
    tr_postal_outcode,tr_postal_incode, tr_delivery_point, tr_fail_level,
    tr_return_code, tr_country_name,cherished_name) SELECT /*+ INDEX */ tr_sub_prem_key,
    tr_address_quality, best_address_line_1, best_address_line_2, best_address_line_3,
    best_address_line_4, best_postcode, tr_org_name,tr_po_box, tr_sub_premise,tr_sub_premise_ind,
    tr_premise_name,tr_premise_name_ind,tr_thoroughfar e_number,tr_depend_thoroughfare,tr_thoroughfare,tr _thoroughfare_ind,
    tr_double_depend_locality,tr_locality,tr_locality_ ind,tr_town,tr_town_ind,tr_county,tr_county_ind,tr _postal_outcode,
    tr_postal_incode, tr_delivery_point, tr_fail_level, tr_return_code, tr_country_name,
    cherished_name FROM NAD_ADDRESS WHERE tr_thoroughfare_number='10'

    NAD_ADDRESS Contains 30 Millions of data out of which it filters 400000 dat for thoroughfare_number=10. Why is is taking time to insert into a table.

    Is it required to create indexex on TAB2.

    Please help me out...

    Thanks & Regards
    Rasmi

  4. #4
    Join Date
    May 2004
    Posts
    42

    Problmes With Optimizer

    Hi
    Table TAB2 is a Global Temporary Table without any Index, Primary Key/Foreign Key. Why the Insert Statement is taking time, i am not able to trace out the reason. Please help me..

    Thanks & regards
    Rasmi

  5. #5
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Hi Rashmi..

    No need to create index in TAB2.
    Can you try by restricting the select query to insert only one row by puting
    rownum=1 condition in the where clause of the query and tell how much time it is taking

    Thanks,
    Pagnint
    (No need to search web before posting new question)

  6. #6
    Join Date
    May 2004
    Posts
    42

    Problems With Optimizer

    Hi paginit
    As per your instruction i run the query mentioning ROWNUM = 1 as well as ROWNUM<100000.

    If it is ROWNUM = 1 it iakes 200 MS. but in case of ROWNUM<100000 it takes arounf 1.5 Minutes.

    What is the problem behind it???.

    Thanks & Regards
    Rasmi

  7. #7
    Join Date
    May 2004
    Posts
    42
    Hi paginit
    As per your instruction i run the query mentioning ROWNUM = 1 as well as ROWNUM<100000.

    If it is ROWNUM = 1 it iakes 200 MS. but in case of ROWNUM<100000 it takes arounf 1.5 Minutes.

    What is the problem behind it???.

    Thanks & Regards
    Rasmi

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    One point is when you do the select is it getting the last row? I can run a huge query which returns the first row in less than a second, however the last row returns after two hours as it is fetching back 10 million rows. So just because you get the first set of rows back quickly doesnt mean you select is any faster than your insert. Stick tracing on and see.

    What I would suggest to speed up your insert is try the /*+ APPEND */ hint inyour insert. Also if it is a temp table is it a global temporary table as these are faster than an ordinary table. Thirdly it helps if TAB2 is on a different disk (or set of disks) to your NAD_ADDRESS table. Also tuning your log buffer and archived logs helps.

    Alan

Posting Permissions

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