sql with order a desc, b desc

[reydp]

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

[PAVB]

(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.

[reydp]

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 ?

[davey23uk]

if you dont need the order by - remove it - if you need it well you gotta keep it

[reydp]

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?

[PAVB]

Limit the number of rows returned by you inline view.

[reydp]

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?

[PAVB]

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:

(
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!

[reydp]

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".

[PAVB]

I'm not sure if you're serious or not.

Dead serious always I'm