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 -> View performance tuning

View performance tuning

From: Geld Marcoss <geld1_at_nexus.hu>
Date: Sat, 25 Feb 2006 17:37:57 +0100
Message-ID: <dtq15q$j4r$1@namru.matavnet.hu>


Hello,

I have a db layout as defined below. It contains 1 main table, 2 helper tables and 1 view.
The problem is that the view is a little bit slow. It means that a select ID from the view takes 4-6 times more time than the select ID from the table. I'm using Oracle 10.2.

How can I improve the speed of the view?

Thanks!

CREATE TABLE ErrorSpecs (

    errorId           INTEGER,
    errorIdDescr      VARCHAR2(100),

    CONSTRAINT SPECIFIC_PRI PRIMARY KEY (ERRORID)     )

CREATE TABLE ProbableCause (

    probableCauseOID         VARCHAR2(200),
    probableCauseDescr       VARCHAR2(100),
    CONSTRAINT PROBABLE_PRI PRIMARY KEY (PROBABLECAUSEOID)     )

CREATE TABLE PERF_TEST10 (

    ID                    NUMBER
    SPECIFICPROBLEMS      VARCHAR2(100)
    CEASEDALARMID         VARCHAR2(100)
    PROBABLECAUSEOID      VARCHAR2(100)
    SPECIFICPROBLEMSID    NUMBER
    TECHNOLOGY            NUMBER

    )

CREATE OR REPLACE VIEW PERF_VIEW2 (ID, PROBABLECAUSE, SPECIFICPROBLEMS, TECHNOLOGY, PROBABLECAUSEOID, SPECIFICPROBLEMSID) AS SELECT test.ID
,NVL(substr(prob.PROBABLECAUSEDESCR,2,instr(prob.PROBABLECAUSEDESCR,',')-3),to_char(test.PROBABLECAUSEOID)||'
:unknown value')
,NVL(test.SPECIFICPROBLEMS,NVL(substr(spec.erroriddescr,2,length(spec.erroriddescr)-2),to_char(test.SPECIFICPROBLEMSID)||'
:unknown value'))
,test.TECHNOLOGY
,NVL(prob.PROBABLECAUSEOID, test.PROBABLECAUSEOID)
,NVL(spec.ERRORID, test.SPECIFICPROBLEMSID)

FROM PERF_TEST10 test, ErrorSpecs spec, PROBABLECAUSE prob WHERE test.PROBABLECAUSEOID = prob.PROBABLECAUSEOID (+)

   AND test.SPECIFICPROBLEMSID = spec.ERRORID (+) Received on Sat Feb 25 2006 - 10:37:57 CST

Original text of this message

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