sql with order a desc, b desc
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: sql with order a desc, b desc

  1. #1
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857

    sql with order a desc, b desc

    Can anybody help me with this sql:
    SQL> SET AUTOTRACE ON
    SQL> select msg_oid
    2 from (select msg_oid, msg_log_time
    3 from mb_fix_message
    4 where (msg_message_type!='0' and msg_message_type!='1')
    5 order by msg_log_time desc, msg_oid desc)
    6 where rownum <= 10
    7 order by msg_log_time desc, msg_oid desc
    8 /

    MSG_OID
    ----------
    10213059
    10213058
    10213057
    10213056
    10204431
    10204430
    10204429
    10204428
    10195803
    10195802

    10 rows selected.


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=136968 Card=10 Byt
    es=220)

    1 0 COUNT (STOPKEY)
    2 1 VIEW (Cost=136968 Card=9929518 Bytes=218449396)
    3 2 SORT (ORDER BY STOPKEY) (Cost=136968 Card=9929518 Byte
    s=158872288)

    4 3 TABLE ACCESS (FULL) OF 'MB_FIX_MESSAGE' (TABLE) (Cos
    t=136968 Card=9929518 Bytes=158872288)





    Statistics
    ----------------------------------------------------------
    1 recursive calls
    0 db block gets
    626289 consistent gets
    622304 physical reads
    0 redo size
    521 bytes sent via SQL*Net to client
    508 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    10 rows processed


    If somebody can help rewrite the sql by breaking the:
    ORDER BY MSG_LOG_TIME DESC, MSG_OID DESC
    I believe this is where the overhead goes.

    thanksinadvance
    ---------------

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by reydp
    (msg_message_type!='0' and msg_message_type!='1')
    This is a horrible predicate.

    There is just one thing uglier than a "NOT EQUAL" condition which is, two "NOT EQUAL" conditions joined by and AND.

    Fix the inline view.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Quote Originally Posted by PAVB
    This is a horrible predicate.

    There is just one thing uglier than a "NOT EQUAL" condition which is, two "NOT EQUAL" conditions joined by and AND.

    Fix the inline view.
    Yeah...right.

    The msg_message_type has a low cardinality, 5/10000000.
    I even used it with bitmap idx. with MSG_MESSAGE_TYPE in ('A','2','3)
    or MSG_MESSAGE_TYPE in ('0','1') it doesn't make any difference anyway.
    Just happened that I have pasted the original SQL code. By the way,
    I also remove the order by .. desc, .. desc in the outer.

    It is the ORDER BY .. DESC, .. DESC that I'm concern.
    Any idea ?
    ---------------

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    if you dont need the order by - remove it - if you need it well you gotta keep it

  5. #5
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Quote Originally Posted by davey23uk
    if you dont need the order by - remove it - if you need it well you gotta keep it
    I need it, in fact, the output should have the lastN.
    anyway to have this optmze with HINT or any tricks?
    ---------------

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Limit the number of rows returned by you inline view.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Quote Originally Posted by PAVB
    Limit the number of rows returned by you inline view.
    That's exactly what I need to figure out, with ORDER BY desc then I have a problem.

    what's your idea?
    ---------------

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    OK... here is the deal.

    1- Since you have stated you need the ORDER BY, that's not your problem.
    That's a business requirement which by definition can't be a problem.

    2- Your problem is how much data are you sorting at ORDER BY time.
    Then, you have to limit how much data are you sorting.

    3- How to limit how much data are you sorting?
    You have to limit the number of rows returned by your inline view.

    4- How to do that?
    You have to stop doing Full Table Scan on that table.
    Since I do not have access to your system, follows an extreme solution.

    a) Build a non-unique index on mb_fix_message.msg_message_type
    b) Create a pivot table with a single column: msg_message_type
    c) Populate your pivot table with a row for each msg_message_type value you want to retrieve from mb_fix_message
    d) Gather fresh stats on both your pivot table and your mb_fix_message table, including indexes.
    e) Now, make your pivot table the driving table and your original table your lookup table by writing your inline view like:
    Code:
    (
    select  a.msg_oid, a.msg_log_time
    from    mb_fix_message a,
              pivot_table b
    where  b.msg_message_type = a.msg_message_type
    )
    f) Do an Explain Plan of your inline view alone, finetune it until you have FTS on pivot_table and index range search on original table.

    Good luck!
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  9. #9
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Quote Originally Posted by PAVB
    Good luck!
    I'm not sure if you're serious or not.
    Because sometimes I used "Good Luck" to test the poster.

    With your suggestion, creating pivot table will help improve of the query.

    But what will I do to maintain the table afterthen?
    You're suggesting me to solve a problem by adding another problem.

    Anyway, I find way to resolve the problem.

    Thanks for the "Good luck".
    ---------------

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by reydp
    I'm not sure if you're serious or not.
    Dead serious always I'm
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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