| Order by Clause is too slow [message #482516] |
Fri, 12 November 2010 04:04  |
oravijay
Messages: 43
Registered: March 2010
|
Member |
|
|
Dear Oracle Experts,
I am facing a query which is very slow takes more than 1 hour to fetch the record
Query:
SQL> SELECT * FROM (SELECT Object1.Name, Classid, Clearance, Created, UpdatedDate, CreatedBy, UpdatedBy, ObjContent2.* FROM OBJECT1,
2 ObjContent2 WHERE Object1.ClassId=19 AND ObjContent2.ObjectId=Object1.ObjectId ORDER BY Rating desc ) WHERE ROWNUM <= 1000
3 /
Description:
No.Of rows in the object1 table: 6266491
No of rows in the Objcontent2 table: 6222058
Total number of columns 26 in both the table.
Exectuin plan for this query
SQL> SELECT * FROM (SELECT Object1.Name, Classid, Clearance, Created, UpdatedDate, CreatedBy, UpdatedBy, ObjContent2.* FROM OBJECT1,
2 ObjContent2 WHERE Object1.ClassId=19 AND ObjContent2.ObjectId=Object1.ObjectId ORDER BY Rating desc ) WHERE ROWNUM <= 1000
3 /
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 4077891311
------------------------------------------------------------------------------------------------------------------------------------- ------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------- ------
| 0 | SELECT STATEMENT | | 1000 | 5832K| | 527K (1)| 01:45:30 | | |
|* 1 | COUNT STOPKEY | | | | | | | | |
| 2 | VIEW | | 482K| 2747M| | 527K (1)| 01:45:30 | | |
|* 3 | SORT ORDER BY STOPKEY | | 482K| 1081M| 1256M| 527K (1)| 01:45:30 | | |
| 4 | NESTED LOOPS | | | | | | | | |
| 5 | NESTED LOOPS | | 482K| 1081M| | 290K (1)| 00:58:11 | | |
| 6 | TABLE ACCESS BY GLOBAL INDEX ROWID| OBJECT1 | 482K| 32M| | 1436 (1)| 00:00:18 | ROWID | ROWID |
|* 7 | INDEX RANGE SCAN | IX_OBJECT_CLASSID1 | 482K| | | 241 (1)| 00:00:03 | | |
|* 8 | INDEX RANGE SCAN | IX_OBJCONTENT_OBJECTID2 | 1 | | | 1 (0)| 00:00:01 | | |
| 9 | TABLE ACCESS BY GLOBAL INDEX ROWID | OBJCONTENT2 | 1 | 2280 | | 1 (0)| 00:00:01 | ROWID | ROWID |
------------------------------------------------------------------------------------------------------------------------------------- ------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=1000)
3 - filter(ROWNUM<=1000)
7 - access("OBJECT1"."CLASSID"=19)
8 - access("OBJCONTENT2"."OBJECTID"="OBJECT1"."OBJECTID")
Indexes on object1 table
-------------------------
SQL> select INDEX_NAME, COLUMN_NAME from user_ind_columns where table_name='OBJECT1';
INDEX_NAME COLUMN_NAME
-------------------- --------------------
PK_OBJECT1 OBJECTID
IX_OBJECT_CLASSID1 CLASSID
IX_OBJECT_NAME1 NAME
Indexes on objcontent2 table
-----------------------------
select INDEX_NAME, COLUMN_NAME from user_ind_columns where table_name='OBJCONTENT2';
INDEX_NAME COLUMN_NAME
------------------------- -------------------------
IX_objcontent2_rating RATING
IX_OBJCONTENT_OBJECTID2 OBJECTID
IX_SEARCHINDEXID2 SEARCHINDEXID
IX_CONTENT_TYPE2 CONTENTTYPE
BOTH the table are partitioned by objectid column
also:
if i query order by objcontent2.objectid
It takes only milli seconds to show the results
SQL> SELECT * FROM (SELECT Object1.Name, Classid, Clearance, Created, UpdatedDate, CreatedBy, UpdatedBy, ObjContent2.* FROM OBJECT1,
2 ObjContent2 WHERE Object1.ClassId=19 AND ObjContent2.ObjectId=Object1.ObjectId ORDER BY objcontent2.objectid desc ) WHERE ROWNUM <= 1000
3 /
Note:
here objectid is not null ,unique
but the column rating will allow null and Presently the rating column have the values 0 in all rows except 4 rows
Please let me know the way to speed up the query
Kindly advise me,If any thing wrong
Thanks in Advance
Vijay
|
|
|
|
|
|
| Re: Order by Clause is too slow [message #482527 is a reply to message #482522] |
Fri, 12 November 2010 04:22  |
oravijay
Messages: 43
Registered: March 2010
|
Member |
|
|
Also
SQL> desc object1
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECTID NOT NULL NUMBER(38)
NAME VARCHAR2(512)
CLASSID NOT NULL NUMBER(38)
CLEARANCE NUMBER(10)
CREATED NOT NULL TIMESTAMP(6)
UPDATEDDATE TIMESTAMP(6)
CREATEDBY VARCHAR2(255)
UPDATEDBY VARCHAR2(255)
SQL> desc objcontent2
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECTID NOT NULL NUMBER(38)
PRIVACY NUMBER
EFFECTIVEPRIVACY NUMBER
NUMUSERREAD NUMBER
ISPREVREAD CHAR(1)
PUBLICBOOKMARKCOUNT NUMBER
PRIVATEBOOKMARKCOUNT NUMBER
RATING NUMBER
CONTENTTYPE VARCHAR2(255)
SEARCHINDEXID VARCHAR2(255)
SUMMARY VARCHAR2(1024)
BODY BLOB
TEXTCONTENT CLOB
GENRE VARCHAR2(255)
SOURCE VARCHAR2(255)
AUTHOR VARCHAR2(255)
AUTHOREMAIL VARCHAR2(255)
DATEACQUISITION DATE
DATEPUBLICATION DATE
Kindly let me know for any other information
Regards
Vijay
|
|
|
|
| Re: Order by Clause is too slow [message #482538 is a reply to message #482527] |
Fri, 12 November 2010 06:07 |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
First and foremost could you please format your post as it is very difficult to read.
Secondly read the very first link in this forum performance tuning sticky guide on how to performance tuning request. Without that information it is very difficult to suggest anything.
General questions like
a) Is your statistics are upto date
b) Are the indexes global or local
c) Is the estimation from the optimizer is comparable to the real estiamte or is it way off.
If you could answer the above question I am sure somebody will be able to point you in the right direction.
Regards
Raj
|
|
|
|
| Re: Order by Clause is too slow [message #482575 is a reply to message #482538] |
Fri, 12 November 2010 16:59 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
It is possible for an ORDER BY to use an index, and that is probably what is happening in your 'fast' case. For this to happen, you must:
- Select from a single table
- If there is a WHERE clause, it must be on a leading subset of the indexed columns
- The ORDER BY must be on a leading subset of the indexed columns.
Your query is not using the index to ORDER BY because of the join. This forces it to sort the ENTIRE result set before returning the first row.
The best way to utilise an index for ORDER BY and ALSO join to other sources is to construct the logic manually in PL/SQL. You can select and ORDER BY a single table in the main cursor, then select other tables inside the cursor. If you place all of this in a PIPELINED TABLE FUNCTION, then you can use it in other SQL statements.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
