Use of index in a VIEW

From: StefanoG <sorloff_at_libero.it>
Date: 25 Feb 2005 09:36:26 -0800
Message-ID: <54178c95.0502250936.7f436dfc@posting.google.com>

I have 2 tables where i must make a view with a special order by. I dont'use "cost based"
I create a view.
When i make explain plan on the select of view it's all OK When i make explain plan on the view it's explain is not optimal Wath I can made?

First put the View and after the 2 tables. Who can help me?

---

CREATE OR REPLACE VIEW V17_MESS_FOLDER_1 AS
SELECT ROWNUM ins_number,

       mess.fld_id, 
       m.* 
  FROM (SELECT  
                 f.fld_id, m.mess_id 
            FROM p17_folder_mess f, p17_messaggio m 
           WHERE f.mess_id = m.mess_id 
        ORDER BY NVL (m.data_mod, m.data_ins) ASC) mess, 
       p17_messaggio m 

 WHERE mess.mess_id = m.mess_id
--AND fld_id = 29;

---

CREATE TABLE P17_MESSAGGIO
(

  MESS_ID        NUMBER(10)                     NOT NULL,
  TIPO_MESS_ID   NUMBER(10)                     NOT NULL,
  USER_ID        NUMBER(10)                     NOT NULL,
  STATO          CHAR(1 BYTE)                   DEFAULT 'N'           
       NOT NULL,
  TITOLO         VARCHAR2(80 BYTE),
  DES            VARCHAR2(255 BYTE),
  TESTO          VARCHAR2(4000 BYTE),
  USER_INS_ID    NUMBER(10),
  DATA_INS       DATE,
  USER_MOD_ID    NUMBER(10),
  DATA_MOD       DATE,
  PUBBLICO_FLG   NUMBER(1)                      DEFAULT 0             
       NOT NULL,
  DATA_INIZIO    DATE,
  DATA_FINE      DATE,
  CHIUSO_FLG     NUMBER(1)                      DEFAULT 0             
       NOT NULL,
  PRIORITA       NUMBER(1),

  MESS_PADRE_ID NUMBER(10)
)
;

ALTER TABLE P17_MESSAGGIO ADD (
  CONSTRAINT XPKP17_MESSAGGIO PRIMARY KEY (MESS_ID)     USING INDEX
);

---

CREATE TABLE P17_FOLDER_MESS
(

  FLD_ID    NUMBER(10)                          NOT NULL,
  MESS_ID   NUMBER(10)                          NOT NULL,
  LINK_FLG  NUMBER(1)                           DEFAULT 0             
       NOT NULL

);

CREATE INDEX XIFP17FOLDERMESS_MESS ON P17_FOLDER_MESS (MESS_ID); ALTER TABLE P17_FOLDER_MESS ADD (
  CONSTRAINT XPKP17_FOLDER_MESS PRIMARY KEY (FLD_ID, MESS_ID)     USING INDEX
);

---

Received on Fri Feb 25 2005 - 11:36:26 CST