Path: news.easynews.com!core-easynews!newsfeed1.easynews.com!easynews.com!easynews!cyclone1.gnilink.net!gnilink.net!news.glorb.com!postnews2.google.com!not-for-mail From: spendius@muchomail.com (Spendius) Newsgroups: comp.databases.oracle.server Subject: Re: "select *" MUCH quicker than "select from...": explanation ? Date: 7 Jul 2004 06:17:59 -0700 Organization: http://groups.google.com Lines: 169 Message-ID: References: NNTP-Posting-Host: 195.177.247.226 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1089206283 27213 127.0.0.1 (7 Jul 2004 13:18:03 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Wed, 7 Jul 2004 13:18:03 +0000 (UTC) Xref: core-easynews comp.databases.oracle.server:225145 X-Received-Date: Wed, 07 Jul 2004 06:17:12 MST (news.easynews.com) Sybrand Bakker wrote: > - compare the execution paths and/or ran EXPLAIN PLAN? > - traced the session ? Below: o plan when selecting only one field (which is null actually o " " " all fields o text of the statement 1/ Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=106) 1 0 NESTED LOOPS (Cost=8 Card=1 Bytes=106) 2 1 NESTED LOOPS (Cost=8 Card=1 Bytes=102) 3 2 NESTED LOOPS (Cost=8 Card=1 Bytes=98) 4 3 NESTED LOOPS (Cost=8 Card=1 Bytes=94) 5 4 NESTED LOOPS (Cost=8 Card=1 Bytes=90) 6 5 NESTED LOOPS (Cost=8 Card=1 Bytes=86) 7 6 HASH JOIN (Cost=8 Card=1 Bytes=82) 8 7 NESTED LOOPS (Cost=6 Card=1 Bytes=57) 9 8 NESTED LOOPS (Cost=3 Card=1 Bytes=21) 10 9 NESTED LOOPS (OUTER) (Cost=3 Card=1 Bytes=19) 11 10 NESTED LOOPS (Cost=3 Card=1 Bytes=17) 12 11 NESTED LOOPS (Cost=2 Card=1 Bytes=12) 13 12 TABLE ACCESS (BY INDEX ROWID) OF 'VEHICLE_MASTER' (Cost=2 Card=1 Bytes=9) 14 13 INDEX (UNIQUE SCAN) OF 'VHCMAS_PK' (UNIQUE) (Cost=1 Card=1) 15 12 INDEX (UNIQUE SCAN) OF 'VHC_PK' (UNIQUE) 16 11 TABLE ACCESS (BY INDEX ROWID) OF 'ACTIVITY_CENTRE' (Cost=1 Card=29 Bytes=145) 17 16 INDEX (UNIQUE SCAN) OF 'ACTCE_PK' (UNIQUE) 18 10 INDEX (UNIQUE SCAN) OF 'TUP_PK' (UNIQUE) 19 9 INDEX (UNIQUE SCAN) OF 'SERIES_PK' (UNIQUE) 20 8 TABLE ACCESS (BY INDEX ROWID) OF 'VEHICLE_STATE' (Cost=6 Card=1 Bytes=36) 21 20 AND-EQUAL 22 21 INDEX (RANGE SCAN) OF 'VHCST_VALID_TO_NI' (NON-UNIQUE) (Cost=1 Card=1) 23 21 INDEX (RANGE SCAN) OF 'VHCST_VHC_FK_I' (NON-UNIQUE) (Cost=1 Card=1) 24 7 FILTER 25 24 REMOTE* OWNER2.WORLD 26 6 INDEX (UNIQUE SCAN) OF 'MD_PK' (UNIQUE) 27 5 INDEX (UNIQUE SCAN) OF 'MD_PK' (UNIQUE) 28 4 INDEX (UNIQUE SCAN) OF 'MD_PK' (UNIQUE) 29 3 INDEX (UNIQUE SCAN) OF 'MD_PK' (UNIQUE) 30 2 INDEX (UNIQUE SCAN) OF 'MD_PK' (UNIQUE) 31 1 INDEX (UNIQUE SCAN) OF 'MD_PK' (UNIQUE) 25 SERIAL_FROM_REMOTE SELECT "ID","VALID_FROM_DATE","VALID_TO_DATE " FROM "OWNER2"."A_PTCAR" "PTC" 2/ Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=18 Card=1 Bytes=267) 1 0 NESTED LOOPS (Cost=18 Card=1 Bytes=267) 2 1 NESTED LOOPS (Cost=17 Card=1 Bytes=247) 3 2 NESTED LOOPS (Cost=16 Card=1 Bytes=227) 4 3 NESTED LOOPS (Cost=15 Card=1 Bytes=207) 5 4 NESTED LOOPS (Cost=14 Card=1 Bytes=187) 6 5 NESTED LOOPS (Cost=13 Card=1 Bytes=167) 7 6 NESTED LOOPS (Cost=12 Card=1 Bytes=147) 8 7 NESTED LOOPS (Cost=9 Card=1 Bytes=118) 9 8 NESTED LOOPS (Cost=6 Card=1 Bytes=49) 10 9 NESTED LOOPS (OUTER) (Cost=5 Card=1 Bytes=43) 11 10 NESTED LOOPS (Cost=4 Card=1 Bytes=23) 12 11 NESTED LOOPS (Cost=3 Card=1 Bytes=18) 13 12 TABLE ACCESS (BY INDEX ROWID) OF 'VEHICLE_MASTER' (Cost=2 Card=1 Bytes=9) 14 13 INDEX (UNIQUE SCAN) OF 'VHCMAS_PK' (UNIQUE) (Cost=1 Card=1) 15 12 TABLE ACCESS (BY INDEX ROWID) OF 'VEHICLE' (Cost=1 Card=1 Bytes=9) 16 15 INDEX (UNIQUE SCAN) OF 'VHC_PK' (UNIQUE) 17 11 TABLE ACCESS (BY INDEX ROWID) OF 'ACTIVITY_CENTRE' (Cost=1 Card=29 Bytes=145) 18 17 INDEX (UNIQUE SCAN) OF 'ACTCE_PK' (UNIQUE) 19 10 TABLE ACCESS (BY INDEX ROWID) OF 'TRACTION_UNIT_PANEL' (Cost=1 Card=21 Bytes=420) 20 19 INDEX (UNIQUE SCAN) OF 'TUP_PK' (UNIQUE) 21 9 TABLE ACCESS (BY INDEX ROWID) OF 'TECHNICAL_SERIES' (Cost=1 Card=39 Bytes=234) 22 21 INDEX (UNIQUE SCAN) OF 'SERIES_PK' (UNIQUE) 23 8 TABLE ACCESS (BY INDEX ROWID) OF 'VEHICLE_STATE' (Cost=9 Card=1 Bytes=69) 24 23 AND-EQUAL 25 24 INDEX (RANGE SCAN) OF 'VHCST_VALID_TO_NI' (NON-UNIQUE) (Cost=1 Card=1) 26 24 INDEX (RANGE SCAN) OF 'VHCST_VHC_FK_I'(NON-UNIQUE) (Cost=1 Card=1) 27 7 FILTER 28 27 REMOTE* OWNER2.WORLD 29 6 TABLE ACCESS (BY INDEX ROWID) OF 'MAINTENANCE_DUTY' (Cost=1 Card=12079 Bytes=241580) 30 29 INDEX (UNIQUE SCAN) OF 'MD_PK' (UNIQUE) 31 5 TABLE ACCESS (BY INDEX ROWID) OF 'MAINTENANCE_DUTY' (Cost=1 Card=12079 Bytes=241580) 32 31 INDEX (UNIQUE SCAN) OF 'MD_PK' (UNIQUE) 33 4 TABLE ACCESS (BY INDEX ROWID) OF 'MAINTENANCE_DUTY' (Cost=1 Card=12079 Bytes=241580) 34 33 INDEX (UNIQUE SCAN) OF 'MD_PK' (UNIQUE) 35 3 TABLE ACCESS (BY INDEX ROWID) OF 'MAINTENANCE_DUTY' (Cost=1 Card=12079 Bytes=241580) 36 35 INDEX (UNIQUE SCAN) OF 'MD_PK' (UNIQUE) 37 2 TABLE ACCESS (BY INDEX ROWID) OF 'MAINTENANCE_DUTY' (Cost=1 Card=12079 Bytes=241580) 38 37 INDEX (UNIQUE SCAN) OF 'MD_PK' (UNIQUE) 39 1 TABLE ACCESS (BY INDEX ROWID) OF 'MAINTENANCE_DUTY' (Cost=1 Card=12079 Bytes=241580) 40 39 INDEX (UNIQUE SCAN) OF 'MD_PK' (UNIQUE) 28 SERIAL_FROM_REMOTE SELECT "ID","VALID_FROM_DATE","VALID_TO_DATE", "SYMBOLIC_NAME" FROM "OWNER2"."A371_PTCAR" "PTC" WHERE :1="ID" 3/ SELECT VHCST.IMMOB_ID <= here a single column is selected => cf. 1/ above FROM VEHICLE_STATE VHCST, VEHICLE VHC, VEHICLE_MASTER VHCMAS, TECHNICAL_SERIES SERIES, TRACTION_UNIT_PANEL TUP, ACTIVITY_CENTRE ACTCE, MAINTENANCE_DUTY MD_OH, MAINTENANCE_DUTY MD_GS, MAINTENANCE_DUTY MD_WB, MAINTENANCE_DUTY MD_CW, MAINTENANCE_DUTY MD_CARWASH, MAINTENANCE_DUTY MD_CLEANUP, PTCAR PTC WHERE -- VHCST.VHC_ID = VHC.ID AND VHCST.VALID_TO = PA_TIME.f_valid_to() AND -- VHC.ID = VHCMAS.ID AND VHCMAS.ACTCE_ID = ACTCE.ID AND ACTCE.PTCAR_ID = PTC.ID AND PTC.VALID_TO_DATE >= pa_time.f_actdate_371() AND PTC.VALID_FROM_DATE <= pa_time.f_actdate_371() AND -- VHCST.MD_ID_OH = MD_OH.ID AND VHCST.MD_ID_GS = MD_GS.ID AND VHCST.MD_ID_WB = MD_WB.ID AND VHCST.MD_ID_CW = MD_CW.ID AND VHCST.MD_ID_CARWASH = MD_CARWASH.ID AND VHCST.MD_ID_CLEANUP = MD_CLEANUP.ID AND -- VHCMAS.SERIES_ID = SERIES.ID AND -- VHCMAS.TUP_ID = TUP.ID (+) and vhc.id=32;