| Order by some columns [message #601534] |
Thu, 21 November 2013 23:14  |
 |
yuvipoy
Messages: 5
Registered: November 2013
|
Junior Member |
|
|
Hi i am new to this.
i am having a requirement like
I am having a table with 100 columns and with 1 million records.
out of 1 million records need to sort the record based on the client requirement and return back the same.
Client 1 might ask for some 100 records alone with column5 asc where rownum between 10000 and 10100 out of 1 million
Client 2 might ask for some 10k records with column9 asc, column24 desc where rownum between 20000 and 30000 out of 1 million
and so and so...
thess columns will not have any index.
how to sort the data much faster way
i have some thing like this
select a.*
from (
select col_name1.....col_name100
from mytable
order
by col_name5 desc
, col_name8
) a
where rownum <=10000
but it is time consuming.
|
|
|
|
|
|
|
|
|
|
| Re: Order by some columns [message #602325 is a reply to message #601538] |
Tue, 03 December 2013 03:56  |
|
yuvipoy
Messages: 5
Registered: November 2013
|
Junior Member |
|
|
How do i order by a column with the minimum time which is having 1 million rows with some 100 columns
Here is the Execution Plan of my table.
Elapsed: 00:00:23.46
Execution Plan
----------------------------------------------------------
Plan hash value: 182415445
--------------------------------------------------------------------------------
---
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
---
| 0 | SELECT STATEMENT | | 755K| 13M| | 18116 (1)| 00:03:3
8 |
| 1 | SORT ORDER BY | | 755K| 13M| 20M| 18116 (1)| 00:03:3
8 |
| 2 | TABLE ACCESS FULL| mytable | 755K| 13M| | 13671 (1)| 00:02:4
5 |
--------------------------------------------------------------------------------
---
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
68 recursive calls
0 db block gets
50193 consistent gets
50341 physical reads
0 redo size
11743757 bytes sent via SQL*Net to client
588486 bytes received via SQL*Net from client
53463 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1001920 rows processed
[Updated on: Tue, 03 December 2013 04:02] Report message to a moderator |
|
|
|
| Re: Order by some columns [message #602337 is a reply to message #602325] |
Tue, 03 December 2013 04:27 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Please read and follow How to use [code] tags and make your code easier to read?
If the columns in the order by are in an index then oracle can use that index to find the data and avoid the scan.
If the columns aren't in an index, or if you're getting too much data for oracle to consider using it then you are left with two options:
1) enable parallel processing - you'll need enterprise edition for this
2) buy better hardware.
|
|
|
|
| Re: Order by some columns [message #602347 is a reply to message #602325] |
Tue, 03 December 2013 04:43 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
You might be able to improve the fetch time by increasing your array size to the maximum (5000 for SQL*Plus). And you should confirm that the session has enough PGA to run the statement optimally (see v$sql_workarea).
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Order by some columns [message #602410 is a reply to message #602405] |
Wed, 04 December 2013 04:01 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
I'd not be adding parallel to speed something up which it appears users can call on demand. That's a VERY bad idea. (I'm aware of statement queuing/automagic DOP but suspect they'll not be in play).
From your first post I also don't think you know what rownum is or how it works. Unless of course that is just how you've worded it and there is some deterministic record identifier they are using to limit it.
|
|
|
|
| Re: Order by some columns [message #602454 is a reply to message #601534] |
Wed, 04 December 2013 07:13 |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
yuvipoy wrote on Thu, 21 November 2013 23:14Hi i am new to this.
i am having a requirement like
I am having a table with 100 columns and with 1 million records.
out of 1 million records need to sort the record based on the client requirement and return back the same.
Client 1 might ask for some 100 records alone with column5 asc where rownum between 10000 and 10100 out of 1 million
Client 2 might ask for some 10k records with column9 asc, column24 desc where rownum between 20000 and 30000 out of 1 million
<snip>
Sounds suspiciously like this : https://forums.oracle.com/thread/2609847
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
