Re: Use of index in a VIEW

StefanoG wrote:

> 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
> );
>
> -------------

Your post is a collection of bad practices. Give serious consideration to the advice from Sybrand.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)