Results 1 to 8 of 8

Thread: optimize query

  1. #1
    Join Date
    May 2003
    Location
    France
    Posts
    112

    Arrow Unanswered: optimize query

    hye,
    I have the following query which the result is ok, but i can't see explain plan of the subquerys; so i'can't optmimize ....

    table carte is 3000000 rows (PK is iccid)
    table valeurs_carte is 27000000 rows hash partionned by iccid (PK is iccid,code_variable)

    both tables are parallelize

    select iccid ,msisdn,
    (select valeur from valeurs_carte v where v.iccid = a.iccid and code_variable=9) val9,
    (select valeur from valeurs_carte v where v.iccid = a.iccid and code_variable=10) val10,
    (select valeur from valeurs_carte v where v.iccid = a.iccid and code_variable=11) val11,
    (select valeur from valeurs_carte v where v.iccid = a.iccid and code_variable=12) val12 ,
    (select valeur from valeurs_carte v where v.iccid = a.iccid and code_variable=40) val40,
    (select valeur from valeurs_carte v where v.iccid = a.iccid and code_variable=44) val44,
    (select valeur from valeurs_carte v where v.iccid = a.iccid and code_variable=48) val48,
    (select valeur from valeurs_carte v where v.iccid = a.iccid and code_variable=52) val52
    from carte a where numero_of=717095 order by 1

    Help apprecied

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: optimize query

    There is no way to get that explain plan, as you have discovered.

    However, I would hazard a guess that it will be similar to the plan for:

    select iccid ,msisdn,
    from carte a where numero_of=717095 order by 1

    with an additional NESTED LOOP and 8 copies of the plan for:

    select valeur from valeurs_carte v where v.iccid = :num1 and code_variable=:num2

    You could of course re-write it as 8 outer joins.

    I can't help feeling I don't like your table design - too generic for it's own good.

  3. #3
    Join Date
    May 2003
    Location
    France
    Posts
    112

    Re: optimize query

    Originally posted by andrewst
    There is no way to get that explain plan, as you have discovered.

    However, I would hazard a guess that it will be similar to the plan for:

    select iccid ,msisdn,
    from carte a where numero_of=717095 order by 1

    with an additional NESTED LOOP and 8 copies of the plan for:

    select valeur from valeurs_carte v where v.iccid = :num1 and code_variable=:num2

    You could of course re-write it as 8 outer joins.

    I can't help feeling I don't like your table design - too generic for it's own good.
    Thanks,
    I've check the explain plan for these queries; all is ok
    but i suspect an incorrect way when including these subqueries

    i can't rewrite it because i want to have rows of VALEURS_CARTE as columns

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: optimize query

    Originally posted by ndu35
    i can't rewrite it because i want to have rows of VALEURS_CARTE as columns
    I don't understand that last point: your scalar subqueries are (must be) returning single values, not multiple rows, so they should give the same result as the corresponding outer join.

  5. #5
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    I think Tony is right, by the PK's on carte and valeurs_carte, the subqueries should all be single row.

    I'm not sure the multiple joins would be most efficient though. Depending on the number of rows and the datatype of "valeurs_carte"."valeur", I might be tempted to go for the single join, with group by/sum.

    See attached SQL for 8 joins though :-) It is Plannable.

    HTH
    Bill
    Attached Files Attached Files

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    As billm suggests above you could try the following technique

    select iccid ,msisdn,
    max(decode(code_variable,9, valeur, null)) val9,
    max(decode(code_variable,10, valeur, null)) val10,
    max(decode(code_variable,11, valeur, null)) val11,
    max(decode(code_variable,12, valeur, null)) val12,
    max(decode(code_variable,40, valeur, null)) val40,
    max(decode(code_variable,44, valeur, null)) val44,
    max(decode(code_variable,48, valeur, null)) val48,
    max(decode(code_variable,52, valeur, null)) val52,
    from carte a, valeurs_carte v
    where numero_of=717095
    and v.iccid = a.iccid
    and v.code_variable in (9,10,11,12,40,44,48,52)
    group by a.iccid, msisdn
    order by a.iccid

  7. #7
    Join Date
    May 2003
    Location
    France
    Posts
    112
    Originally posted by AlanP
    As billm suggests above you could try the following technique

    select iccid ,msisdn,
    max(decode(code_variable,9, valeur, null)) val9,
    max(decode(code_variable,10, valeur, null)) val10,
    max(decode(code_variable,11, valeur, null)) val11,
    max(decode(code_variable,12, valeur, null)) val12,
    max(decode(code_variable,40, valeur, null)) val40,
    max(decode(code_variable,44, valeur, null)) val44,
    max(decode(code_variable,48, valeur, null)) val48,
    max(decode(code_variable,52, valeur, null)) val52,
    from carte a, valeurs_carte v
    where numero_of=717095
    and v.iccid = a.iccid
    and v.code_variable in (9,10,11,12,40,44,48,52)
    group by a.iccid, msisdn
    order by a.iccid
    Thanks for your help,
    AlanP your solution is write but ,as bill said, no efficient for performance.
    I've succeed to improve perf by using correct Hints in both query and subqueries; but now my interrogation is why oracle 9.2 don't choose the correct plan itself
    i've check statistics => ok

    i suspect incorrect values in init.ora for optimizer
    which force oracle not choosing index way

    can someone give me experience with adjustment of optimizer_index_cost_adj ?

    Thanks for advance

  8. #8
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Small correction to your post - I can't take any credit for pre-guessing the performance of multiple joins or the single join with group by. I was tempted to look at both but didn't :-)

    Without knowing the statistics for both tables and their indexes it's not easy to look at performance tuning. I have an introduction to SQL performance tuning on my personal site if it's of any use. Maybe it will point you in the right direction?

    http://www.billmagee.co.uk/oracle/sqltune/index.html

    HTH
    Bill

Posting Permissions

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