"Geld Marcoss" <geld1_at_nexus.hu> wrote in message
news:dtq15q$j4r$1_at_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 Sun Feb 26 2006 - 05:55:36 CST
