Home » RDBMS Server » Server Utilities » Nested Table query optimization (Oracle 10.0.2.0)
| Nested Table query optimization [message #546264] |
Tue, 06 March 2012 00:54  |
 |
yaggy
Messages: 21
Registered: January 2012
|
Junior Member |
|
|
Hi,
I have the below table ..
create or replace TYPE "NEST_TYPE" IS TABLE OF clob;
CREATE TABLE NESTED_TABLE
2 ("ID" NUMBER(38,0),
3 "NAME" VARCHAR2(100 BYTE),
4 "DESCRIPTION" NEST_TYPE,
5 "CITY_ALLOC" NEST_TYPE)
6 NESTED TABLE "DESCRIPTION" STORE AS "DESCRIPTION"
7 NESTED TABLE "CITY_ALLOC" STORE AS "CITY_ALLOC"
8 /
Below two query's are taking little more time if the data is more.How can i optimize them as both of them are accessing nested tables.
select column_value DESCRIPTION from NESTED_TABLE
,table(DESCRIPTION) where ID = 'dynamic value';
select column_value CITY_ALLOC from NESTED_TABLE
,table(CITY_ALLOC) where ID = 'dynamic value'
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Nested Table query optimization [message #546788 is a reply to message #546324] |
Thu, 08 March 2012 23:30  |
|
yaggy
Messages: 21
Registered: January 2012
|
Junior Member |
|
|
Hi,
Thanks for your comments...
Suppose i have implemented the design with relational master/details approach.
In my example i have modified design with master/detail approach.
CREATE TABLE NESTED_TABLE
1 ("ID" NUMBER(38,0) PRIMARY KEY,
2 "NAME" VARCHAR2(100 BYTE))
3 /
CREATE TABLE NESTED_TABLE_CHILD
1 ( "ID" NUMBER (38,0),
2 "DESCRIPTION" CLOB,
3 "CITY_ALLOC" CLOB,
4 CONSTRAINT "FKAID" FOREIGN KEY("ID") REFERENCES NESTED_TABLE ("ID"))
/
I used Sql loader utility for loading data.
My dat file is
1 neham {abcd,csadfasf,asfdfasdfad} {Raipur,Delhi,Kolkata}
2 sapnam \N {Hyderabad,Mumbai,Delhi}
How would be my control file if i used the mentioned approach?
Thanks in advance
Regards,
Yaggy
|
|
|
|
|
|
| Re: Nested Table query optimization [message #546818 is a reply to message #546802] |
Fri, 09 March 2012 02:16 |
|
yaggy
Messages: 21
Registered: January 2012
|
Junior Member |
|
|
Below is my dat file
ID Name Description City_alloc
1 neham abcd,csadfasf,asfdfasdfad Raipur,Delhi,Kolkata
2 sapnam \N Hyderabad,Mumbai,Delhi
Id and name should go into NESTED_TABLE and description and city_alloc should go into NESTED_TABLE_CHILD table.
and i want to store data in table the as below
select * from NESTED_TABLE ;
ID Name
1 neham
2 sapnam
select * from NESTED_TABLE_CHILD ;
ID DESCRIPTION CITY_ALLOC
1 abcd,csadfasf,asfdfasdfad Raipur,Delhi,Kolkata
2 Hyderabad,Mumbai,Delhi
|
|
|
|
|
|
| Re: Nested Table query optimization [message #546825 is a reply to message #546818] |
Fri, 09 March 2012 02:49 |
 |
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
I don't know how to do it in SQL*Loader but here's a first idea to do it with external table.
I had to slighty modify your data file to replace { and } by ":
1 neham "abcd,csadfasf,asfdfasdfad" "Raipur,Delhi,Kolkata"
2 sapnam \N "Hyderabad,Mumbai,Delhi"
Then
SQL> CREATE TABLE nested_table_ext (
2 ID INTEGER,
3 NAME VARCHAR2(100),
4 DESCRIPTION VARCHAR2(4000),
5 "CITY_ALLOC" VARCHAR2(4000)
6 )
7 organization external (
8 type oracle_loader
9 default directory filesdir
10 access parameters (
11 records delimited by newline
12 nobadfile
13 nologfile
14 nodiscardfile
15 fields terminated by ' ' optionally enclosed by '"'
16 missing field values are null
17 (id, "NAME", description, city_alloc)
18 )
19 location ('nested_table.dat')
20 )
21 reject limit unlimited
22 /
Table created.
SQL> col name format a10
SQL> col description format a30
SQL> col CITY_ALLOC format a30
SQL> select * from nested_table_ext;
ID NAME DESCRIPTION CITY_ALLOC
---------- ---------- ------------------------------ -----------------------------
1 neham abcd,csadfasf,asfdfasdfad Raipur,Delhi,Kolkata
2 sapnam \N Hyderabad,Mumbai,Delhi
And so:
SQL> alter table nested_table_child disable constraint fkaid;
Table altered.
SQL> insert all
2 into nested_table values (id, name)
3 into nested_table_child values (id, nullif(description,'\N'), city_alloc)
4 select * from nested_table_ext;
4 rows created.
SQL> alter table nested_table_child enable constraint fkaid;
Table altered.
SQL> select * from nested_table;
ID NAME
---------- ----------
1 neham
2 sapnam
2 rows selected.
SQL> select * from nested_table_child;
ID DESCRIPTION CITY_ALLOC
---------- ------------------------------ ------------------------------
1 abcd,csadfasf,asfdfasdfad Raipur,Delhi,Kolkata
2 Hyderabad,Mumbai,Delhi
2 rows selected.
This is a first step.
Regards
Michel
|
|
|
|
| Re: Nested Table query optimization [message #546887 is a reply to message #546825] |
Fri, 09 March 2012 12:29 |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following demonstrates how to load the data from the file that you provided to the tables that you provided in the format that you provided, using SQL*Loader.
-- test.dat:
1 neham {abcd,csadfasf,asfdfasdfad} {Raipur,Delhi,Kolkata}
2 sapnam \N {Hyderabad,Mumbai,Delhi}
-- test.ctl:
LOAD DATA
INFILE test.dat
INTO TABLE nested_table
FIELDS TERMINATED BY WHITESPACE
(id, name)
INTO TABLE nested_table_child
FIELDS TERMINATED BY WHITESPACE
OPTIONALLY ENCLOSED BY "{" AND "}"
(id POSITION(1),
name FILLER,
description NULLIF description="\\N",
city_alloc)
-- tables:
SCOTT@orcl_11gR2> CREATE TABLE NESTED_TABLE
2 ("ID" NUMBER ( 38,0) PRIMARY KEY,
3 "NAME" VARCHAR2 (100 BYTE))
4 /
Table created.
SCOTT@orcl_11gR2> CREATE TABLE NESTED_TABLE_CHILD
2 ("ID" NUMBER (38,0),
3 "DESCRIPTION" CLOB,
4 "CITY_ALLOC" CLOB,
5 CONSTRAINT "FKAID" FOREIGN KEY ("ID") REFERENCES NESTED_TABLE ("ID"))
6 /
Table created.
-- load data:
SCOTT@orcl_11gR2> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log
-- results:
SCOTT@orcl_11gR2> COLUMN name FORMAT A6
SCOTT@orcl_11gR2> COLUMN description FORMAT A25
SCOTT@orcl_11gR2> COLUMN city_alloc FORMAT A25
SCOTT@orcl_11gR2> SELECT * FROM nested_table
2 /
ID NAME
---------- ------
1 neham
2 sapnam
2 rows selected.
SCOTT@orcl_11gR2> SELECT * FROM nested_table_child
2 /
ID DESCRIPTION CITY_ALLOC
---------- ------------------------- -------------------------
1 abcd,csadfasf,asfdfasdfad Raipur,Delhi,Kolkata
2 Hyderabad,Mumbai,Delhi
2 rows selected.
|
|
|
|
| Re: Nested Table query optimization [message #546889 is a reply to message #546887] |
Fri, 09 March 2012 13:36 |
|
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Going back to your original question, have you tried using bind variables, as shown below?
VARIABLE dynamic_desc VARCHAR2(100)
EXEC :dynamic_desc := 'abcd'
select column_value DESCRIPTION from NESTED_TABLE
,table(DESCRIPTION) where ID = :dynamic_desc;
VARIABLE dynamic_city VARCHAR2(100)
EXEC :dynamic_city := 'Delhi'
select column_value CITY_ALLOC from NESTED_TABLE
,table(CITY_ALLOC) where ID = :dynamic_city;
|
|
|
|
Goto Forum:
Current Time: Mon Mar 03 17:13:04 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
