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.?
DatabasesOracle DatabaseSQL

Avatar of undefined
Last Comment
sakthikumar

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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.
Sean Stuber

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
sakthikumar

ASKER
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?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Sean Stuber

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.
Sean Stuber

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'
sakthikumar

ASKER
thanks sdstuber
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
sakthikumar

ASKER
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.
Sean Stuber

>>> 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
sakthikumar

ASKER
I have come out from my previous company. I will not be able to get the data what is required to complete the solution.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck