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 Go to next message
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 #546275 is a reply to message #546264] Tue, 06 March 2012 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 66552
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How can i optimize them as both of them are accessing nested tables.


If you want performances do NOT use nested tables but relational master/details ones.

Regards
Michel
Re: Nested Table query optimization [message #546282 is a reply to message #546275] Tue, 06 March 2012 01:49 Go to previous messageGo to next message
yaggy
Messages: 21
Registered: January 2012
Junior Member
Does using object-relational features mean loss
of performance?

Maybe I should explain some more:
I can not go with relational master/details approach as did some Migration activity(Migration of Array datatype in Postgresql to Nested table in Oracle)and Master/Details approach require changes in table structure.

Ok, i did not expect the query to be fast -
but i didn't expect it to be that slow either.



Thanks in advance...
Regards,
Yaggy

Re: Nested Table query optimization [message #546292 is a reply to message #546282] Tue, 06 March 2012 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 66552
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Does using object-relational features mean loss
of performance?


Yes, without any doubt.
Object features are just a patch on relational databases.
They are made for relational model NOT object one; so they internally convert object view into relational implementation and then performances impacts.

And you also add the overhead due to CLOB.

Of course, you must have an index on ID and even a "primary key" constraint, isn't it your primary key?

Regards
Michel
Re: Nested Table query optimization [message #546321 is a reply to message #546264] Tue, 06 March 2012 02:55 Go to previous messageGo to next message
yaggy
Messages: 21
Registered: January 2012
Junior Member
Yes Id is Primary Key.
Is there any other way to improve this?


Thanks in advance

Regards,
Yaggy
Re: Nested Table query optimization [message #546323 is a reply to message #546321] Tue, 06 March 2012 02:59 Go to previous messageGo to next message
Michel Cadot
Messages: 66552
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Yes Id is Primary Key.

And did you declare as it to Oracle (your code does not show it)?

Quote:
Is there any other way to improve this?

If the PK is activated and you can't change the model, no.

Regards
Michel
Re: Nested Table query optimization [message #546324 is a reply to message #546321] Tue, 06 March 2012 03:03 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
No !! I dont think so .. as Michel said Use master/details
Re: Nested Table query optimization [message #546788 is a reply to message #546324] Thu, 08 March 2012 23:30 Go to previous messageGo to next message
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 #546802 is a reply to message #546788] Fri, 09 March 2012 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 66552
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to explain us the format of your data file and where each part/field should go.

Regards
Michel
Re: Nested Table query optimization [message #546818 is a reply to message #546802] Fri, 09 March 2012 02:16 Go to previous messageGo to next message
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 #546820 is a reply to message #546818] Fri, 09 March 2012 02:27 Go to previous messageGo to next message
Michel Cadot
Messages: 66552
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I move the topic to the "Server Utilities" forum where you'll get a better chance to have the answer on SQL*Loader.

Regards
Michel
Re: Nested Table query optimization [message #546825 is a reply to message #546818] Fri, 09 March 2012 02:49 Go to previous messageGo to next message
Michel Cadot
Messages: 66552
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
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 Go to previous message
Barbara Boehmer
Messages: 8919
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;
Previous Topic: how to use Escape character for loading data via sql loader
Next Topic: SQL Loader Unable to open file error (2 Merged)
Goto Forum:
  


Current Time: Mon Sep 16 20:08:57 CDT 2019