Home » RDBMS Server » Server Administration » Data retrieval speed between normal table and IOT on date type indexing
Data retrieval speed between normal table and IOT on date type indexing [message #62044] Mon, 21 June 2004 22:22 Go to next message
Shibaji Ghosh
Messages: 39
Registered: April 2002
Member
Hello,

I am trying to design a storage aspect which will basically be used for faster query access. There will be around 100 fields and millions of rows. I am in a dillema as to go by Index Organized table or a normal relational table.

The situation is as mentioned. There is a date type field and most of the queries will be based on date and time range. If I plan to go by IOT then the date field has to be the primary key and in such a case if I query with a format specifier (say to_char) then the index will not be used properly. I am getting a Full Index Scan and not a Index Range Scan.

Can you tell me whether I go by the above mentioned design or should I stick to a normal relational table and use a function based index (with to_char) on the date field.

While querying data which one will be faster. Also there is a trade off to be made with the data load time, since IOT will take more time in loading.

Thanks in advance.
Shibaji

 

 

 

Report message to a moderator

Re: Data retrieval speed between normal table and IOT on date type indexing [message #62046 is a reply to message #62044] Mon, 21 June 2004 22:40 Go to previous message
IA
Messages: 91
Registered: March 2004
Member
Hi,

I would create a normal table and create a function based index (to_char) on the primary key date column. This will avoid full table scans, and allow you the option of index range scans.

Thanks ... IA

Report message to a moderator

Previous Topic: Stupid question about import/export!
Next Topic: unsubscribe me plz
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Sep 20 14:15:03 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.