?
Solved

INDEX BY clause in collection

Posted on 2010-11-19
16
Medium Priority
?
647 Views
Last Modified: 2013-12-18
Can anyone tell me what exactly is meant by INDEX by clause?

How exactly INDEX BY clause will help in below collections?

Please explain with example

TYPE row_num_type  IS TABLE OF NUMBER       INDEX BY PLS_INTEGER;

TYPE row_text_type IS TABLE OF VARCHAR2(11) INDEX BY PLS_INTEGER;
0
Comment
Question by:ajexpert
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 5
16 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 34175562
it's not an "index" like a normal table

by PLS_INTEGER  means the elements of your collection are referenced by integers

x(1)
x(2)
x(3)

as opposed to INDEX BY VARCHAR2(10)

x('Hi')
x('Hello')
x('otherstring')
0
 
LVL 14

Author Comment

by:ajexpert
ID: 34175586
Ok Can you give me example of the following type?

TYPE row_text_type IS TABLE OF VARCHAR2(11) INDEX BY PLS_INTEGER;

how the PLS_INTEGER will enhance the performance?

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34175595
it doesn't enhance performance
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 74

Expert Comment

by:sdstuber
ID: 34175603
INDEX BY simply declares how you find stuff in your collection.

Do you find it by a number,  or do you find it by a string?



0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 34175612
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34175688
can I ask why you think the INDEX BY declarative might be a performance tool?
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 34175734
ok, now I can think of one.

sparse vs dense collections.


string based indexing is better for performance than numeric if your collection is sparse
numeric based indexing is better for performance than string if your collection is dense

by dense I mean  1,2,3,4,5,6,7...N  -  all index positions are populated

by sparse I mean 'Abacus',  'Gorilla', 'Mountain',  'Xylophone'  -  index positions could have many gaps between them

to compare directly try..

generating a thousand elements with random numbers as the indexes
then generate the same elements with those numbers converted to strings as the indexes.

the string version should perform better (slightly, but measurably)

conversely  if you have 1-1000,  and strings '1'-'1000'  the number version should perform better (slightly, but measurably)
0
 
LVL 14

Author Comment

by:ajexpert
ID: 34175737
Sean,


In the example

TYPE row_num_type  IS TABLE OF NUMBER      

x(1)
x(2)
x(3)
.
.
x(30)
.
.
x(50)

if I have to delete x(30) element, I have to traverse first then delete 30th element

Conversely, if I declare

TYPE row_num_type  IS TABLE OF NUMBER   INDEX BY BINARY INTEGER

I can delete x(30) without traversing, correct?

So I thought INDEX BY is useful in some way as we are gaining performance by not looping

Please correct me if I am wrong
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34175768
the extact threshold of how dense vs how sparse will depend on usage, memory, cpu, etc.  There is no exact number for when you should use one versus the other.  However I wouldn't worry about it usually.

Use whichever indexing is the most logical programmatically.
Only try to tune with this if you've exhausted all other performance options.
The gains are small and if it's not intuitively obvious why one method was chosen over the other then you're just adding complexity
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 34175787
you are wrong

the difference between the two declarations is you are creating two different types.
if you read the link above you'll get more complete descriptions, but in summary...

TYPE row_num_type  IS TABLE OF NUMBER    --- this declares a nested table structure

TYPE row_num_type  IS TABLE OF NUMBER   INDEX BY BINARY INTEGER  --- this declares an associative array
0
 
LVL 14

Author Comment

by:ajexpert
ID: 34175800
Ok, let me read it first then I will ask you more questions :)

By now, you might have got an idea, I am weak in collections, so more questions to follow
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 34175802
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 34175818
actually that is the same chapter, but a different section, just a little bit before the previous link
0
 
LVL 14

Author Comment

by:ajexpert
ID: 34176725
Thanks Sean,

I am still to complete the whole chapter but got some idea.

I believe there is no use of the following

TYPE row_text_type IS TABLE OF VARCHAR2(11) INDEX BY PLS_INTEGER;

it should be INDEX BY VARCHAR2 right?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34177427
no, you need to specify a size

0
 
LVL 14

Author Closing Comment

by:ajexpert
ID: 34207304
Thanks Sean
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question