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

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.

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 ?

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

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?

Limit the number of rows returned by you inline view.

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?

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!

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

Quote:

---

Originally Posted by reydp

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

---

Dead serious always I'm :)

Quote:

---

Originally Posted by PAVB

Dead serious always I'm :)

---

Dead serious so you are :(

But likewise, the question remains the same.

I was able to completely optimized the SQL without pivot table.

processing 10M+ records from 5minutes down to 15 seconds.

curious are you not?
:)

nah... believe me, I'm not curious about that.

either way, am I good or what? just by hanging around you got smarter and solved the issue LOL

what if you tried

MSG_MESSAGE_TYPE = 'A'
or MSG_MESSAGE_TYPE =2
or MSG_MESSAGE_TYPE =3
or MSG_MESSAGE_TYPE =0
or MSG_MESSAGE_TYPE = 1

Quote:

---

Originally Posted by ndisang

what if you tried

MSG_MESSAGE_TYPE = 'A'
or MSG_MESSAGE_TYPE =2
or MSG_MESSAGE_TYPE =3
or MSG_MESSAGE_TYPE =0
or MSG_MESSAGE_TYPE = 1

---

What part of "I was able to completely optimized the SQL without pivot table.
processing 10M+ records from 5minutes down to 15 seconds." you didn't understand?

Reydp already solved it! :rolleyes:

Quote:

---

Originally Posted by PAVB

nah... believe me, I'm not curious about that.

either way, am I good or what? just by hanging around you got smarter and solved the issue LOL

---

it was 15 seconds with the autotrace, but to be precise it is only 10 seconds...
and with high end servers it will only take 5 seconds or less..

blow your mind...:D

problem is in FTS.
If you use this clause
msg_message_type!='0' and msg_message_type!='1'

It's bound to do FTS. Try to use some other predicate.
This is almost 10 million records table and doing FTS, so what you expect?

Quote:

---

Originally Posted by malay_biswal

problem is in FTS.

---

What's wrong with you guys?
Don't you read the thread before posting?
Are you unable to understand something as basic as "problem already solved?"

I give up :rolleyes:

HTML Code:

---

I give up

---

You better be.

Quote:

---

Originally Posted by PAVB

I give up :rolleyes:

---

It should be:

my give up :confused: