If you’re using Oracle and you have tables with foreign keys, then you must remember to add indexes to all the columns in the referenced tables (i.e. the foreign tables). If your schema has more than two tables, it’s hard to make sure all the necessary indexes exist. Fret no more and let Oracle do (most of) the work for you:
select table_name, constraint_name,
cname1 || nvl2(cname2,','||cname2,null) ||
nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
columns
from ( select b.table_name,
b.constraint_name,
max(decode( position, 1, column_name, null )) cname1,
max(decode( position, 2, column_name, null )) cname2,
max(decode( position, 3, column_name, null )) cname3,
max(decode( position, 4, column_name, null )) cname4,
max(decode( position, 5, column_name, null )) cname5,
max(decode( position, 6, column_name, null )) cname6,
max(decode( position, 7, column_name, null )) cname7,
max(decode( position, 8, column_name, null )) cname8,
count(*) col_cnt
from (select substr(table_name,1,30) table_name,
substr(constraint_name,1,30) constraint_name,
substr(column_name,1,30) column_name,
position
from sys.user_cons_columns ) a,
sys.user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by b.table_name, b.constraint_name
) cons
where col_cnt > ALL
( select count(*)
from sys.user_ind_columns i
where i.table_name = cons.table_name
and i.column_name in (cname1, cname2, cname3, cname4,
cname5, cname6, cname7, cname8 )
and i.column_position <= cons.col_cnt
group by i.index_name
)
Isn’t it a beauty? Thanks to Tom.
Posted by digulla 
Dark Views 
Akaelae
28. November, 2009Don’t ask me how to pronounce that, I have no idea. Akaelae is a web-comic by Tiffany Ross. It’s one of those rare gems that warm the heart (and not only by raising your adrenaline level). If you like Stan Sakai’s Usagi Yojimbo or Elfquest, you’ll live this, too. It’s the story of a couple of childhood friends that get in all kinds of adventures at school, home, even space. The focus is rarely on the action but on the emotions and reasons of the characters. It’s about how people can hurt each other and how they deal with it. Here is an example: Darrik, a young, lonely black fox is moving to a new room and wants to say goodbye to a shy albino fox that’s living on the same floor. During the chat, she tells him that the wolves are only keeping them to sell them as slaves later. Which is why she is refusing to take the proficiency tests.
Darrik is confused. “Then aren’t you useless to them? If they’re running a slave trade? Wouldn’t they just sell you instead of feeding you, giving you clothing, art supplies, medical attention?”
Conclusion: Buy. You can find the whole story in the archive or support the starving artists by buying her books as PDF downloads over Lulu.
If you get confused with the characters and the names, visit the ComixPedia page: “The Cyantian Chronicles“.
Note that the Cyantian.net site has some technical difficulties (like images not showing up) now and then, but Tiff is always quick to fix that. Drop her a polite note if something lingers for more than a few days.
Share this: