ajexpert
asked on
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;
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;
ASKER
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?
TYPE row_text_type IS TABLE OF VARCHAR2(11) INDEX BY PLS_INTEGER;
how the PLS_INTEGER will enhance the performance?
it doesn't enhance performance
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?
Do you find it by a number, or do you find it by a string?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
can I ask why you think the INDEX BY declarative might be a performance tool?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
By now, you might have got an idea, I am weak in collections, so more questions to follow
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
no, you need to specify a size
ASKER
Thanks Sean
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')