Hint not used

[JPDB_75]

Oracle 9.2
Optimizer choose

I have a table with 2 indexes.

When I make the following staement :

select /*+ index_asc (a attrib_name ) */ *
from attributes a
where rownum < 5

It makes a full of the table.

When I make this one :

select /*+ index_desc (a OT_ATTRIBUTELIST_FK ) */ *
from attributes a
where rownum < 5

It uses appropriately the index.

Both indexes exist ( btree) and have been analyzed.

Can someone explain ?

[padders]

Post table and index create scripts.

[JPDB_75]

Originally posted by padders
Post table and index create scripts.

DROP TABLE ATTRIBUTES CASCADE CONSTRAINTS ;

CREATE TABLE ATTRIBUTES (
ATTRIB_ID NUMBER NOT NULL,
OT_ID NUMBER NOT NULL,
ATTRIB_NAME VARCHAR2 (50),
ATTRIB_COLUMN_INDEX NUMBER,
ATTRIB_IN_REAL NUMBER,
ATTRIB_VALUE_LENGTH NUMBER,
ATTRIB_VALUE_TYPE NUMBER,
EDITABLE NUMBER (1),
CALCULABLE NUMBER (1),
CI NUMBER (1),
NAME NUMBER (1),
OBJECT_CODE NUMBER (1),
FORMULA VARCHAR2 (1000),
MODIF NUMBER (1),
IS_VISIBLE NUMBER (1),
MANDATORY NUMBER (1),
CONSTRAINT PK_ATTRIBUTES
PRIMARY KEY ( ATTRIB_ID )
USING INDEX
TABLESPACE IND_DATA PCTFREE 10
STORAGE ( INITIAL 10485760 NEXT 262144 PCTINCREASE 0 ))
TABLESPACE TAB_DATA
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 10485760
NEXT 262144
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 2147483645
FREELISTS 1 FREELIST GROUPS 1 )
NOCACHE;

ALTER TABLE ATTRIBUTES ADD CONSTRAINT FK_ATTRIBUT_OT_ATTRIB_OBJECTTY
FOREIGN KEY (OT_ID)
REFERENCES DIDIER.OBJECTTYPE (OT_ID) ;




CREATE INDEX ATTRIB_NAME ON
"DIDIER".ATTRIBUTES(ATTRIB_NAME)
TABLESPACE TAB_DATA PCTFREE 10 STORAGE(INITIAL 262144 NEXT 262144 PCTINCREASE 0 )
;

CREATE INDEX OT_ATTRIBUTELIST_FK ON
"DIDIER".ATTRIBUTES(OT_ID)
TABLESPACE IND_DATA PCTFREE 10 STORAGE(INITIAL 10485760 NEXT 262144 PCTINCREASE 0 )
;

[padders]

...and what differences do you see between "OT_ID" and "ATTRIB_NAME"?

[jmodic]

Well, NONE of your two queries will ever use any of those two indexes, no matter how many hints you include in it. Oracle simply can not give you answer to your query by using those two indexes, nor can they be of any help regarding speed for those two querise.

[JPDB_75]

Originally posted by jmodic
Well, NONE of your two queries will ever use any of those two indexes, no matter how many hints you include in it. Oracle simply can not give you answer to your query by using those two indexes, nor can they be of any help regarding speed for those two querise.

Well the second one uses the index.

It uses the index then goes back to the table to get
the remaining information.

The point was not to use the indexes to speed up the query.
I understand that they will be slower using the index.

I just want to understant why in one case, it uses follows the hint
and uses the index and in another case it does not.

[JPDB_75]

Originally posted by padders
...and what differences do you see between "OT_ID" and "ATTRIB_NAME"?

I do not understand whet you meant.

[waitecj]

quote:
--------------------------------------------------------------------------------
Originally posted by padders
...and what differences do you see between "OT_ID" and "ATTRIB_NAME"?
--------------------------------------------------------------------------------



I do not understand whet you meant.

different datatypes?

[jmodic]

Originally posted by JPDB_75
Well the second one uses the index.

It uses the index then goes back to the table to get
the remaining information.

Ah, I see. I thought CBO was clever enough to ignore a hint in a situation like that, but I was obviously wrong.

Originally posted by waitecj
different datatypes?

Nah, it's the NOT NULL constraint that makes the whole difference.

OT_ID is declared as NOT NULL, that means that every row in a table has to have an entry in the Btree index OT_ATTRIBUTELIST_FK. So it can sattisfy your query by using that wierd execution plan by range scanning the index and then using ROWIDs to visit rows in a table.

On the other hand, column ATTRIB_NAME is declared as NULL, meaning that there might be some rows that have null values for that attribute, meaning that those rows will not have their corresponding entries in the index ATTRIB_NAME. So CBO can not satisfy your query by using that index, as some rows might not be recorded in it.

[JPDB_75]

Originally posted by jmodic
Ah, I see. I thought CBO was clever enough to ignore a hint in a situation like that, but I was obviously wrong.

Nah, it's the NOT NULL constraint that makes the whole difference.

OT_ID is declared as NOT NULL, that means that every row in a table has to have an entry in the Btree index OT_ATTRIBUTELIST_FK. So it can sattisfy your query by using that wierd execution plan by range scanning the index and then using ROWIDs to visit rows in a table.

On the other hand, column ATTRIB_NAME is declared as NULL, meaning that there might be some rows that have null values for that attribute, meaning that those rows will not have their corresponding entries in the index ATTRIB_NAME. So CBO can not satisfy your query by using that index, as some rows might not be recorded in it.

Thanks for the explanation