• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 651
  • Last Modified:

INDEX BY clause in collection

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
ajexpert
Asked:
ajexpert
  • 11
  • 5
5 Solutions
 
sdstuberCommented:
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
 
ajexpertAuthor Commented:
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
 
sdstuberCommented:
it doesn't enhance performance
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
sdstuberCommented:
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
 
sdstuberCommented:
can I ask why you think the INDEX BY declarative might be a performance tool?
0
 
sdstuberCommented:
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
 
ajexpertAuthor Commented:
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
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
ajexpertAuthor Commented:
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
 
sdstuberCommented:
0
 
sdstuberCommented:
actually that is the same chapter, but a different section, just a little bit before the previous link
0
 
ajexpertAuthor Commented:
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
 
sdstuberCommented:
no, you need to specify a size

0
 
ajexpertAuthor Commented:
Thanks Sean
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 11
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now