Re: Use of index in a VIEW

Comments embedded

On 25 Feb 2005 09:36:26 -0800, sorloff_at_libero.it (StefanoG) wrote:

>I have 2 tables where i must make a view with a special order by.
>I dont'use "cost based"

Which you shouldn't do.

>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?

No one can derive the execution plan from the view definition. You need to include the 'optimal' and 'not optimal' execution plan. May be the optimizer is right, who can tell?

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

You shoudln't use * in a view definition and also you shouldn't use rownum. In a view context you can't rely on them.
>
>
>-------------
>
>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
>);
>
>-------------

--
Sybrand Bakker, Senior Oracle DBA