Order By Performance

Tuning de Banco, Tuning de SQL, Ferramentas de tuning
  

Poston Tue, 17 Sep 2013 11:44 am

Good morning, I have a view that extracts data from XML files stored in a field in a table. The view is relatively quickly by the volume of processed information. However, if you added a clause order by " " in some XML field in SQL, search take so long as to be infeasible.

The case statement example:
Code: Select all
CREATE OR REPLACE VIEW VW_DEMO AS
SELECT TABELA.CODIGO
       XTAB.CAMPO
  FROM TABELA,
       XMLTABLE ('//root/campo' PASSING TABELA.XML COLUMNS
                 CAMPO VARCHAR2 (50) PATH '/campo/text()') XTAB:

SELECT * FROM VW_DEMO;  --executa em menos de 1 segundo
SELECT * FROM VW_DEMO ORDER BY CODIGO; --executa em menos de 1 segundo
SELECT * FROM VW_DEMO ORDER BY CAMPO; --leva vários minutos
how to improve this situation?
Marciel
Location: Vitória - ES

Sabe qual o cúmulo da ignorância, da apatia e da ira?
Não sei, não quero saber e tenho raiva de quem sabe.

Poston Tue, 17 Sep 2013 11:36 pm

Marciel, ordering consumes a lot of resources BD and the best recommendation to optimize the SQL is to avoid it. Never googled for understand why take longer in XML, I know that this is normal. When I was a developer, I never did sorting in my SQL statements, views, etc. I was returning the data to the application in the manner in which they came from the BD and was ordering directly in the application. Sort the data in the application is usually faster than ordering in BD.

[] s
fbifabio
Location: São Paulo - SP

Fábio Prado
www.fabioprado.net

Poston Wed, 18 Sep 2013 10:32 am

Guys, is a doubt my, specific to this case: what if, instead of the view, you create a temp, populate it with the value that view (create a procedure popula_temp, for example) without ordering and then do a select into temp, ordained?

Hug, cheezburger network
Trevisolli
Location: Araraquara - SP

Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP



Return to DBA Tuning

Who is online

Users browsing this forum: No registered users and 1 guest