Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Eliminating cartesian merge

Re: Eliminating cartesian merge

From: Chuck <skilover_nospam_at_softhome.net>
Date: Wed, 28 Sep 2005 11:47:18 -0400
Message-ID: <1127918214.a86aa150f9acb770b1c7365331b2c2f4@bubbanews> 





Jonathan Lewis wrote:


> "Chuck" <skilover_nospam_at_softhome.net> wrote in message 

> news:1127916742.b86beac2d919d6bb3fbc8b1ab6476a5c_at_bubbanews...

> 


>>Jonathan Lewis wrote:
>>
>>>You could also try putting a no_merge hint into
>>>the query, perhaps using a global hints to reference
>>>objects inside the view.
>>>
>>>
>>
>>I don't think that'll help either. IIRC a view that contains nested
>>subqueries is non-mergeable anyway. But to be sure I did try a no_merge
>>hint to no avail.
>>




> 

> 

> You might post the execution path that you

> get with the RBO - it might give us a hint

> about why the execution plan is different.

> 

> There are cases where nested subqueries

> can be transformed in 9.2 - somewhere

> there's probably a list things that can work.

> 

> One idle thoght - not relevant to anything

> that's visible - is that transitive closure can

> convert

>     column1 = 'constant'

> and column2 = column1

> into

>     column1 ='constant'

> and column2 = 'constant'

> 

> at which point 9.2 will eliminate the

> join predicate - which can introduce

> a cartesian join.  If you can find this

> happening somewhere in the depths

> of your views, then this particular

> predicate  elimination is stopped when

> you query_rewrite_enabled = true

> (don't ask why - it just is).

> 

> This does have a side effect on

> cost and cardinality calculations,

> of course, so it's not a great fix

> to your problem, even if it does

> work.

> 

> 




Query_rewrite_enabled is already set to true. Here's the execution plan
with the RULE hint. You'll probably need to turn of line wrapping for it
to make any sense.




| Id  | Operation                         |  Name              | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                    |       |       |       |
|   1 |  SORT UNIQUE                      |                    |       |       |       |
|*  2 |   FILTER                          |                    |       |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID    | PS_NAMES           |       |       |       |
|   4 |     NESTED LOOPS                  |                    |       |       |       |
|   5 |      NESTED LOOPS                 |                    |       |       |       |
|   6 |       NESTED LOOPS                |                    |       |       |       |
|*  7 |        TABLE ACCESS BY INDEX ROWID| PS_SCRTY_TBL_DEPT  |       |       |       |
|*  8 |         INDEX RANGE SCAN          | PSBSCRTY_TBL_DEPT  |       |       |       |
|*  9 |        TABLE ACCESS BY INDEX ROWID| PSTREENODE         |       |       |       |
|* 10 |         INDEX RANGE SCAN          | PSFPSTREENODE      |       |       |       |
|* 11 |       TABLE ACCESS BY INDEX ROWID | PS_JOB             |       |       |       |
|* 12 |        INDEX RANGE SCAN           | PS0JOB             |       |       |       |
|* 13 |      INDEX RANGE SCAN             | PS_NAMES           |       |       |       |
|  14 |    SORT AGGREGATE                 |                    |       |       |       |
|* 15 |     INDEX RANGE SCAN              | PSBJOB             |       |       |       |
|  16 |    SORT AGGREGATE                 |                    |       |       |       |
|* 17 |     INDEX RANGE SCAN              | PSBJOB             |       |       |       |
|  18 |    SORT AGGREGATE                 |                    |       |       |       |
|* 19 |     FILTER                        |                    |       |       |       |
|* 20 |      INDEX RANGE SCAN             | PS_NAMES           |       |       |       |
|  21 |      SORT AGGREGATE               |                    |       |       |       |
|* 22 |       INDEX RANGE SCAN            | PS_NAMES           |       |       |       |
|* 23 |    TABLE ACCESS BY INDEX ROWID    | PS_SCRTY_TBL_DEPT  |       |       |       |
|* 24 |     INDEX RANGE SCAN              | PS_SCRTY_TBL_DEPT  |       |       |       |
----------------------------------------------------------------------------------------





Predicate Information (identified by operation id):





   2 - filter(("SYS_ALIAS_10"."EFFDT">=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-

              DD') OR "SYS_ALIAS_10"."EFFDT"= (SELECT MAX("JOB2"."EFFDT") FROM SYSADM."PS_JOB" "JOB2"
              WHERE "JOB2"."EMPL_RCD"=:B1 AND "JOB2"."EMPLID"=:B2 AND
              "JOB2"."EFFDT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')) AND
              "SYS_ALIAS_10"."EFFSEQ"= (SELECT MAX("JOB3"."EFFSEQ") FROM SYSADM."PS_JOB" "JOB3" WHERE
              "JOB3"."EFFDT"=:B3 AND "JOB3"."EMPL_RCD"=:B4 AND "JOB3"."EMPLID"=:B5)) AND
              "SYS_ALIAS_12"."EFFDT"= (SELECT MAX("SYS_ALIAS_2"."EFFDT") FROM SYSADM."PS_NAMES"
              "SYS_ALIAS_2" WHERE ("SYS_ALIAS_2"."EFFDT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYY
              Y-MM-DD') OR "SYS_ALIAS_2"."EFFDT">TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')
               AND TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')< (SELECT MIN("C"."EFFDT")
              FROM SYSADM."PS_NAMES" "C" WHERE "C"."NAME_TYPE"=:B6 AND "C"."EMPLID"=:B7)) AND
              "SYS_ALIAS_2"."NAME_TYPE"=:B8 AND "SYS_ALIAS_2"."EMPLID"=:B9) AND  NOT EXISTS (SELECT 0
              FROM SYSADM."PS_SCRTY_TBL_DEPT" "SEC2" WHERE "SEC2"."SETID"=:B10 AND
              "SEC2"."ROWSECCLASS"=:B11 AND "SEC2"."TREE_NODE_NUM"<=:B12 AND
              "SEC2"."TREE_NODE_NUM">=:B13 AND "SEC2"."TREE_NODE_NUM_END">=:B14 AND
              "SEC2"."TREE_NODE_NUM"<=:B15 AND "SEC2"."TREE_NODE_NUM"<>:B16))
   7 - filter("SYS_ALIAS_5"."ROWSECCLASS"=:Z)
   8 - access("SYS_ALIAS_5"."ACCESS_CD"='Y')
   9 - filter("SYS_ALIAS_4"."TREE_NODE_NUM"<="SYS_ALIAS_5"."TREE_NODE_NUM_END" AND
              "SYS_ALIAS_4"."TREE_NODE_NUM">="SYS_ALIAS_5"."TREE_NODE_NUM" AND
              "SYS_ALIAS_4"."SETID"="SYS_ALIAS_5"."SETID")
  10 - access("SYS_ALIAS_4"."TREE_NAME"='DEPT_SECURITY' AND
              "SYS_ALIAS_4"."EFFDT"="SYS_ALIAS_5"."TREE_EFFDT")
  11 - filter("SYS_ALIAS_4"."SETID"="SYS_ALIAS_10"."SETID_DEPT" AND
              "SYS_ALIAS_10"."APPT_TYPE"<>'1')
  12 - access("SYS_ALIAS_4"."TREE_NODE"="SYS_ALIAS_10"."DEPTID")
  13 - access("SYS_ALIAS_12"."EMPLID"="SYS_ALIAS_10"."EMPLID" AND
              "SYS_ALIAS_12"."NAME_TYPE"='PRI')
  15 - access("JOB2"."EMPLID"=:B1 AND "JOB2"."EMPL_RCD"=:B2 AND
              "JOB2"."EFFDT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD'))
       filter("JOB2"."EFFDT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD'))


  17 - access("JOB3"."EMPLID"=:B1 AND "JOB3"."EMPL_RCD"=:B2 AND "JOB3"."EFFDT"=:B3)

  19 - filter("SYS_ALIAS_2"."EFFDT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD
              ') OR "SYS_ALIAS_2"."EFFDT">TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD') AND
              TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')< (SELECT MIN("C"."EFFDT") FROM
              SYSADM."PS_NAMES" "C" WHERE "C"."NAME_TYPE"=:B1 AND "C"."EMPLID"=:B2))
  20 - access("SYS_ALIAS_2"."EMPLID"=:B1 AND "SYS_ALIAS_2"."NAME_TYPE"=:B2)
  22 - access("C"."EMPLID"=:B1 AND "C"."NAME_TYPE"=:B2)
  23 - filter("SEC2"."TREE_NODE_NUM"<=:B1 AND "SEC2"."TREE_NODE_NUM">=:B2 AND
              "SEC2"."TREE_NODE_NUM_END">=:B3 AND "SEC2"."TREE_NODE_NUM"<=:B4 AND
              "SEC2"."TREE_NODE_NUM"<>:B5)


  24 - access("SEC2"."ROWSECCLASS"=:B1 AND "SEC2"."SETID"=:B2)



Note: rule based optimization


-- 
To reply by email remove "_nospam"


Received on Wed Sep 28 2005 - 10:47:18 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US