-
1. Re: Order by query in TEIID
shawkins Oct 16, 2013 4:21 PM (in response to teiid123)What version are you on? Can you provide a query plan?
Steve
-
2. Re: Order by query in TEIID
teiid123 Oct 17, 2013 4:40 PM (in response to shawkins)Hi Steve,
Here is my detail question with example.
Teiid version : 8.2.0 and Oracle Database
vdb Name : TEST.VDB
Base Table Name : FUND_REFERENCEQuery Under Base Table in tediid designer editor : SELECT F.FUND_CODE, F.FUND_NAME FROM FUND AS F ORDER BY F.FUND_NAME
Deployed this VDB in Jboss server and tried to test from squirrel.
Select * from FUND_REFERENCEQuery plan which is from teiid log file
-----------------------------------------SELECT DISTINCT g_0.FUND_CODE AS c_0, g_0.FUND_NAME AS c_1 FROM FUND AS g_0
I don't find ORDER BY FUND_NAME in my query plan. Why teiid ignores ORDER BY in my case? pleaseThanks
Lawrence
-
3. Re: Order by query in TEIID
shawkins Oct 17, 2013 6:28 PM (in response to teiid123)Teiid, just like dbs like SQL Server, don't consider tables/views to have an ordering. This allows for greater flexibility in optimizing. So if you put just an order by in view the optimizer will simply remove it. You can use a limit to force the ordering (such as limit 2147483647) or do your select as part of a procedure, or ideally add the desired ordering to your user query.
Steve
-
4. Re: Order by query in TEIID
teiid123 Oct 18, 2013 3:33 PM (in response to shawkins)Hi Steve,
It worked for me and thank you very much for your help and support. I Used Limit feature.
Thanks
Lawrence
-
5. Re: Order by query in TEIID
shawkins Oct 18, 2013 3:38 PM (in response to teiid123)Just keep in mind that using a limited order by in a view will force that possibly expensive sort operation to take place even if you use your view in joins, unions, etc.
-
6. Re: Order by query in TEIID
teiid123 Oct 18, 2013 4:20 PM (in response to shawkins)
Hi Steve,Is it good if i use throug procedure as below
CREATE VIRTUAL PROCEDURE
BEGIN
SELECT DISTINCT FUND_CODE, FUND_NAME FROM FUND order by FUND_NAME;
ENDThanks
Lawrence
-
7. Re: Order by query in TEIID
shawkins Oct 18, 2013 4:25 PM (in response to teiid123)Yes, that is fine as long as your client doesn't mind calling through a stored procedure.