Re: Q:Object query (nested tables?) vs query on normalized tables

You can index the nested tables,
but you may have to go through some
slightly 'counter-intuitive' steps to get data in and out.

The main performance overhead will
probably be due to the space taken
up by the introduction of what are effectively surrogate keys that you could otherwise
choose not to use.

I wonder if I could mention at this point "Chapter 16 Collection Objects" in my book which may answer a lot of your questions and give you some extra ideas as well.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



gnn_gnn wrote in message <93g8t3$3hs$1_at_slb7.atl.mindspring.net>...


>Hi Jonathan:

>

>It kind of looked like it was, because the docs talk about "hidden

 columns",


>etc.  So it isn't necessarily any BETTER performance-wise.  What are the

>odds of it being worse?  Can one make indexes on these phantom tables, etc.

>or if I want to search for credit card transaction data (as in my example)

>am I doing full table scans left and right?

>

>Thanks,

>    Mike

>

>

>

>Jonathan Lewis wrote in message

><979068270.22040.1.nnrp-10.9e984b29_at_news.demon.co.uk>...

>>

>>Nested tables are implemented as normlised tables

>>with a meaningless key combined with a hidden

>>unique constraint on the parent table, so it's all

>>overhead and no (physical) benefit over doing it

>>the relational way.

>>

>>

>>--

>>Jonathan Lewis

>>Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

>>

>>Practical Oracle 8i:  Building Efficient Databases

>>

>>Publishers:  Addison-Wesley

>>See a first review at:

>>http://www.ixora.com.au/resources/index.htm#practical_8i

>>More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html

>>

>>

>>

>>gnn_gnn wrote in message <93ebi5$t56$1_at_nntp9.atl.mindspring.net>...

>>>Hi:

>>>

>>>I have been looking at the documentation on defining types, objects,

 nested


>>>tables, etc. in Oracle 8i and have a basic question on the topic as a NB.

>>>Some of these things look very interesting, like points, lines, etc.  But

>>>how does the query speed stack up?  For example, if I wanted a list of

>>>people and all of their credit card transactions for a month (or several

>>>months) and wanted to do queries like "show all the people, and the

>>>transactions who spent more than $200 on clothes in the month of

 december".


>>>Would I be better off using something like nested tables

 (performance-wise)


>>>or breaking out the credit card transactions into a separate table with a

>>>foreign key back to the user table?  What are the pros and cons of each

>>>method?

>>>

>>>Thanks,

>>>   Mike

>>>

>>>

>>>

>>

>>

>

>