Index organized table

#41

Quote:

Index and data separation. Richard wrote some excellent articles about
this myth - err - concept:

http://richardfoote.wordpress.com/20...s-right-place/

From a glance at this I'd say that you might need less space with an IOT.
But space is not everything. Btw, are you sure that you'll never do
lookups based on PAGEURL?

I'm sure that I will not need an pageUrl as an any kind of key.

Quote:

I tend to be conservative about these things that's why I would leave it
at the default unless there are reasons to change it. I'd probably create
both versions and fire a defined load (sequence of insert, select etc.
statements) against both and measure performance as well as final size of
both tables and indexes. If you are just concerned with the size you
could just copy your current contents into a second table and look at the
sizes - although that will yield slightly skewed results (because of the
missing deletions and updates which might leave some holes).

If your volume is going to be large then partitioning would also be a
technique to think about but in your case hash partitioning seems the only
realistic option and then you have to carefully choose the partitioning
key so you can benefit from pruning for PK and SITEID accesses. Ideally
you would want to have partition local indexes only.

Kind regards

robert

tnx a lot.
I tried to create a separate copy of it and to make a load to see cpu/time
needed for that.
But, next problem was that I cannot find stats/size data in my Toad for that
(currently Oracle 10.2 XE with an Toad 8.5.1). I can see this data for all
other tables, but not for IOT.
Also, there are no data about IOT in tablespace info, and not in user_tables
(there are info, some are null).

do you know anything about that?

tnx... Dario

#42

Quote:

Index and data separation. Richard wrote some excellent articles about
this myth - err - concept:

http://richardfoote.wordpress.com/20...s-right-place/

From a glance at this I'd say that you might need less space with an IOT.
But space is not everything. Btw, are you sure that you'll never do
lookups based on PAGEURL?

I'm sure that I will not need an pageUrl as an any kind of key.

Quote:

I tend to be conservative about these things that's why I would leave it
at the default unless there are reasons to change it. I'd probably create
both versions and fire a defined load (sequence of insert, select etc.
statements) against both and measure performance as well as final size of
both tables and indexes. If you are just concerned with the size you
could just copy your current contents into a second table and look at the
sizes - although that will yield slightly skewed results (because of the
missing deletions and updates which might leave some holes).

If your volume is going to be large then partitioning would also be a
technique to think about but in your case hash partitioning seems the only
realistic option and then you have to carefully choose the partitioning
key so you can benefit from pruning for PK and SITEID accesses. Ideally
you would want to have partition local indexes only.

Kind regards

robert

tnx a lot.
I tried to create a separate copy of it and to make a load to see cpu/time
needed for that.
But, next problem was that I cannot find stats/size data in my Toad for that
(currently Oracle 10.2 XE with an Toad 8.5.1). I can see this data for all
other tables, but not for IOT.
Also, there are no data about IOT in tablespace info, and not in user_tables
(there are info, some are null).

do you know anything about that?

tnx... Dario

#43

Quote:

Index and data separation. Richard wrote some excellent articles about
this myth - err - concept:

http://richardfoote.wordpress.com/20...s-right-place/

From a glance at this I'd say that you might need less space with an IOT.
But space is not everything. Btw, are you sure that you'll never do
lookups based on PAGEURL?

I'm sure that I will not need an pageUrl as an any kind of key.

Quote:

I tend to be conservative about these things that's why I would leave it
at the default unless there are reasons to change it. I'd probably create
both versions and fire a defined load (sequence of insert, select etc.
statements) against both and measure performance as well as final size of
both tables and indexes. If you are just concerned with the size you
could just copy your current contents into a second table and look at the
sizes - although that will yield slightly skewed results (because of the
missing deletions and updates which might leave some holes).

If your volume is going to be large then partitioning would also be a
technique to think about but in your case hash partitioning seems the only
realistic option and then you have to carefully choose the partitioning
key so you can benefit from pruning for PK and SITEID accesses. Ideally
you would want to have partition local indexes only.

Kind regards

robert

tnx a lot.
I tried to create a separate copy of it and to make a load to see cpu/time
needed for that.
But, next problem was that I cannot find stats/size data in my Toad for that
(currently Oracle 10.2 XE with an Toad 8.5.1). I can see this data for all
other tables, but not for IOT.
Also, there are no data about IOT in tablespace info, and not in user_tables
(there are info, some are null).

do you know anything about that?

tnx... Dario

#44

On 17.06.2008 19:15, buu wrote:

Quote:

tnx a lot.
I tried to create a separate copy of it and to make a load to see cpu/time
needed for that.
But, next problem was that I cannot find stats/size data in my Toad for that
(currently Oracle 10.2 XE with an Toad 8.5.1). I can see this data for all
other tables, but not for IOT.
Also, there are no data about IOT in tablespace info, and not in user_tables
(there are info, some are null).

do you know anything about that?