database - does incremented column makes the b-tree index on the column unbalanced? -


i have been thinking 2 questions. couldn't find resources on internet this. how dbms handle ? or ? oracle.

before questions, here example: have master table "master" , slave table "slave". master table has "id" column primary key , index created oracle.slave table has foreign key "master_id" refers master table , "slave_no". these 2 primary key of slave table, again indexed.

 **master**  |  **slave**      (p) id <------> (p)(f) master_id                       (p) slave_no

now questions;

1- if master_id autoincremented column, , no record ever deleted, doesn't table's index unbalanced ? oracle rebuilds indexes periodically ? far know oracle balances index branches @ build time. oracle re-builds indexes automatically ever ? if level goes high levels ?

2- assuming oracle not rebuild automatically, apart scheduling job rebuilds index periodically, wiser order slave table's primary key columns reverse ? mean instead of "master_id", "slave_no" ordering "slave_no", "master_id"i, slave table's b-tree index more balanced ? (well each master table might not have exact number of slave records, still, seems better reverse order)

anyone know ? or opinions ?

if master_id autoincremented column, , no record ever deleted, doesn't table's index unbalanced ?

oracle's indexes never "unbalanced": every leaf in index @ same depth other leaf.

no page split introduces new level itself: leaf page not become parent new pages on non-self-balancing tree.

instead, sibling split page made , new record (plus possibly of records old page) go new page. pointer new page added parent.

if parent page out of space (can't accept pointer newly created leaf page), gets split well, , on.

these splits can propagate root page, split thing increases index depth (and pages @ once).

index pages additionally organized double-linked lists, each list on own level. impossible if tree unbalanced.

if master_id auto-incremented means splits occur @ end (such called 90/10 splits) makes dense index possible.

would wiser order slave table's primary key columns reverse?

no, not, reasons above.

if join slave master often, may consider creating cluster of 2 tables, indexed master_id. means records both tables, sharing same master_id, go same or nearby data pages makes join between them fast.

when engine found record master, index or whatever, means has found records slave joined master. , vice versa, locating slave means locating master.


Comments

Post a Comment

Popular posts from this blog

jquery - Invalid Assignment Left-Hand Side -

i trying assign class tag via jquery, resulting in error through firebug says "invalid assignment left-hand side" on fourth line below. <cfoutput> $('.pngfix a').each(function(index) { var hreflink = $(this).attr("href"); if (hreflink.tolowercase() = '../audio.cfm') { $(this).addclass('audioimg'); } }); </cfoutput> looking around online, looks syntax problem, don't seem see it... any tips? thanks if (hreflink.tolowercase() = '../audio.cfm') that attempts assign value, rather check equality. change = operator == .
Read more

java - Play! framework 2.0: How to display multiple image? -

i need display gallery of photos. here template: @(photos: list[photo]) @title = { <bold>gallery</bold> } @main(title,"photo"){ <ul class="thumbnails"> @for(photo <- photos) { <li class="span3"> <a href="#" class="thumbnail"> <img src="@photo.path" alt=""> </a> </li> } </ul> } and here controller method: public static result getphotos() { return ok(views.html.photo.gallery.render(photo.get())); } and here photo bean : @entity public class photo extends model { @id public long id; @required public string label; public string path; public photo(string path, string label) { this.path = path; this.label = label; } private static finder<long, photo> find = new finder<long, photo>( long.class, photo.class); public static list<phot...
Read more

gmail - Is there any documentation for read-only access to the Google Contacts API? -

i want read-only access user's google contacts. there stackoverflow member wanted this, , received following answer : yes, there is, use https://www.googleapis.com/auth/contacts.readonly scope , "view contacts". this exchange reference read-only access google contacts can find using google. simply using access token obtained attempt read usual google contacts api (i.e. https://www.google.com/m8/feeds ) fails insufficient permissions. similarly, using google contacts api on new target fails 404 errors (for example https://www.googleapis.com/auth/contacts.readonly/default/full ). is there documentation anywhere on how use api, or permissible way access google contacts api full read-write access?
Read more