Link to home
Start Free TrialLog in
Avatar of sakthikumar
sakthikumar

asked on

Selecting from nested tables are very slow, anyway to minimize.

I have a table created through objects like follows.

CREATE TABLE "MMS_STG1"."MMS_AUDIT"
   (	"AUDIT_ID" NUMBER,
	"TABLE_NAME" VARCHAR2(30),
	"DML_TYPE" VARCHAR2(30),
	"MODIFIED_USER" VARCHAR2(100),
	"TIMESTAMP" DATE,
	"CHANGE_HISTORY" "MMS_STG1"."TABLE_HISTORY_TAB" ,
	 PRIMARY KEY ("AUDIT_ID")


CREATE OR REPLACE TYPE "MMS_STG1"."TABLE_HISTORY_TAB" IS TABLE OF table_history;

CREATE OR REPLACE TYPE table_history AS OBJECT (
column_name varchar2(30),
new_value varchar2(4000),
old_value varchar2(4000)
);

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
when trying query the table with filter clause in CHANGE_HISTORY columns like below

select *
  from mms_audit m, table(m.change_history) h
 where h.new_value = '4595198'
   and m.table_name = 'RELEASE_INVENTORY';

It is taking more time to retrieve the rows, Is there anyway I can get the results quickly.?
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sakthikumar
sakthikumar

ASKER

When I execute this,

create index idx_change_history on table_history(new_value);

Am getting a message like type,function,package is not allowed here.
first, as mentioned above,  the index is on the nested table, not on the parent table

also as noted previously,  your create table is missing the nested table clause - fix that.
Once you do, whatever your nested table is, that is the table you put the index on.  I called it "nested_history_tab"  but that's just a name I made up because you didn't specify it in your example  use your real nested table name instead  

finally, when you do create the index, you need to include the nested_table_id pseudo column like in the example above
sdstuber,

In my example, it does not have a nested table clause, but it is still working, can we do
anything on that to improve performance, or do we need change that to nested table for more performance?
whether you show it or not,  your table has an underlying table that holds the nested table.

Since you aren't showing it, I can only guess as to what that table is.

So, follow the exact steps shown above, except you need to use your real table name instead of nested_history_tab.

by "real table"  I mean the table name you are not showing,  not mms_audit.
If you don't know what the nested table is, you can look it up


select table_name from all_all_tables where table_type ='TABLE_HISTORY' and owner = 'MMS_STG1'
thanks sdstuber
Hi SDSTUBER,

I am not hiding anything, this is the exact code I used.

please try to create similar to this and check it is working.

and from there tell me how I can improve performance.

I dont know what I have created is nested table or not, but can you help me in improving on this.
>>> I am not hiding anything, this is the exact code I used.

I believe you, but the code you showed doesn't correspond to the example I gave


>>> please try to create similar to this and check it is working.

I already have, use the steps shown above

>>> I dont know what I have created is nested table or not

run the query above and post the results
I have come out from my previous company. I will not be able to get the data what is required to complete the solution.