# Thread: pedicate - left or right side

1. ## 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 :1 = company.company     AND company.EFFDT = (SELECT MAX(C2.EFFDT)                            FROM PS_COMPANY_TBL C2                           WHERE :1 = C2.COMPANY                             AND C2.EFFDT <= SYSDATE)  ```
different than this

PHP Code:
``` SELECT company.DESCR,         company.DESCRSHORT    FROM PS_company_TBL COMPANY   WHERE company.company = :1     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

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

3. Originally posted by Axr2
You seeing this through an autotrace? Are the plans identical?

PHP Code:
``` SQL> explain plan for   2  SELECT company.DESCR,   3         company.DESCRSHORT   4    FROM PS_company_TBL COMPANY   5  WHERE :1 = company.company   6     AND company.EFFDT = (SELECT MAX(C2.EFFDT)   7                            FROM PS_COMPANY_TBL C2   8                           WHERE :1 = C2.COMPANY   9                             AND C2.EFFDT <= SYSDATE); Explained. SQL> @plan PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- | Id  | Operation                      |  Name           | Rows  | Bytes | Cost  | ---------------------------------------------------------------------------------- |   0 | SELECT STATEMENT               |                 |     1 |    44 |     2 | |   1 |  TABLE ACCESS BY INDEX ROWID   | PS_COMPANY_TBL  |     1 |    44 |     2 | |*  2 |   INDEX UNIQUE SCAN            | PS_COMPANY_TBL  |     1 |       |     1 | |   3 |    SORT AGGREGATE              |                 |     1 |    12 |       | |   4 |     FIRST ROW                  |                 |     2 |    24 |     2 | |*  5 |      INDEX RANGE SCAN (MIN/MAX)| PS_COMPANY_TBL  |   273 |       |     2 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("COMPANY"."COMPANY"=:Z AND "COMPANY"."EFFDT"= (SELECT /*+ */               MAX("C2"."EFFDT") FROM "PS_COMPANY_TBL" "C2" WHERE "C2"."COMPANY"=:Z AND               "C2"."EFFDT"<=SYSDATE@!))    5 - access("C2"."COMPANY"=:Z AND "C2"."EFFDT"<=SYSDATE@!)        filter("C2"."EFFDT"<=SYSDATE@!) Note: cpu costing is off 22 rows selected. SQL> explain 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  | ---------------------------------------------------------------------------------- |   0 | SELECT STATEMENT               |                 |     1 |    63 |    12 | |*  1 |  FILTER                        |                 |       |       |       | |   2 |   SORT GROUP BY                |                 |     1 |    63 |    12 | |   3 |    MERGE JOIN CARTESIAN        |                 |     3 |   189 |     5 | |   4 |     TABLE ACCESS BY INDEX ROWID| PS_COMPANY_TBL  |     2 |   102 |     3 | |*  5 |      INDEX RANGE SCAN          | PSACOMPANY_TBL  |     2 |       |     2 | |   6 |     BUFFER SORT                |                 |     2 |    24 |     9 | |*  7 |      INDEX RANGE SCAN          | PS_COMPANY_TBL  |     2 |    24 |     1 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("COMPANY"."EFFDT"=MAX("C2"."EFFDT"))    5 - access("COMPANY"."COMPANY"=:Z)    7 - access("C2"."COMPANY"=:Z AND "C2"."EFFDT"<=SYSDATE@!)        filter("C2"."EFFDT"<=SYSDATE@!) Note: cpu costing is off 23 rows selected. SQL>  ```

#### Posting Permissions

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