Wrong index ttype label

Toad for Oracle - Beta
1

Hi,

Oracle 11.2.0.3 x64 with Toad 12.10.0.23 x64.

Global index on partition table has a label "function-based" ...

fb_index_wrong.jpeg
fb_index_wrong.jpeg1285×822 142 KB

Is this normal?

I.E. 2IX is defined as:
SET DEFINE OFF;
Prompt Index BILL_2IX;

-- BILL_2IX (Index)

CREATE INDEX MOBAPPO.BILL_2IX ON MOBAPPO.BILL
(BILLING_STATUS)
NOLOGGING
TABLESPACE BILL_I
NOPARALLEL
/

But does not have column name in " " ... in 5IX it is "BILL_DATE" ...

Is this a reason for wrong "function based" determination?

Brg

Damir

2

That information (Index Type = FUNCTION-BASED NORMAL) comes from Oracle, in DBA_INDEXES, in the INDEX_TYPE column. I agree that it doesn’t seem like a function-based index, judging from the script, but if there is a bug here, it is Oracle, not Toad.

3

select dbms_metadata.get_ddl(‘INDEX’,‘BILL_5IX’,‘MOBAPPO’) from dual;

CREATE INDEX “MOBAPPO”.“BILL_5IX” ON “MOBAPPO”.“BILL” (“EU_TOTAL_DUE_AMT”, “BILL_DATE” DESC)
PCTFREE 0 INITRANS 8 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 16 FREELIST GROUPS 4
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “STBS1_I”;

So, I think this is a bug from Oracle, when one column is surrounded with quottes … somehow Oracle interpret in one of its view as FB index.
Brg

Damir

4

All is OK-false alarm from me.

“DESC” part defines really FB index and quotes are to be sure that column is spaced with DESC reserved word…

:slight_smile:

5

I can reproduce it here. Oracle is definitely treating this like a function based index, I guess because one column is ascending and the other is descending. The following queries help you see what’s going on the data dictionary.

create table tables as
select * from dba_tables;

create index ind_test
on tables
(owner, table_name desc);

select index_type
from user_indexes
where index_name = ‘IND_TEST’

select column_position, column_Name, descend
from user_ind_columns
where index_name = ‘IND_TEST’
order by column_position;

select column_position, column_expression
from user_ind_expressions
where index_name = ‘IND_TEST’;