Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    6

    Unanswered: Performance tuning - views, autotrace...

    I have around 12 levels of views and am trying to optimize these..
    I have generated two different versions of sql statements (hoping ) to give me the same output.

    Can someone help me decipher the autotrace for the following and tell me which is better? And if you have any better solution?

    Thanks!!!

    CREATE OR REPLACE VIEW V_GTRULESON4 AS(
    SELECT
    A.GTRULESON4INDEX,
    A.NENAME,
    B.SCCPENTITYSETTYPE
    FROM
    GTRULESON4 A,ENHANCEDSCCPENTITYSETSON4 B
    WHERE A.ENHANCEDSCCPENTITYSETPOINTER = TO_CHAR(B.ENHANCEDSCCPENTITYSETID) AND A.NENAME = B.NENAME
    UNION
    SELECT
    A.GTRULESON4INDEX,
    A.NENAME,
    NULL SCCPENTITYSETTYPE
    FROM
    GTRULESON4 A
    WHERE A.ENHANCEDSCCPENTITYSETPOINTER = 'NULL'
    )


    SQL> select count(*) from v_gtruleson4 where nename = 'B_SON4';

    COUNT(*)
    ----------
    5581


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE
    1 0 SORT (AGGREGATE)
    2 1 VIEW OF 'V_GTRULESON4'
    3 2 SORT (UNIQUE)
    4 3 UNION-ALL
    5 4 NESTED LOOPS
    6 5 TABLE ACCESS (BY INDEX ROWID) OF 'GTRULESON4'
    7 6 INDEX (RANGE SCAN) OF 'GTRULESON4UN' (UNIQUE)
    8 5 TABLE ACCESS (BY INDEX ROWID) OF 'ENHANCEDSCCPEN
    TITYSETSON4'

    9 8 INDEX (RANGE SCAN) OF 'ENHANCEDSCCPENTITYSETSO
    N4UN' (UNIQUE)

    10 4 TABLE ACCESS (BY INDEX ROWID) OF 'GTRULESON4'
    11 10 INDEX (RANGE SCAN) OF 'GTRULESON4UN' (UNIQUE)




    Statistics
    ----------------------------------------------------------
    15 recursive calls
    2 db block gets
    7324 consistent gets
    698 physical reads
    0 redo size
    368 bytes sent via SQL*Net to client
    425 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    1 sorts (disk)
    1 rows processed

    ///////////////////////////////////////////////////////////////////////////////////////
    CREATE OR REPLACE VIEW V_GTRULESON4 AS(
    SELECT
    A.GTRULESON4INDEX,
    A.NENAME,
    B.SCCPENTITYSETTYPE
    FROM
    GTRULESON4 A,ENHANCEDSCCPENTITYSETSON4 B
    WHERE A.ENHANCEDSCCPENTITYSETPOINTER = TO_CHAR(B.ENHANCEDSCCPENTITYSETID(+))
    );


    SQL> select count(*) from v_gtruleson4 where nename = 'B_SON4';

    COUNT(*)
    ----------
    5581


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE
    1 0 SORT (AGGREGATE)
    2 1 MERGE JOIN (OUTER)
    3 2 SORT (JOIN)
    4 3 TABLE ACCESS (BY INDEX ROWID) OF 'GTRULESON4'
    5 4 INDEX (RANGE SCAN) OF 'GTRULESON4UN' (UNIQUE)
    6 2 SORT (JOIN)
    7 6 TABLE ACCESS (FULL) OF 'ENHANCEDSCCPENTITYSETSON4'




    Statistics
    ----------------------------------------------------------
    23 recursive calls
    12 db block gets
    1003 consistent gets
    158 physical reads
    0 redo size
    368 bytes sent via SQL*Net to client
    425 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    2 sorts (disk)
    1 rows processed

  2. #2
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    The two view scripts will not result in same output if
    A.ENHANCEDSCCPENTITYSETPOINTER has values other than 'NULL' and those in B.ENHANCEDSCCPENTITYSETID.

    Is 'NULL' the only value possible in this column other than those in B.ENHANCEDSCCPENTITYSETID?
    Oracle can do wonders !

  3. #3
    Join Date
    Nov 2003
    Posts
    6
    Yes, Null is the only other value if it does not exist in the second table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •