Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    Unanswered: performance problem

    I have i query when executing it only uses index unique scan and index range scan. cost is 29. this should be fast. but it takes 9 secons. when i add hint first_rows. it's happening within 500 msecs. can anyone tell me why this is so. I actually don't know why it takes the first time so long. is there something wrong with the optimiser or did i formulated the query wrong.

    SELECT tc.COLUMN_NAME, tc.DATA_TYPE domain_id,
    DECODE(tc.DATA_TYPE, 'NUMBER', tc.DATA_PRECISION, tc.DATA_LENGTH) width, d.sfieinput,d.sfieprotected
    FROM DBA_CONS_COLUMNS child, DBA_CONSTRAINTS cc, DBA_TAB_COLUMNS tc, ish.sfield d
    WHERE child.OWNER = 'ISH'
    AND child.TABLE_NAME = 'BUILDINGPART'
    AND cc.OWNER = 'ISH'
    AND cc.CONSTRAINT_NAME = child.CONSTRAINT_NAME
    AND cc.TABLE_NAME = 'BUILDINGPART'
    AND cc.CONSTRAINT_TYPE = 'P'
    AND tc.OWNER = 'ISH'
    AND tc.TABLE_NAME = 'BUILDINGPART'
    AND tc.COLUMN_NAME = child.COLUMN_NAME
    AND d.sfiedbd = 'UMM'
    AND d.sfierec = 'BUILDINGPART'
    AND d.sfiename = tc.COLUMN_NAME
    ;

  2. #2
    Join Date
    Jul 2003
    Location
    Near Paris France
    Posts
    60
    can you give us the table structure of BUILDINGPART
    and some values to feed it?

  3. #3
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149
    Originally posted by pmscontact
    can you give us the table structure of BUILDINGPART
    and some values to feed it?
    Attached Thumbnails Attached Thumbnails bpart.jpg  

  4. #4
    Join Date
    Jul 2003
    Location
    Near Paris France
    Posts
    60
    Please the SQL script, I don't want to rwrite all.

    There is a lot of tools to do it simply, as this one "Oracle tool"
    on http://membres.lycos.fr/pmscontact/
    it is free

  5. #5
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    af

    Create Table ISH.BUILDINGPART (
    BLDPID VARCHAR2 (11) NOT NULL,
    TIME_STAMP DATE,
    BLDPRECSTATUS NUMBER (8) DEFAULT 0 ,
    BLDPEXPORTED NUMBER (8) DEFAULT 0 ,
    BLDPDESCR VARCHAR2 (41),
    BLDPDEPID VARCHAR2 (11),
    BLDPCONTEXT NUMBER (16) DEFAULT 1 ,
    BLDPBLDID VARCHAR2 (11) NOT NULL,
    BLDPYEAR1 NUMBER (8) DEFAULT 0 ,
    BLDPCOSTYEAR1 NUMBER (32, 6) DEFAULT 0 ,
    BLDPYEAR2 NUMBER (8) DEFAULT 0 ,
    BLDPCOSTYEAR2 NUMBER (32, 6) DEFAULT 0 ,
    BLDPYEAR3 NUMBER (8) DEFAULT 0 ,
    BLDPCOSTYEAR3 NUMBER (32, 6) DEFAULT 0 ,
    BLDPYEAR4 NUMBER (8) DEFAULT 0 ,
    BLDPCOSTYEAR4 NUMBER (32, 6) DEFAULT 0 ,
    BLDPYEAR5 NUMBER (8) DEFAULT 0 ,
    BLDPCOSTYEAR5 NUMBER (32, 6) DEFAULT 0 ,
    BLDPYEAR6 NUMBER (8) DEFAULT 0 ,
    BLDPCOSTYEAR6 NUMBER (32, 6) DEFAULT 0 ,
    BLDPYEAR7 NUMBER (8) DEFAULT 0 ,
    BLDPCOSTYEAR7 NUMBER (32, 6) DEFAULT 0 ,
    BLDPYEAR8 NUMBER (8) DEFAULT 0 ,
    BLDPCOSTYEAR8 NUMBER (32, 6) DEFAULT 0 ,
    BLDPYEAR9 NUMBER (8) DEFAULT 0 ,
    BLDPCOSTYEAR9 NUMBER (32, 6) DEFAULT 0 ,
    BLDPYEAR10 NUMBER (8) DEFAULT 0 ,
    BLDPCOSTYEAR10 NUMBER (32, 6) DEFAULT 0 ,
    BLDPYEAR11 NUMBER (8) DEFAULT 0 ,
    BLDPCOSTYEAR11 NUMBER (32, 6) DEFAULT 0 ,
    BLDPYEAR12 NUMBER (8) DEFAULT 0 ,
    BLDPCOSTYEAR12 NUMBER (32, 6) DEFAULT 0 ,
    BLDPYEAR13 NUMBER (8) DEFAULT 0 ,
    BLDPCOSTYEAR13 NUMBER (32, 6) DEFAULT 0 ,
    BLDPYEAR14 NUMBER (8) DEFAULT 0 ,
    BLDPCOSTYEAR14 NUMBER (32, 6) DEFAULT 0 ,
    BLDPYEAR15 NUMBER (8) DEFAULT 0 ,
    BLDPCOSTYEAR15 NUMBER (32, 6) DEFAULT 0 ,
    BLDPYEAR16 NUMBER (8) DEFAULT 0 ,
    BLDPCOSTYEAR16 NUMBER (32, 6) DEFAULT 0 ,
    BLDPYEAR17 NUMBER (8) DEFAULT 0 ,
    BLDPCOSTYEAR17 NUMBER (32, 6) DEFAULT 0 ,
    BLDPYEAR18 NUMBER (8) DEFAULT 0 ,
    BLDPCOSTYEAR18 NUMBER (32, 6) DEFAULT 0 ,
    BLDPYEAR19 NUMBER (8) DEFAULT 0 ,
    BLDPCOSTYEAR19 NUMBER (32, 6) DEFAULT 0 ,
    BLDPYEAR20 NUMBER (8) DEFAULT 0 ,
    BLDPCOSTYEAR20 NUMBER (32, 6) DEFAULT 0 ,
    BLDPWALLM2 NUMBER (32, 6) DEFAULT 0 ,
    BLDPFLOORGROSSM2 NUMBER (32, 6) DEFAULT 0 ,
    BLDPFLOORTAREM2 NUMBER (32, 6) DEFAULT 0 ,
    BLDPFLOORNETTM2 NUMBER (32, 6) DEFAULT 0 ,
    BLDPFLOORNETTINSTM2 NUMBER (32, 6) DEFAULT 0 ,
    BLDPFLOORNETTTRAFVERTM2 NUMBER (32, 6) DEFAULT 0 ,
    BLDPFLOORNETTTRAFHORM2 NUMBER (32, 6) DEFAULT 0 ,
    BLDPFLOORNETTUSABM2 NUMBER (32, 6) DEFAULT 0 ,
    BLDPIMAGEFILE VARCHAR2 (254)
    )

    PCTFREE 10
    INITRANS 1
    MAXTRANS 255
    TABLESPACE USERS
    STORAGE (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    )

    LOGGING

  6. #6
    Join Date
    Jul 2003
    Location
    Near Paris France
    Posts
    60
    sorry I miss this table on my request

    ish.sfield

    and join file with data to feed these tables

    regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •