troubleshooting Question

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

Avatar of sakthikumar
sakthikumar asked on
DatabasesOracle DatabaseSQL
10 Comments1 Solution754 ViewsLast Modified:
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
Join our community to see this answer!
Unlock 1 Answer and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros