-
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
---------------
-
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.
-
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 ?
---------------
-
if you dont need the order by - remove it - if you need it well you gotta keep it
-
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?
---------------
-
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.
-
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?
---------------
-
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.
-
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".
---------------
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|