pedicate - left or right side

[stmontgo]

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

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>