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

sakthikumar
sakthikumar used Ask the Experts™
on
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.?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
your create table is missing the nested table clause

whatever the nested table store as specifies, create an index on that table, something like this...
ee.txt

Author

Commented:
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.
Most Valuable Expert 2011
Top Expert 2012

Commented:
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
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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?
Most Valuable Expert 2011
Top Expert 2012

Commented:
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.
Most Valuable Expert 2011
Top Expert 2012

Commented:
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'

Author

Commented:
thanks sdstuber

Author

Commented:
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.
Most Valuable Expert 2011
Top Expert 2012

Commented:
>>> 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

Author

Commented:
I have come out from my previous company. I will not be able to get the data what is required to complete the solution.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial