PLAN_TABLE_OUTPUT (without histograms) BEGIN DBMS_STATS. GATHER_TABLE_STATS (ownname => 'xxx', tabname => 'produkttitelinstanzen', estimate_percent => NULL, method_opt => 'for all columns size 1', cascade => TRUE); END; / SQL_ID 0x4sfjdj0wshv, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ pt1.id quellprodukt_id, pt2.id zielprodukt_id from t_sendung_import1 timp, produkttitelinstanzen pt1, produkttitelinstanzen pt2 where timp.id = pt1.produkt_id and pt1.produkt_id <> pt2.produkt_id and pt1.titelsuche_id = pt2.titelsuche_id and pt1.objektbereich_id = 0 and pt1.objektbereich_id = pt2.objektbereich_id and pt1.produkttitelart_id = 1 and pt1.produkttitelart_id = pt2.produkttitelart_id and pt1.reihenfolge = 0 and pt1.reihenfolge = pt2.reihenfolge and timp.jobid = 1111 Plan hash value: 2472102189 ------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 197 |00:00:00.01 | 125 | | 1 | NESTED LOOPS | | 1 | | 197 |00:00:00.01 | 125 | | 2 | NESTED LOOPS | | 1 | 1 | 198 |00:00:00.01 | 25 | | 3 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 7 | |* 4 | INDEX RANGE SCAN | T_SENDUNG_IMPORT1_PK | 1 | 1 | 1 |00:00:00.01 | 2 | | 5 | TABLE ACCESS BY INDEX ROWID| PRODUKTTITELINSTANZEN | 1 | 1 | 1 |00:00:00.01 | 5 | |* 6 | INDEX RANGE SCAN | PRTI_UK2_I | 1 | 1 | 1 |00:00:00.01 | 4 | |* 7 | INDEX RANGE SCAN | PRTI_TISU_FK_I | 1 | 12 | 198 |00:00:00.01 | 18 | |* 8 | TABLE ACCESS BY INDEX ROWID | PRODUKTTITELINSTANZEN | 198 | 1 | 197 |00:00:00.01 | 100 | ------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("TIMP"."JOBID"=1111) 6 - access("TIMP"."ID"="PT1"."PRODUKT_ID" AND "PT1"."PRODUKTTITELART_ID"=1 AND "PT1"."OBJEKTBEREICH_ID"=0 AND "PT1"."REIHENFOLGE"=0) 7 - access("PT1"."TITELSUCHE_ID"="PT2"."TITELSUCHE_ID") 8 - filter(("PT2"."REIHENFOLGE"=0 AND "PT2"."OBJEKTBEREICH_ID"=0 AND "PT2"."PRODUKTTITELART_ID"=1 AND "PT1"."PRODUKT_ID"<>"PT2"."PRODUKT_ID")) PLAN_TABLE_OUTPUT (with histograms) BEGIN DBMS_STATS. GATHER_TABLE_STATS (ownname => 'xxx', tabname => 'produkttitelinstanzen', estimate_percent => NULL, method_opt => 'for all columns size auto', cascade => TRUE); END; / SQL_ID 6k2stg2srtq2w, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ pt1.id quellprodukt_id, pt2.id zielprodukt_id from t_sendung_import1 timp, produkttitelinstanzen pt1, produkttitelinstanzen pt2 where timp.id = pt1.produkt_id and pt1.produkt_id <> pt2.produkt_id and pt1.titelsuche_id = pt2.titelsuche_id and pt1.objektbereich_id = 0 and pt1.objektbereich_id = pt2.objektbereich_id and pt1.produkttitelart_id = 1 and pt1.produkttitelart_id = pt2.produkttitelart_id and pt1.reihenfolge = 0 and pt1.reihenfolge = pt2.reihenfolge and timp.jobid = 1111 Plan hash value: 2472102189 ------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 197 |00:00:00.01 | 129 | | 1 | NESTED LOOPS | | 1 | | 197 |00:00:00.01 | 129 | | 2 | NESTED LOOPS | | 1 | 39799 | 198 |00:00:00.01 | 29 | | 3 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 11 | |* 4 | INDEX RANGE SCAN | T_SENDUNG_IMPORT1_PK | 1 | 1 | 2 |00:00:00.01 | 3 | | 5 | TABLE ACCESS BY INDEX ROWID| PRODUKTTITELINSTANZEN | 2 | 1 | 1 |00:00:00.01 | 8 | |* 6 | INDEX RANGE SCAN | PRTI_UK2_I | 2 | 1 | 1 |00:00:00.01 | 7 | |* 7 | INDEX RANGE SCAN | PRTI_TISU_FK_I | 1 | 11 | 198 |00:00:00.01 | 18 | |* 8 | TABLE ACCESS BY INDEX ROWID | PRODUKTTITELINSTANZEN | 198 | 57767 | 197 |00:00:00.01 | 100 | ------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("TIMP"."JOBID"=1111) 6 - access("TIMP"."ID"="PT1"."PRODUKT_ID" AND "PT1"."PRODUKTTITELART_ID"=1 AND "PT1"."OBJEKTBEREICH_ID"=0 AND "PT1"."REIHENFOLGE"=0) 7 - access("PT1"."TITELSUCHE_ID"="PT2"."TITELSUCHE_ID") 8 - filter(("PT2"."PRODUKTTITELART_ID"=1 AND "PT2"."REIHENFOLGE"=0 AND "PT2"."OBJEKTBEREICH_ID"=0 AND "PT1"."PRODUKT_ID"<>"PT2"."PRODUKT_ID")) 39 rows selected.