On a divergent topic:
See notes below:
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )
____USA__________November 7/9 (Detroit)
____USA__________November 19/21 (Dallas)
____England______November 12/14
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Billy Verreynne wrote in message ...
Jan Gelbrich wrote:
|>
|> SELECT /*+ FIRST_ROWS */
|> Per.Name,
|> P.Pers_ID,
|> P.special_NR
|> FROM
|> Bigtable1 P, --1 million rows
|> Bigtable2 Per --1 million rows
|> WHERE
|> To_Number(Substr(To_Char(P.Special_Nr),2,4)) in
|> (
|> select distinct s.vendor_id
|> from Bigtable3 s --800,000 rows
|> where id in
|> (
|> select az_id
|> from Bigtable4 b --1 million rows
|> where B.Fact_date Between :P_Date_From And :P_Date_to
|> and s.id = b.az_id
|> )
|> )
|> And P.Pers_ID = Per.ID
|> And P.Company = :P_Company
|> And P.Dept_ID = :P_Dept_ID
|
|
|
|Last suggestion. Use EXPLAIN PLAN. Use TKPROF. Look at the optimiser manual
|and play around with hints - Oracle's CBO does not always get it right. But
|that does not that you should too.. :-) IMO these type of nested sub
|SELECTS are usually an indication of a lack of understanding on how to
|effectively use Oracle and retrieve data via SQL.
|
A question - when do you make the trade-off between writing
SQL which is human readable, and therefore subject to easy
comprehension and quick modification by the next person,
and writing SQL which is designed to do the job the optimizer
ought to do.
In the fragment about, the primary complaint I would have is
that a meaningful item of information has been embedded
in a column rather than being in a column of its own. The
sub-selects MAY not be folded properly by the optimizer, but
a (literally) 10 second glance down the code tells me it is
trying to:
Acquire information from big_table1 and big_table2
based on a flag identifying the vendors who have
been involved in a certain activity between a pair of
dates.
I like the code because it seems to say in SQL exactly
what you mean in English.
(Of course I'd than pass it through explain plan to see
if Oracle converted the subqueries to joins correctly -
but then that's also a question of knowing what the data
volumes and distributions are supposed to be).
Received on Wed Oct 16 2002 - 02:58:54 CDT
