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 /
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.
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 ?
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.
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.
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.
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.
Reply With Quote
Originally Posted by reydp
Bookmarks