Order by


I know this is very optimistic question but just want to check i have any luck.

In our client ware house, there is a query running on 115 million rows table doing order by 29 columns. As we have a limited pga_aggregate_target (6G) this sort is going to TEMP table space and is consuming lot of time. The query in this example is run for 9 hours.

The query is like this ...

select
col 1,
col 2, ....
col x
from table order by
col 1 ...
col 29

Just trying to find is there any work around available to get rid of order by on such a big table.

Your suggestions are highly appreciated.

Posted On: Friday 16th of November 2012 03:02:03 AM Total Views:  726
View Complete with Replies

RELATED TOPICS OF Oracle PROGRAMMING LANGUAGE




order by ... question

I have a table with one field, F1 values : A001 B001 A999 B999 0001 9999 'select F1 from tab order by F1' gives me A001 A999 B001 B999 0001 9999 I like to have as result 0001 9999 A001 A999 B001 B999 How can i do this Gert
VIEWS ON THIS POST

194

Posted on:

Friday 2nd November 2012
View Replies!

"Got Packets out of order" error while execting LOAD command From ASP.Net C# while it works fine in MYSQL..

I execute following LOAD query of MYSQL,which works fine,when i use this query in MySql,but when i try to execute it through .Net it gives me error: Got Packets out of order error. My Code:- try { MySqlCommand cmdMySQL = conn.CreateCommand(); if (conn.State == ConnectionState.Open) conn.Close(); conn.ConnectionString = bs.constring; conn.Open(); tran = conn.BeginTransaction(); cmdMySQL.CommandText = @"LOAD DATA LOCAL INFILE 'C:\WebSite1\UploadData\state1.txt' INTO TABLE state1 FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n'"; cmdMySQL.ExecuteNonQuery(); tran.Commit(); } catch (Exception ex) { tran.Rollback(); Label1.Text = "Data is not imported!! Error:" + ex.ToString(); } finally { conn.Close(); conn.Dispose(); } MySql Load Command is Correct,i have checked many times(and my C drive have that file & everything in that cmd is all right..) Can any one tell me what can help me... Or at least what this error means.. This command i have used with asp.net & it had worked fine..
VIEWS ON THIS POST

237

Posted on:

Friday 2nd November 2012
View Replies!

How to use order by clause for time?

I am using oracle database and in database i have one column appdate that contain date with time. I have five records with same date but timing is diffrent. How to write a oracle query to orader the data based on timing asc and dsc order. Please provide some query and solve my issue.
VIEWS ON THIS POST

201

Posted on:

Friday 2nd November 2012
View Replies!

Error Code: 1054. Unknown column 'StartDate' in 'order clause'

The below is my sql statement, but it pop out error in mtsql can tell me what the error SelectRECID,'1'ASSeqno,'1'ASScheduleContinues,MessageDirection FROMtblBranch_ScheduleWHEREDATEDIFF('2011-07-07',StartDate)>0ANDDATEDIFF(EndDate,'2011-07-07')>=0 ANDBranchRECID='b6a2edf0-1ff1-4b21-8121-30a0197b33ea'UNION SelectRECID,'2'ASSeqno,ScheduleContinues,MessageDirection FROMtblBranch_ScheduleawhereBranchRECID='b6a2edf0-1ff1-4b21-8121-30a0197b33ea'OrderbyStartDateDESC I got the below error Error Code: 1054. Unknown column 'StartDate' in 'order clause'
VIEWS ON THIS POST

206

Posted on:

Friday 2nd November 2012
View Replies!

Shell script with an sql order : strange behavior ?

, I noticed the following thing : I have a shell script in which a sql order is executed : ********************************************************* export ORACLE_SID=$1 $ORACLE_HOME/bin/sqlplus /nolog Connected. SQL> SQL> select * from sys.v * ERROR at line 1: ORA-00942: table or view does not exist Doing the same thing with a non v$* views, the result is OK. (for v$*, I have to put the select order in a sql script and call it from the shell). Do you have an explanation of this behavior
VIEWS ON THIS POST

239

Posted on:

Saturday 3rd November 2012
View Replies!

row order of a select result

, In the same database, we performed a copy of a schema (exp/imp fromuser1 touser2). One of our developer noticed that he has a result not ordered in the same way when he execute the same statement (a jointure beteween tow tables). I asked him to you ORDER BY to garantee the same order ... But I would like to know more about the order of a select statement (in our case, we have the same result, and I think that rows were inserted in the same order ...).
VIEWS ON THIS POST

153

Posted on:

Saturday 3rd November 2012
View Replies!

Poor Performance of order by Clause

