| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: ORA-01450 on creation of new index.
On Wed, 28 Apr 1999 02:29:03 GMT, odessa7_at_my-dejanews.com wrote:
>I am trying to create a nonunique index on a varchar2(1000) (nulls ok) column
> Here is the script:
>
>CREATE BITMAP INDEX FILTER.ITEM_DESCS_ITEMNAME_DX
>ON FILTER.ITEM_DESCRIPTIONS(block_description)
>PARALLEL
>INITRANS 2
>MAXTRANS 255
>PCTFREE 5 --changed from 10
>STORAGE (INITIAL 51200
> NEXT 77824
> PCTINCREASE 1
> MINEXTENTS 1
> MAXEXTENTS 2147483645
> FREELISTS 1)
>TABLESPACE INDX
>
>Get this error Message 1: ORA-01450: maximum key length (758) exceeded
>
>I am planning on using 'like' in my where clause on this column. Do I need an
>index at all, and if yes how can I create it?
This doesn't seem like an appropriate column for a bitmap index. Bitmap indexes are for low-cardinality columns with relatively few values, such as the following:
Sex: M or F
Status: VALID, INVALID, PENDING, etc.
those types of things
Could you put a keyword from your 1000-byte column into another (shorter) column and index that?
Using LIKE will only use an index if the leading portion of the indexed column is referenced. For example:
" select * from table_A where index_col like 'ABC%' " will use an index on index_col.
" select * from table_A where index_col like '%ABC%' " will NOT use an index.
Therefore, an index may not be appropriate at all, depending on the nature of your queries. If you have a lot of text-searching to do, try Oracle ConText.
Chris
 |
 |