Now Tom - that's just plain nasty!
Thomas Kyte wrote:
> 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@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 Corp
Received on Tue Jan 08 2002 - 20:00:44 CST