I have a query which takes 7 seconds to comeup with results when it has an order by clause and only 1 second when without it. Below are the tkpfro outputs of both of them Code: ******************************************************************************** SELECT * FROM mv_flat_news_story t WHERE region_id IN (SELECT child_region_id FROM flat_regionhierarchy WHERE parent_region_id = 50015 UNION SELECT 50015 FROM dual) AND product_location_id >= 500 AND product_location_id < 600 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.06 0.07 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 63 0.83 7.85 8676 17533 55 6247 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 65 0.89 7.92 8676 17533 55 6247 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 94 Rows Row Source Operation ------- --------------------------------------------------- 6247 HASH JOIN 789 VIEW VW_NSO_1 789 SORT UNIQUE 789 UNION-ALL 788 INDEX RANGE SCAN (object id 240394) 1 TABLE ACCESS FULL DUAL 20037 TABLE ACCESS FULL MV_FLAT_NEWS_STORY ******************************************************************************** SELECT * FROM mv_flat_news_story t WHERE region_id IN (SELECT child_region_id FROM flat_regionhierarchy WHERE parent_region_id = 50015 UNION SELECT 50015 FROM dual) AND product_location_id >= 500 AND product_location_id < 600 ORDER BY t.date_of_story DESC call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.05 0.05 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 63 1.08 7.63 9174 17482 57 6247 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 66 1.13 7.68 9174 17482 57 6247 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 94 Rows Row Source Operation ------- --------------------------------------------------- 6247 SORT ORDER BY 6247 HASH JOIN 789 VIEW VW_NSO_1 789 SORT UNIQUE 789 UNION-ALL 788 INDEX RANGE SCAN (object id 240394) 1 TABLE ACCESS FULL DUAL 20037 TABLE ACCESS FULL MV_FLAT_NEWS_STORY The column that is being ordered by has an index on it. I am quite baffelled by a couple of things 1. The elapsed time shows apprximately the same in both tkprofs but the actual results came up faster without order by clause. 2. How do I imporove the performance of this query.
VIEWS ON THIS POST

178

Posted on:

Saturday 3rd November 2012
View Replies!

order of index columns

I have a table test ( country varchar2(32), state varchar2(32), city varchar2(32), ... ) Assuming that: 1. 50% rows of test have country value 'USA' while another 50% are all other countries. 2. My query is something like: select * from test where country='USA' and state='New York' and city='New York' I thought I_TEST_2 on TEST(city, state, country) is better than I_Test_1 on TEST(country, state, city) But the explain plan shows no difference - query will use either index if exists. Can anyone explain why my thought is wrong
VIEWS ON THIS POST

159

Posted on:

Saturday 3rd November 2012
View Replies!

OleDbDataAdapter, EXCEL and sorting order

Sort order in SQL Server is collation related, if your data is in German then you need to do German collation on the columns you are using to store the Excel data. The thread below covers all you need. Post again if you still need help. http://forums.asp.net/thread/1316949.aspx
VIEWS ON THIS POST

155

Posted on:

Saturday 3rd November 2012
View Replies!

order of import

hi, can someone tell what is the order while importing ... eg: tables,constraints,functions and procedures....etc \t\t Many
VIEWS ON THIS POST

289

Posted on:

Sunday 4th November 2012
View Replies!

order by non-table attribute help

I wonder if someone could help me out. I have created the following query: SELECT order_id,sum(suggested_whlsl_price*quantity) FROM order_item, product WHERE product.p_id = order_item.product_id GROUP BY order_id This displays my order ID numbers along with the total value of the order. However I can only get it to order by order_id. What I want to actually do is order it by the value of the order and only return, say the first 5 rows, so in effect im selecting the 5 most expensive orders from the database. Now I'm still fairly new to Oracle, however I have been studying hard and I'm either missing something fairly obvious or I'm getting brain freeze because I've been trying for hours to get it to work now. If someone could help me out with this I would HUGELY appreciate it Thank you in advance Rich
VIEWS ON THIS POST

310

Posted on:

Sunday 4th November 2012
View Replies!

using sequence cause inserted rows not in order

Halu.. i wanna ask about inserted records using sequence. I created a seq for table t1. I insert 10 rows or record and the id (which is the pk) shows the records in order (1.2.3.4.5.6.7.8.9.10). Then i delete one of the record (where id=3). I inserted one record into table t1 but when i select back the id appeared to be not in order which is id=1,2,11,4,5,6,7,8,9,10. Why is this happening Why the next record is inserted in the space of id=3 whihch i had deleted And how to resolve this
VIEWS ON THIS POST

237

Posted on:

Wednesday 7th November 2012
View Replies!

SQL order to have max-1, max-2, max-3 ...

