Home » RDBMS Server » Performance Tuning » ORDER BY CLAUSE
| ORDER BY CLAUSE [message #161777] |
Tue, 07 March 2006 02:43  |
rvnair123
Messages: 26
Registered: January 2006
|
Junior Member |
|
|
Hello,
I have a query as follows
SELECT CE_DASHBOARD_DISPLAY.DISPLAY, CE_DD_ORDERS.CRIT_ORDER_GROUP, CE_DD_ORDERS.CUSTOMER_NAME, CE_DD_ORDERS.AGE,
CE_DD_ORDERS.ORDER_NUM, CE_DD_ORDERS.LOC, CE_DD_ORDERS.TIME, CE_DD_ORDERS.LOB, CE_DD_ORDERS.QTY, CE_DD_ORDERS.COMMENTS,
CE_DD_ORDERS.TIE_NUMBER, CE_DD_ORDERS.OPR_TARGET, CE_DD_ORDERS.STC_VARIANCE, CE_DD_ORDERS.CURR_FACILITY,
CE_DD_ORDERS.LOCAL_CHANNEL, CE_DD_ORDERS.ACTUAL_SYSTEM_QTY, CE_DD_ORDERS.POSITION_AGE,
CE_DD_ORDERS.WIP_PROCESS_LOCATION, CE_DD_ORDERS.TASK_CODE, CE_DD_ORDERS.SALES_REP_NAME, CE_DD_ORDERS.ISSUES,
CE_DD_ORDERS.SVC_TAG, CE_DD_ORDERS.FAMILY_PARENT_DESC, CE_DD_ORDERS.SEGMENT, CE_DD_ORDERS.CHANNEL_DESC, CE_DD_ORDERS.CUSTOMER_NUM,
CE_DD_ORDERS.PAY_CODE, CE_DD_ORDERS.MERGE_FACILITY, CE_DD_ORDERS.MFG_FACILITY, CE_DD_ORDERS.WORKCENTER_ID,
CE_DD_ORDERS.FAMILY_DESC, CE_DD_ORDERS.POSITION_DAYS, CE_DD_ORDERS.MFG_LINE, CE_DD_ORDERS.PROCESS_AGE, CE_DD_ORDERS.WTCS_COMMENT,
CE_DD_ORDERS.ROUTE_DATE, CE_DD_ORDERS.WIP_STATUS, CE_DD_ORDERS.STAGGERED
FROM dpm_reporting_code.CE_DD_ORDERS LEFT OUTER JOIN dpm_reporting.CE_DASHBOARD_DISPLAY
ON CE_DD_ORDERS.ORDER_NUM = CE_DASHBOARD_DISPLAY.D_ORDER_NUM
AND CE_DD_ORDERS.TIE_NUMBER = CE_DASHBOARD_DISPLAY.D_TIE_NUMBER
AND CE_DD_ORDERS.CRIT_ORDER_GROUP = CE_DASHBOARD_DISPLAY.D_PRDCNTR
WHERE ROWNUM <= 3000 AND RECORD_TYPE IN ('S', 'T')
ORDER BY CRIT_ORDER_GROUP,ORDER_NUM, TIE_NUMBER
When i do explain plan(here dpm_reporting_code.CE_DD_ORDERS is view based on many tables),i find without order by
SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=69 Card=100 Bytes=458400)
COUNT (STOPKEY)
NESTED LOOPS (OUTER) (Cost=69 Card=100 Bytes=458400)
VIEW OF CE_DD_ORDERS (VIEW) (Cost=69 Card=100 Bytes=456200)
HASH JOIN (RIGHT OUTER) (Cost=125504 Card=182434 Bytes=87021018)
TABLE ACCESS (FULL) OF COMMENT_ROOT_CAUSE (TABLE) (Cost=3 Card=36 Bytes=1080)
HASH JOIN (RIGHT OUTER) (Cost=125497 Card=182434 Bytes=81547998)
TABLE ACCESS (FULL) OF LAST_ROUTE_POSITION (TABLE) (Cost=42 Card=25108 Bytes=351512)
NESTED LOOPS (Cost=125452 Card=182434 Bytes=78993922)
NESTED LOOPS (Cost=125234 Card=182434 Bytes=71331694)
HASH JOIN (RIGHT OUTER) (Cost=125038 Card=182434 Bytes=66405976)
INDEX (FULL SCAN) OF SALES_REP_INFO_IDX (INDEX (UNIQUE))
HASH JOIN (Cost=125035 Card=182434 Bytes=59291050)
INDEX (FAST FULL SCAN) OF ORDER_COMMENT_STORAGE_PK (INDEX (UNIQUE)) (Cost=749 Card=238130 Bytes=8096420)
VIEW (Cost=121200 Card=178250 Bytes=51870750)
UNION-ALL
INLIST ITERATOR
TABLE ACCESS (BY INDEX ROWID) OF BACKLOG_INTRADAY_SKU_REPORT_1 (TABLE) (Cost=5776 Card=1 Bytes=101)
INDEX (RANGE SCAN) OF BACKLOG_INTRA_SKU_INT_GEN_1 (INDEX) (Cost=5775 Card=1)
TABLE ACCESS (BY INDEX ROWID) OF BACKLOG_EXPAND_CSR_INTRA_RPT_2 (TABLE) (Cost=115424 Card=178249 Bytes=21211631)
INDEX (FULL SCAN) OF BACKLOG_INTRA_RPT_PRC_IDX_2 (INDEX) (Cost=2585 Card=200466)
INDEX (UNIQUE SCAN) OF CHANNELS_IDX (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=27)
INDEX (UNIQUE SCAN) OF SYS_IOT_TOP_189392 (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=42)
INDEX (UNIQUE SCAN) OF CE_DASHBOARD_DISPLAY (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=22)
With Order By
***************************************
SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=361459 Card=3000 Bytes=13752000)
SORT (ORDER BY) (Cost=361459 Card=3000 Bytes=13752000)
COUNT (STOPKEY)
NESTED LOOPS (OUTER) (Cost=125699 Card=182434 Bytes=836277456)
VIEW OF CE_DD_ORDERS (VIEW) (Cost=125504 Card=182434 Bytes=832263908)
HASH JOIN (RIGHT OUTER) (Cost=125504 Card=182434 Bytes=87021018)
TABLE ACCESS (FULL) OF COMMENT_ROOT_CAUSE (TABLE) (Cost=3 Card=36 Bytes=1080)
HASH JOIN (RIGHT OUTER) (Cost=125497 Card=182434 Bytes=81547998)
TABLE ACCESS (FULL) OF LAST_ROUTE_POSITION (TABLE) (Cost=42 Card=25108 Bytes=351512)
NESTED LOOPS (Cost=125452 Card=182434 Bytes=78993922)
NESTED LOOPS (Cost=125234 Card=182434 Bytes=71331694)
HASH JOIN (RIGHT OUTER) (Cost=125038 Card=182434 Bytes=66405976)
INDEX (FULL SCAN) OF SALES_REP_INFO_IDX (INDEX (UNIQUE))
HASH JOIN (Cost=125035 Card=182434 Bytes=59291050)
INDEX (FAST FULL SCAN) OF ORDER_COMMENT_STORAGE_PK (INDEX (UNIQUE)) (Cost=749 Card=238130 Bytes=8096420)
VIEW (Cost=121200 Card=178250 Bytes=51870750)
UNION-ALL
INLIST ITERATOR
TABLE ACCESS (BY INDEX ROWID) OF BACKLOG_INTRADAY_SKU_REPORT_1 (TABLE) (Cost=5776 Card=1 Bytes=101)
INDEX (RANGE SCAN) OF BACKLOG_INTRA_SKU_INT_GEN_1 (INDEX) (Cost=5775 Card=1)
TABLE ACCESS (BY INDEX ROWID) OF BACKLOG_EXPAND_CSR_INTRA_RPT_2 (TABLE) (Cost=115424 Card=178249 Bytes=21211631)
INDEX (FULL SCAN) OF BACKLOG_INTRA_RPT_PRC_IDX_2 (INDEX) (Cost=2585 Card=200466)
INDEX (UNIQUE SCAN) OF CHANNELS_IDX (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=27)
INDEX (UNIQUE SCAN) OF SYS_IOT_TOP_189392 (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=42)
INDEX (UNIQUE SCAN) OF CE_DASHBOARD_DISPLAY (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=22)
The problem is the output is displayed on .net screen has to be sorted,will sorting be faster in .net than oracle or is there work around for the same?
Thanks in advance,
Rekha
|
|
|
|
| Re: ORDER BY CLAUSE [message #161782 is a reply to message #161777] |
Tue, 07 March 2006 03:10  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| Quote: |
WHERE ROWNUM <= 3000 AND RECORD_TYPE IN ('S', 'T')
ORDER BY CRIT_ORDER_GROUP,ORDER_NUM, TIE_NUMBER
|
This is a bug.
As you can see in the plan, Oracle will first get 3000 random rows, and then sort those:
SORT (ORDER BY) (Cost=361459 Card=3000 Bytes=13752000)
COUNT (STOPKEY)
You should sort inside an inline view and then restrict the number of rows returned.
If you use the query in .net you should not worry about the max number of retrieved rows anyway, let your code handle that (bulk collect 3000 rows, or stop fetching after 3000 rows).
Generally speaking, ordering in the database is faster than outside. The database was made to handle data, so you should use it for it.
|
|
|
|
| Re: ORDER BY CLAUSE [message #161785 is a reply to message #161782] |
Tue, 07 March 2006 03:31 |
rvnair123
Messages: 26
Registered: January 2006
|
Junior Member |
|
|
Hello,
Thanks for the response
I changed my query as follows
SELECT DISPLAY, CRIT_ORDER_GROUP, CUSTOMER_NAME, AGE, ORDER_NUM, LOC, TIME, LOB, QTY,
COMMENTS, TIE_NUMBER, OPR_TARGET, STC_VARIANCE, CURR_FACILITY, LOCAL_CHANNEL, ACTUAL_SYSTEM_QTY, POSITION_AGE,
WIP_PROCESS_LOCATION, TASK_CODE, SALES_REP_NAME, ISSUES, SVC_TAG, FAMILY_PARENT_DESC, SEGMENT, CHANNEL_DESC,
CUSTOMER_NUM, PAY_CODE, MERGE_FACILITY, MFG_FACILITY,
WORKCENTER_ID, FAMILY_DESC, POSITION_DAYS, MFG_LINE, PROCESS_AGE, WTCS_COMMENT, ROUTE_DATE, WIP_STATUS, STAGGERED
FROM
(SELECT CE_DASHBOARD_DISPLAY.DISPLAY, CE_DD_ORDERS.CRIT_ORDER_GROUP, CE_DD_ORDERS.CUSTOMER_NAME, CE_DD_ORDERS.AGE,
CE_DD_ORDERS.ORDER_NUM, CE_DD_ORDERS.LOC, CE_DD_ORDERS.TIME, CE_DD_ORDERS.LOB, CE_DD_ORDERS.QTY, CE_DD_ORDERS.COMMENTS,
CE_DD_ORDERS.TIE_NUMBER, CE_DD_ORDERS.OPR_TARGET, CE_DD_ORDERS.STC_VARIANCE, CE_DD_ORDERS.CURR_FACILITY,
CE_DD_ORDERS.LOCAL_CHANNEL, CE_DD_ORDERS.ACTUAL_SYSTEM_QTY, CE_DD_ORDERS.POSITION_AGE,
CE_DD_ORDERS.WIP_PROCESS_LOCATION, CE_DD_ORDERS.TASK_CODE, CE_DD_ORDERS.SALES_REP_NAME, CE_DD_ORDERS.ISSUES,
CE_DD_ORDERS.SVC_TAG, CE_DD_ORDERS.FAMILY_PARENT_DESC, CE_DD_ORDERS.SEGMENT, CE_DD_ORDERS.CHANNEL_DESC, CE_DD_ORDERS.CUSTOMER_NUM,
CE_DD_ORDERS.PAY_CODE, CE_DD_ORDERS.MERGE_FACILITY, CE_DD_ORDERS.MFG_FACILITY, CE_DD_ORDERS.WORKCENTER_ID,
CE_DD_ORDERS.FAMILY_DESC, CE_DD_ORDERS.POSITION_DAYS, CE_DD_ORDERS.MFG_LINE, CE_DD_ORDERS.PROCESS_AGE, CE_DD_ORDERS.WTCS_COMMENT,
CE_DD_ORDERS.ROUTE_DATE, CE_DD_ORDERS.WIP_STATUS, CE_DD_ORDERS.STAGGERED,RECORD_TYPE
FROM dpm_reporting_code.CE_DD_ORDERS LEFT OUTER JOIN dpm_reporting.CE_DASHBOARD_DISPLAY
ON CE_DD_ORDERS.ORDER_NUM = CE_DASHBOARD_DISPLAY.D_ORDER_NUM
AND CE_DD_ORDERS.TIE_NUMBER = CE_DASHBOARD_DISPLAY.D_TIE_NUMBER
AND CE_DD_ORDERS.CRIT_ORDER_GROUP = CE_DASHBOARD_DISPLAY.D_PRDCNTR
WHERE RECORD_TYPE IN ('S', 'T')
ORDER BY CRIT_ORDER_GROUP,ORDER_NUM, TIE_NUMBER)
WHERE ROWNUM <= 3000
The Explain Plan is as follows
********************************
SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=356817 Card=3000 Bytes=13716000)
COUNT (STOPKEY)
VIEW (Cost=356817 Card=178609 Bytes=816600348)
SORT (ORDER BY STOPKEY) (Cost=356817 Card=178609 Bytes=818743656)
NESTED LOOPS (OUTER) (Cost=126000 Card=178609 Bytes=818743656)
VIEW OF CE_DD_ORDERS (VIEW) (Cost=125809 Card=178609 Bytes=814814258)
NESTED LOOPS (OUTER) (Cost=125809 Card=178609 Bytes=85017884)
HASH JOIN (RIGHT OUTER) (Cost=125757 Card=178609 Bytes=79659614)
TABLE ACCESS (FULL) OF LAST_ROUTE_POSITION (TABLE) (Cost=42 Card=25490 Bytes=356860)
NESTED LOOPS (Cost=125712 Card=178609 Bytes=77159088)
NESTED LOOPS (Cost=125498 Card=178609 Bytes=69836119)
HASH JOIN (RIGHT OUTER) (Cost=125307 Card=178609 Bytes=65013676)
INDEX (FULL SCAN) OF SALES_REP_INFO_IDX (INDEX (UNIQUE))
HASH JOIN (Cost=125304 Card=178609 Bytes=58047925)
INDEX (FAST FULL SCAN) OF ORDER_COMMENT_STORAGE_PK (INDEX (UNIQUE)) (Cost=781 Card=260600 Bytes=8860400)
VIEW (Cost=121361 Card=180106 Bytes=52410846)
UNION-ALL
INLIST ITERATOR
TABLE ACCESS (BY INDEX ROWID) OF BACKLOG_INTRADAY_SKU_REPORT_1 (TABLE) (Cost=5785 Card=1 Bytes=101)
INDEX (RANGE SCAN) OF BACKLOG_INTRA_SKU_INT_GEN_1 (INDEX) (Cost=5784 Card=1)
TABLE ACCESS (BY INDEX ROWID) OF BACKLOG_EXPAND_CSR_INTRA_RPT_1 (TABLE) (Cost=115575 Card=180105 Bytes=21432495)
INDEX (FULL SCAN) OF BACKLOG_INTRA_RPT_PRC_IDX_1 (INDEX) (Cost=2607 Card=198535)
INDEX (UNIQUE SCAN) OF CHANNELS_IDX (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=27)
INDEX (UNIQUE SCAN) OF SYS_IOT_TOP_189392 (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=41)
TABLE ACCESS (BY INDEX ROWID) OF COMMENT_ROOT_CAUSE (TABLE) (Cost=1 Card=1 Bytes=30)
INDEX (RANGE SCAN) OF RCID_IDX (INDEX) (Cost=0 Card=1)
INDEX (UNIQUE SCAN) OF CE_DASHBOARD_DISPLAY (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=22)
My Query is still slower,Is there a way out to speed up query???
Thanks,
Rekha
|
|
|
|
| Re: ORDER BY CLAUSE [message #161810 is a reply to message #161785] |
Tue, 07 March 2006 05:27 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Very hard to say. You are (outer) joining two views as it seems. I don't know the construction of these views, nor the distribution of the underlying data.
Looks like the only restriction is the "record_type in ('S', 'T')".
Standard advice: did you gather statistics?
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 09:18:50 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
