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: "select *" MUCH quicker than "select <field> from...": explanation ?

Re: "select *" MUCH quicker than "select <field> from...": explanation ?

From: Spendius <spendius_at_muchomail.com>
Date: 7 Jul 2004 06:17:59 -0700
Message-ID: <aba30b75.0407070517.317db00b@posting.google.com>


Sybrand Bakker <sybrandb_at_hccnet.nl> 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; Received on Wed Jul 07 2004 - 08:17:59 CDT

Original text of this message

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