0

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);
SU88
  • 1
  • 3
  • Just to be clear, are you creating the index between inserting data into your GTT and querying the GTT? (Which would be a bad idea, but your wording is ambiguous, and it could cause this since DDL commits and your GTT defaults to not preserve rows on commit). Or has the index creation happened once and all runtime calls that insert/query now show this issue? – Alex Poole Apr 13 '21 at 14:21
  • index creation happens once only. Can you explain this "all runtime calls that insert/query now show this issue?". only the cursor using temp table has the issue. – SU88 Apr 14 '21 at 10:58
  • OK, the main thing was when the index was created. The index shouldn't make any difference to a query that works without it, so - as long as you haven\'t added a commit or rollback somewhere, or other DDL - you could be hitting a bug, particularly if you're on the base version of the very old 12.1.0.1. You might be able to get some idea of where it's going wrong if you look at the old and new execution plan; and might be able to rewrite the query to avoid the issue. Otherwise you might need to raise a service request with Oracle. – Alex Poole Apr 14 '21 at 11:13
  • Thanks Alex. Yap only change that I done was adding a index. So basically this could be a oracle bug. I will check the execution plans and see for any difference. – SU88 Apr 15 '21 at 14:46

0 Answers0