After adding index global temporary table data will not get fetched

628 Views Asked by At

Need some help to identify the reason for the below issue. I have created a global temporary table as below:

Create global temporary table glo_temp_table
(
  row_no             NUMBER not null,
  resource_id        VARCHAR2(40),
  company_id         VARCHAR2(20),
);

This table’s data gets inserted during the runtime by a function which later used by a another function to fetch data using a cursor. This functionally works fine without any issue. Problem starts when I add an index below (to clear this is not done during the run time.):

CREATE INDEX SS ON glo_temp_table (resource_id);

Now no data will gets fetched by the cursor. Is there any specific reason for this behavior? How can I created a such a index to work properly?

Oracle db veriosn is 12c Release 12.1.0.1.0 This table only has the below constrain only.

alter table glo_temp_table
  add constraint glo_temp_table_PK primary key (ROW_NO);
0

There are 0 best solutions below