Re: SQL help

How did you get this output? I mean, what statistics level did you set in
the session?
what version of the db?
rgds
On 4/20/06, Nirmalya Das <nirmalya@xxxxxxx> wrote:
>
> The logical read for this query is way too high for the rows returned.
>
> Anything stands out..............
>
> Also why I am not getting the row count in the explain plan....
>
>
> -------------------------------------------------------------------------------
> SELECT wa_ho.work_assignment_pk,
>        wa_ho.channel_override_cd,
>        wa_ho.can_supplement_flg,
>        wa_ho.work_assignment_status AS status,
>        (CASE
>            WHEN wa_ho.work_assignment_status = 'NT'
>               THEN 'Manual'
>            WHEN wa_ho.work_assignment_status = 'NS'
>               THEN 'Staff Appraiser'
>            WHEN wa_ho.work_assignment_status = 'NE'
>               THEN 'Manual'
>         END
>        ) destination,
>        CAST (CAST(wa_ho.assignment_date AS TIMESTAMP WITH TIME ZONE) AT
> TIME
> ZONE 'EST' AS DAT
> E) AS assign_date,
>        CAST (CAST(wa_ho.create_datetime AS TIMESTAMP WITH TIME ZONE) AT
> TIME
> ZONE 'EST' AS DAT
> E) AS create_datetime,
>        c.claim_nbr,
>        c.policy_nbr,
>        dv.description AS object_desc,
>        (CASE
>            WHEN cp.last_name IS NULL
>               THEN cp.first_name
>            WHEN cp.first_name IS NULL
>               THEN cp.last_name
>            WHEN cp.last_name IS NOT NULL AND cp.last_name IS NOT NULL
>               THEN cp.last_name || ', ' || cp.first_name
>            ELSE ''
>         END
>        ) objowner
>   FROM cgw30.damaged_vehicle dv,
>        cgw30.claim_party cp,
>        cgw30.claim c,
>        cgw30.work_assignment wa_ho,
>        cgw30.work_assignment_entity wae
> WHERE cp.CLAIM_PARTY_ROLE IN ( 'AG','OY')
>    AND UPPER(cp.last_name) LIKE 'S%'
>    AND cp.claim_pk = c.claim_pk
>    AND c.claim_pk=dv.claim_pk
>    AND dv.damaged_object_pk=wa_ho.damaged_object_pk
>    AND wa_ho.work_assignment_type IN ('HO', 'EST')
>    AND wa_ho.assign_to_pk = wae.work_assignment_entity_pk
>    AND wae.ldap_identity IN ('404.CTD', '404.WAD')
>
> call     count       cpu    elapsed
> disk      query    current        rows
> ------- ------  -------- ---------- ---------- ----------
> ----------  ----------
> ------- ------  -------- ---------- ---------- ----------
> ----------  ----------
> Parse        1      0.03       0.02          0          0          0
> 0
> Execute      1      0.00       0.00          0          0          0
> 0
> Fetch      117      1.95       1.87          0
> 149104          0        1728
> ------- ------  -------- ---------- ---------- ----------
> ----------  ----------
> total      119      1.98       1.89          0
> 149104          0        1728
>
> Misses in library cache during parse: 1
> Optimizer goal: CHOOSE
> Parsing user id: 141  (NDAS)
>
> Rows     Execution Plan
> -------  ---------------------------------------------------
>       0  SELECT STATEMENT   GOAL: CHOOSE
>       0   TABLE ACCESS   GOAL: ANALYZED (BY GLOBAL INDEX ROWID) OF
>               'CLAIM_PARTY' PARTITION:ROW LOCATION
>       0    NESTED LOOPS
>       0     NESTED LOOPS
>       0      NESTED LOOPS
>       0       NESTED LOOPS
>       0        INLIST ITERATOR
>       0         INDEX   GOAL: ANALYZED (RANGE SCAN) OF
>                     'WORK_ASSIGNMENT_ENTITY_IE6' (NON-UNIQUE)
>       0        TABLE ACCESS   GOAL: ANALYZED (BY GLOBAL INDEX ROWID)
>                    OF 'WORK_ASSIGNMENT' PARTITION:ROW LOCATION
>       0         INDEX   GOAL: ANALYZED (RANGE SCAN) OF
>                     'WORK_ASSIGNMENT_IE1' (NON-UNIQUE)
>       0       INDEX   GOAL: ANALYZED (RANGE SCAN) OF
>                   'DAMAGE_VEHICLE_PK_I' (NON-UNIQUE)
>       0      INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'CLAIM_PK_I'
>                  (NON-UNIQUE)
>       0     INLIST ITERATOR
>       0      INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'CP_UK_I'
>                  (NON-UNIQUE)
>
> -------------------------------------------------------------------------------
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

Other related posts: