Re: getting in a little over my head

     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_at_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_at_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]



--
http://www.freelists.org/webpage/oracle-l