Re: Query performance: DivideAndConquer vs. BruteForce
Date: 8 Jan 2002 16:25:03 -0800
Message-ID: <a1g2kv02r69_at_drn.newsguy.com>
In article <a1fqp2$hj4$1_at_news8.svr.pol.co.uk>, "David says...
>
>
>"Nuno Souto" <nsouto_at_optushome.com.au.nospam> wrote in message
>news:3c3ae18b.3166895_at_news-vip.optusnet.com.au...
>> Jim Kennedy doodled thusly:
>>
>> >I haven't had this problem under Oracle. (joins greater than 3 tables)
>>
>> me neither. this morning did an 11 table join without the slightest
>> performance problem...
>>
>>
>
> With complex subqueries, where exists and where not in's
> with outer joins?
sure, it is not a function of the number of tables -- its a function of how much data is going to have to be sifted through. Those constructs could cause you to sift through tons of data (and if you attempted to do it procedurally, on your own, in the client -- you would lose).
I'm just putting this up to show off, but this is a view that is executed internally at Oracle many thousands of times a day. Its part of a palm sync I worked on. Some of the tables referenced in here have 16 million + rows. There are exists, unions, subqueries out the whazoo, calls to custom PLSQL functions for timezones and other things, queries on queries on queries and probably more then 16 tables (never counted them).
It runs in about a second at most, typically subsecond (a function of the number of events a person has ever scheduled in their tenure). I had the choice of either filtering the data on the client (a palm sync conduit) or letting the database do my work for me. I always choose the database - and trust me, its way faster (not saying the first query you write will be faster, you might have to tune it but it'll be faster)
create or replace force view wwv_date_book_sync_schedulesV2 as
select user USERNAME,
rownum+200000 SEQNO,
nvl(event_reminder,0) ALARM_ADVANCE_TIME,
0 ALARM_ADVANCE_UNIT,
event_title DESCRIPTION,
date_time_begin_local+length_minutes/1440 END_DATE,
decode( nvl(event_reminder,0), 0, 'N', 'Y' ) ISALARMED,
isrepeating ISREPEATING,
decode( nvl(length_minutes,0), 0, 'Y', 'N' ) ISUNTIMED,
event_notes NOTE,
decode( event_recur_type, 'NO_REPEAT', 0, 'DAILY', 1,
'WEEKDAYS', 2, 'WEEKLY', 2, 'BIWEEKLY', 2, 'MONTHLY_DATE', 4,
'MONTHLY_DAY', 3, 'YEARLY', 5, null ) REPEAT_TYPE,
event_recur_end_date_local REPEAT_END_DATE,
decode( event_recur_type, 'NO_REPEAT', 0,
nvl(decode(event_recur_type,'BIWEEKLY',2,event_recur_frequency),1))
REPEAT_FREQUENCY,
decode(event_recur_type,'NO_REPEAT', 0,
date_time_begin_local- next_day(date_time_begin_local-7, 'SUN'))
REPEAT_ON_DAY,
decode( event_recur_type, 'NO_REPEAT', 0, event_recur_month_week-1)
REPEAT_ON_WEEK,
0 REPEAT_START_WEEK,
date_time_begin_local START_DATE,
nvl( palm_id, 0 ) PALM_ID,
'N' IS_ARCHIVED,
decode( deleted_as_of, to_date('01010001','ddmmyyyy'), 'N', 'Y' )
IS_DELETED,
decode( palm_id, NULL, 'Y', 'N' ) IS_NEW,
decode( palm_id, NULL, 'N', 'Y' ) IS_MODIFIED,
event_private IS_PRIVATE,
id DB_ID,
'N' FROM_PALM,
created_on CREATED_ON,
deleted_as_of DELETED_AS_OF,
api_updated_on API_UPDATED_ON,
table_from ,
event_owner
from
( select decode( length_minutes, 0, trunc(a.date_time_begin),
decode( a.date_time_begin, null, to_date(null),
get_adjusted_time( a.date_time_begin,
to_number(substr( userenv('client_info'), 1,
instr(userenv('client_info'),',')-1)) ) ) )
AS date_time_begin_local,
decode( length_minutes, 0, trunc( a.event_recur_end_date ),
decode( a.event_recur_end_date, null, to_date(null),
get_adjusted_time( a.event_recur_end_date,
to_number(substr( userenv('client_info'), 1,
instr(userenv('client_info'),',')-1)) ) ) )
AS event_recur_end_date_local, id, event_reminder, event_title, nvl(length_minutes,0) length_minutes,
event_recur_type, event_recur_frequency, event_recur_month_week, event_private, isrepeating, event_notes,palm_id,
created_on, deleted_as_of, api_updated_on, table_from, event_owner
from ( select a.id, event_reminder, event_title,
nvl(length_minutes,0) length_minutes,
event_recur_type, event_recur_frequency,
event_recur_month_week, event_private, event_recur_end_date,
date_time_begin , 'Y' isrepeating,
wwv_date_book_sync_utils_pkg.get_rules_notes( a.rowid )
event_notes, palm_id,
created_on, deleted_as_of, api_updated_on, 'RULES'
table_from, event_owner
from wwv_cal_schedules_rules$ a,
wwv_date_book_sync_id_mapping b,
( select distinct event_recur_rule_id
from wwv_cal_schedules$
where event_owner in ( select * from
wwv_date_book_sync_eventowners )
and event_recur_rule_id <> 0
and deleted_as_of = to_date( '01010001', 'ddmmyyyy' )
) c
where a.id = c.event_recur_rule_id
and a.deleted_as_of = to_date( '01010001', 'ddmmyyyy' )
and b.username(+) = USER
and b.id(+) = a.id
and b.isrule(+) = 'Y'
and ( (b.id is NULL )
or (b.id is not NULL and greatest(
a.deleted_as_of,nvl(a.api_updated_on,to_date('01010001','ddmmyyyy')) ) > to_date(substr(userenv('client_info'),instr(userenv('client_info'),',')+1),'dd/mm/yyyy hh24:mi:ss'))
)
union all
select a.id, event_reminder, event_title,
nvl(length_minutes,0) length_minutes,
'NO_REPEAT' event_recur_type, 1 event_recur_frequency,
to_number(null) event_recur_month_week, event_private,
to_date(null) event_recur_end_date,
date_time_begin , 'N' isrepeating,
wwv_date_book_sync_utils_pkg.get_schedules_notes( a.rowid )
event_notes, palm_id,
created_on, deleted_as_of, api_updated_on,
'SCHEDULES' table_from, event_owner
from wwv_cal_schedules$ a,
wwv_date_book_sync_id_mapping b
where a.event_owner in ( select * from
wwv_date_book_sync_eventowners )
and a.deleted_as_of = to_date( '01010001', 'ddmmyyyy' )
and ( event_recur_rule_id = 0
or
(event_recur_rule_id <> 0 and change_exception = 'Y')
)
and b.username(+) = USER
and b.id(+) = a.id
and b.isrule(+) = 'N'
and ( (b.id is NULL )
or (b.id is not NULL and greatest(
a.deleted_as_of,nvl(a.api_updated_on,to_date('01010001','ddmmyyyy')) ) > to_date(substr(userenv('client_info'),instr(userenv('client_info'),',')+1),'dd/mm/yyyy hh24:mi:ss'))
)
union all
select a.id, null event_reminder, null event_title, 0
length_minutes,
decode( isrule, 'Y', 'WEEKLY', 'NO_REPEAT' )
event_recur_type, 1 event_recur_frequency,
to_number(null) event_recur_month_week, 'N' event_private,
to_date(null) event_recur_end_date,
to_date(null) date_time_begin , isrule isrepeating,
null event_notes, palm_id,
to_date(null) created_on, sysdate deleted_as_of,
to_date(null) api_updated_on, 'ID_MAPPING' table_from,
'unknown'
from wwv_date_book_sync_id_mapping a
where username = USER
and ( ( isRule = 'Y'
and
not exists ( select null
from wwv_cal_schedules_rules$
where id = a.id
and event_owner in
( select * from
wwv_date_book_sync_eventowners )
and deleted_as_of = to_date( '01010001', 'ddmmyyyy' )
)
)
or ( isRule = 'N'
and
not exists ( select null
from wwv_cal_schedules$
where id = a.id
and event_owner in ( select * from wwv_date_book_sync_eventowners )
and ( deleted_as_of = to_date( '01010001', 'ddmmyyyy' )
or
( event_recur_rule_id <> 0 and change_exception = 'N' )
)
)
)
or ( isRule = 'N'
and
exists ( select null
from wwv_cal_schedules$
where id = a.id
and event_owner in ( select * from wwv_date_book_sync_eventowners )
and event_recur_rule_id <> 0 and change_exception = 'N'
)
)
)
) a
)
>
>> Cheers
>> Nuno Souto
>> nsouto_at_optushome.com.au.nospam
>
>
-- Thomas Kyte (tkyte_at_us.oracle.com) http://asktom.oracle.com/ Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle CorpReceived on Wed Jan 09 2002 - 01:25:03 CET