We want to have the max, max-1, ..., max-9 of a table column. So, suppose that you have a table with 1 column (C1) and with the following rows : C1 --- 1 2 2 3 3 4 5 6 7 8 9 11 13 17 - first sql statement to have the max-1 : 13 - second sql statement to have max-2 : 11 - ... to have max-1, I used this statement : select max(C1) from (select C1 from (select C1 from z_tab where C1 < (select max(C1) from z_tab)) ) could you suggest the best method to have result deed.
VIEWS ON THIS POST

238

Posted on:

Wednesday 7th November 2012
View Replies!

order of sql statements

Hi friends.. Can u please let me know the best order for writing a sql statement.What i wanted to know was if a sql statement contains a join followed by a subquery...should the subquery come first and then the join or the join and then subquery... regards anandkl
VIEWS ON THIS POST

196

Posted on:

Wednesday 7th November 2012
View Replies!

order

I was wondering is there any order to follow while runnning the following three or it doesn't matter coalesce tablspace index rebuild Anlayze tables
VIEWS ON THIS POST

141

Posted on:

Wednesday 7th November 2012
View Replies!

outer join order

We have a query which does something similar to where a.id (+) = b.id and b.id (+) = c.id and c.other_id (+) = d.other_id and expensive_function (arbitrary_parameter, d.column) >= arbitrary_number It's my understanding that the only valid path given all the outer joins is to access table D first, in this case with a full table scan since there are no other predicates. Unfortunately given this order the query will probably never return since it needs to execute an expensive function on every row in the relativly large table. Is it possible to somehow change the path to filter D last If I understand outer join restrictions correctly I don't think so, but I'm a little hazy so I thought I'd ask. So far with a few quick ORDERED tests nothing works.
VIEWS ON THIS POST

166

Posted on:

Wednesday 7th November 2012
View Replies!

ordering

hi, all: I have a question need you help. currently we need to design a website maybe just like myYAHOO. user can config it themself. let's say user1 want to see stock first , then top stories from Reuters second, Business from Reuters third..... and user 2 want to see top stories from Reuters first, stock second, and Business from Reuter third..... what i did is create a table named userConfig(userid, item ,order); the content of it would be user1,stock,1 user1,TopStories,2 user1,Business,3 user2,TopStories,1 user2,stock,2 user2,Business,3 it works, but i don't think it is a good solution, let's say user don't want to see stock anymore, at website he(she) simple remove stock item, what i need to do is remove record user1,stock,1 and then modify all other user1's records. subtract one from the order column. do you guy have any better idea
VIEWS ON THIS POST

149

Posted on:

Wednesday 7th November 2012
View Replies!

order by question

create table t (name varchar2(30)); insert into t values('Zoro'); insert into t values('quipement'); select name from t order by name; select name from t order by name; NAME ------------------------------ Zoro quipement How can i make the name quipement ordered before the name Zoro
VIEWS ON THIS POST

146

Posted on:

Wednesday 7th November 2012
View Replies!

Update ordernumber in Mysql from ordernumber in Excel

I have the following code . protected void Page_Load(object sender, EventArgs e) \t { \t\t\t DataSet ds = GetExcel(@"C:\Documents and Settings\My Documents\tes1t.xls"); \t\t\t System.Data.DataTable dt = ds.Tables[0]; \t\t\t GridView1.DataSource = ds; \t\t\t //ds.Tables.Contains("ordernumber"); \t\t GridView1.DataBind(); \t\t\t //selectmethod(); \t\t\t \t\t\t //your connection string \t\t\t OdbcConnection con = new OdbcConnection(ConnStr); \t\t\t int i; \t\t int ia= ds.Tables["dtExcel"].Rows.Count - 1; \t\t\t for (i = 0; i
VIEWS ON THIS POST

142

Posted on:

Wednesday 7th November 2012
View Replies!

Select MAX(order_date) ??

Hi , I'm NOT a developer, so rarely get stuck into code. This sounds easy but I'm having trouble. Here goes... I need to select the Person ID (PID), order_date and order_amount from transaction table. Each PID can have many transaction rows, but I need to return the row with the latest transaction date for each PID. So, I'm looking at select pid,order_date,order_amount from transaction group by pid,max(order_date) ORA-00934: group function is not allowed here To further clarify, here's a sample of the transaction table PID ORDER_DATE ORDER_AMOUNT 12 12-JUL-02 12.99 12 13-JUL-02 45.99 12 14-JUL-02 2.99 13 22-MAY-02 33.99 13 14-JUN-02 22.99 I need to return 12 14-JUL-02 2.99 13 14-JUN-02 22.99 I'm probably being a bit thick here, but I'll admit to being less than experienced with SQL
VIEWS ON THIS POST

188

Posted on:

Thursday 15th November 2012
View Replies!