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