Re: getting in a little over my head
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: JSweetser@xxxxxxxx
- Date: Fri, 09 Sep 2011 09:26:54 +0200
Joe,
I have never been a great fan of execution plans but I have always
hated multiple subqueries that kind of "sequentialize" everything. For
me, everything depends in your query about the selectivity of accountid
in tables quote and pol_policy, and my gut instinct would make me
rewrite your query like this:
SELECT rownum AS pk,
128847 AS accountId,
NULL AS logid,
0 AS deleted,
acv.CREATED_TIME AS CREATEDATE,
acv.CREATED_USERNAME AS USERNAME,
'GENERAL' AS CATEGORY,
acv.MESSAGE AS note
FROM (select objectid objid, 'Quote' linktype
FROM quote
WHERE accountid = 128847
union all
select objectid objid, 'Policy' linktype
FROM pol_policy
WHERE accountid = 128847
AND logid = 1) x
inner join accountnotemview acv
on acv.objid = x.objid
and acv.linktype = x.linktype
/
at which point it becomes obvious that an index (on acv) on (linktype,
objid) or (objid, linktype), depending on already existing indexes (no
need to make a concatenated index start with a column that is already
separately indexed) would help, unless of course objid is already unique.
Otherwise, are you aware that calling "pk" a rownum (which may be
different for the same line the next time the query runs, especially if
the table is modified) is relational heresy? Some people died in a
bonfire for lesser crimes.
--
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
On 09/08/2011 11:53 PM, Sweetser, Joe wrote:
> SQL gurus,
>
> I have a developer who came to me and asked me to make a certain sql
> statement 'as fast as possible'. Said statement was running against a view
> that was sort of nasty with sub-queries and a union thrown in. Cutting to
> the chase, I created a MV that looks like this:
>
> SSS3@idm1> desc accountnotemview
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> CREATED_USERNAME VARCHAR2(100)
> CREATED_TIME TIMESTAMP(6)
> OBJID VARCHAR2(50)
> LINKTYPE VARCHAR2(100)
> TASKORDER NUMBER
> STATUS CHAR(7)
> MESSAGE VARCHAR2(4000)
>
> Not a lot of data....36,474 rows.
> SSS3@idm1> select count(*) from accountnotemview;
>
> COUNT(*)
> ----------
> 36474
>
> SQL statement is (ultimately, the accountID will be a bind variable):
> SELECT rownum AS pk,
> 128847 AS accountId,
> NULL AS logid,
> 0 AS deleted,
> CREATED_TIME AS CREATEDATE,
> CREATED_USERNAME AS USERNAME,
> 'GENERAL' AS CATEGORY,
> MESSAGE AS note
> FROM accountnotemview acv
> WHERE (linktype = 'Quote'
> AND objid IN
> (SELECT objectid FROM quote WHERE accountid = 128847
> ))
> OR (linktype = 'Policy'
> AND objid IN
> (SELECT objectid FROM pol_policy WHERE accountid = 128847 AND logid = 1
> ))
> /
[snip]
--
//www.freelists.org/webpage/oracle-l
Other related posts:
