Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> View performance tuning
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),
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