pedicate - left or right side
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: pedicate - left or right side

  1. #1
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187

    pedicate - left or right side

    Is this ultra simple stuff or am I missing the banana boat yet again..

    why is


    PHP Code:

    SELECT company
    .DESCR
           
    company.DESCRSHORT 
      FROM PS_company_TBL COMPANY 
     WHERE 
    :company.company 
       
    AND company.EFFDT = (SELECT MAX(C2.EFFDT
                              
    FROM PS_COMPANY_TBL C2 
                             WHERE 
    :C2.COMPANY 
                               
    AND C2.EFFDT <= SYSDATE
    different than this

    PHP Code:


    SELECT company
    .DESCR
           
    company.DESCRSHORT 
      FROM PS_company_TBL COMPANY 
     WHERE company
    .company = :
       
    AND company.EFFDT = (SELECT MAX(C2.EFFDT
                              
    FROM PS_COMPANY_TBL C2 
                             WHERE C2
    .COMPANY COMPANY.COMPANY 
                               
    AND C2.EFFDT <= SYSDATE
    specifically why is the top one cost 2 and the bottom one cost of 12 when essentially all I have done is put the bind variable on the right in the bottom statement
    I'm stmontgo and I approve of this message

  2. #2
    Join Date
    Oct 2002
    Posts
    807
    You seeing this through an autotrace? Are the plans identical?

  3. #3
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    Originally posted by Axr2
    You seeing this through an autotrace? Are the plans identical?

    PHP Code:
    SQLexplain plan for
      
    2  SELECT company.DESCR,
      
    3         company.DESCRSHORT
      4    FROM PS_company_TBL COMPANY
      5  WHERE 
    :company.company
      6     
    AND company.EFFDT = (SELECT MAX(C2.EFFDT)
      
    7                            FROM PS_COMPANY_TBL C2
      8                           WHERE 
    :C2.COMPANY
      9                             
    AND C2.EFFDT <= SYSDATE);

    Explained.

    SQL> @plan

    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------------------------------

    ----------------------------------------------------------------------------------
    Id  Operation                      |  Name           Rows  Bytes Cost  |
    ----------------------------------------------------------------------------------
    |   
    SELECT STATEMENT               |                 |     |    44 |     |
    |   
    |  TABLE ACCESS BY INDEX ROWID   PS_COMPANY_TBL  |     |    44 |     |
    |*  
    |   INDEX UNIQUE SCAN            PS_COMPANY_TBL  |     |       |     |
    |   
    |    SORT AGGREGATE              |                 |     |    12 |       |
    |   
    |     FIRST ROW                  |                 |     |    24 |     |
    |*  
    |      INDEX RANGE SCAN (MIN/MAX)| PS_COMPANY_TBL  |   273 |       |     |
    ----------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

       
    access("COMPANY"."COMPANY"=:AND "COMPANY"."EFFDT"= (SELECT /*+ */
                  
    MAX("C2"."EFFDT"FROM "PS_COMPANY_TBL" "C2" WHERE "C2"."COMPANY"=:AND
                  
    "C2"."EFFDT"<=SYSDATE@!))
       
    access("C2"."COMPANY"=:AND "C2"."EFFDT"<=SYSDATE@!)
           
    filter("C2"."EFFDT"<=SYSDATE@!)

    Notecpu costing is off

    22 rows selected
    .

    SQLexplain plan for
      
    2  SELECT company.DESCR,
      
    3         company.DESCRSHORT
      4    FROM PS_company_TBL COMPANY
      5  WHERE company
    .company = :1
      6     
    AND company.EFFDT = (SELECT MAX(C2.EFFDT)
      
    7                            FROM PS_COMPANY_TBL C2
      8                           WHERE C2
    .COMPANY COMPANY.COMPANY
      9                             
    AND C2.EFFDT <= SYSDATE);

    Explained.

    SQL> @plan

    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------------------------------

    ----------------------------------------------------------------------------------
    Id  Operation                      |  Name           Rows  Bytes Cost  |
    ----------------------------------------------------------------------------------
    |   
    SELECT STATEMENT               |                 |     |    63 |    12 |
    |*  
    |  FILTER                        |                 |       |       |       |
    |   
    |   SORT GROUP BY                |                 |     |    63 |    12 |
    |   
    |    MERGE JOIN CARTESIAN        |                 |     |   189 |     |
    |   
    |     TABLE ACCESS BY INDEX ROWIDPS_COMPANY_TBL  |     |   102 |     |
    |*  
    |      INDEX RANGE SCAN          PSACOMPANY_TBL  |     |       |     |
    |   
    |     BUFFER SORT                |                 |     |    24 |     |
    |*  
    |      INDEX RANGE SCAN          PS_COMPANY_TBL  |     |    24 |     |
    ----------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

       
    filter("COMPANY"."EFFDT"=MAX("C2"."EFFDT"))
       
    access("COMPANY"."COMPANY"=:Z)
       
    access("C2"."COMPANY"=:AND "C2"."EFFDT"<=SYSDATE@!)
           
    filter("C2"."EFFDT"<=SYSDATE@!)

    Notecpu costing is off

    23 rows selected
    .

    SQL
    I'm stmontgo and I approve of this message

Posting Permissions

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



Click Here to Expand Forum to Full Width