Questions about performance-Join/Where/(+)??

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc

Poston Thu, 09 Aug 2007 5:43 pm

guys, I started working with Oracle for almost 1 month, and told me here at work who do not have the need to use the command JOIN because \"increases the complexity of code view\" and that the hardware to run the Bank realize peaceful etc etc, that the performance gain is insignificant to be use JOIN instead of doing comparisons directly in the WHERE clauseI worked almost 3 years with Firebird and BD, the performance difference between JOIN (LEFT JOIN, etc) and comparisons on WHERE he was glaring!!, in Oracle is there any optimizer that resolves this issue for what it really is not necessary to use the JOIN?, how is it in Oracle because I honestly got used to use JOIN same way ... until the Select is even more ' professional '.

Anyway, what is the opinion of you?

Location: São Paulo/SP

Poston Thu, 09 Aug 2007 5:52 pm

Well. \"LEFT JOIN\", \"RIGHT JOIN\" in oracle works, but the default is to use the equal sign for joins and (+) for OUTER-JOINS. As far as I know, it's the same thing using the equal sign or JOIN names, etc. At the bottom, will be done the same thing by the optimizer.

Anyway, if anyone has a demonstration that would be interesting!
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?

Poston Thu, 16 Aug 2007 9:48 am

the best test for you would be to use an execution plan for select this having a higher cost and generate an output like this: > set autotrace traceonly SQL > SET AUTOTRACE TRACEONLY SQL > select * from units where description like '% M% ';

Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer = ALL_ROWS (Cost = 3 Card = 1 Bytes = 24) 1 0 TABLE ACCESS (FULL) OF ' UNITS ' (TABLE) (Cost = 3 Card = 1 B ytes = 24) Statistics----------------------------------------------------------1 recursive calls 0 db block gets 0 physical reads 8 consistent gets 0 redo size 876 bytes sent via SQL * Net to client 504 bytes received via SQL * Net from client 2 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3 rows processed do this and I see the access path to the data ...

I hope I helped ...

Location: sao caetano do sul - SP

Keen On Oracle - DBA

  • See also
    Last Post

      Return to SQL

      Who is online

      Users browsing this forum: No registered users and 6 guests