« first day (302 days earlier)      last day (896 days later) » 

12:08 AM
Just popped in for a quick "lol" on @gbn and @Nick with the "horlicks" et al :)
 
12:26 AM
6
A: Efficient INSERT INTO a Table With Clustered Index

GonsaluThe ORDER BY clause in the SELECT statement is redundant. It is redundant because the rows that will be inserted must be sorted anyway. Let us create a test case. CREATE TABLE #Test ( id INTEGER NOT NULL ); CREATE UNIQUE CLUSTERED INDEX CL_Test_ID ON #Test (id); CREATE TABLE #Sequence ( ...

 
 
8 hours later…
8:52 AM
Morning chaps
 
gbn
9:12 AM
'lo
 
9:50 AM
@JackDouglas I appreciate your help, thank you. It's right. So I must find all foreign keys that references the table to be dropped and after importing recreate them, correct? And that is all?
 
@kupa Hi and welcome
@kupa you also need to consider grants
and possibly snonyms
full backups before you drop the tablespace and after you re-import are also essential IMO
 
10:06 AM
@JackDouglas exp has option GRANTS, doesn't export it all grants on that table? Or this option is for shcema and grants are of this schema on other objects(It seems to be more correct)?
So the list of mentionable items is:
1) FOREIGN KEYs
2) SYNONYMs
3) GRANTs?
 
I can't remember how synonyms work I just think you need to look them up. I think they just hang around and start working again after the import
I think you are right about grants, yes
that import will reapply them
just to be clear, you are dropping a tablespace, not a schema?
grants are stored in the the dictionary not in the tablespace itself
 
10:30 AM
@JackDouglas Synonyms will be invalid as I can guess, after compiling it will be OK.
So grant is for schema yes? If yes it is not my case. Yes I am dropping tablespace.
Grants are stored in dictionary that'r right but after dropping corresponding object entries in the dictionary are deleted as I know
 
that's right so you do just need to recreate
grants are given to schemas/roles
I'm not sure if there is a concept of 'invalid' for a synonyn - they are not code
but obviously they will not work unless the underlying object exists :)
I'll be interested to hear the outcome of this afterwards :)
 
if you drop object that has public synonym for example. This public synonym will be invalid
u can check it by select * from dba_objects where status='INVALID' and object_type='SYNONYM'
 
what do you get when you do 'select * from mysynonym' though?
 
do you mean user_synonyms ?
aaa
understood
there will be error that
this object doesn't exists
or something like that
let me check
Here is an error message: ORA-01775: looping chain of synonyms
 
er that doesn't sound right - I was also expecting 'object doesn't exist' or something like that
 
11:00 AM
I have two users mari_dba and mari_dba1.
I created table "a" in mari_dba, also created pubic synonym.
I granted select on that object to mari_dba1
dropped "a" table from mari_dba schema
At this time synonym got invalid.
recreated "a" table in mari_dba schema
after selecting Oracle trying to compile synonym and makes it VALID(But I had the situation that it couldn't compile it and synonym was left INVALID, I compiled it by hand with the command: alter synonym mysynonym compile;)
connected as mari_dba1 and selected "a" table. got your error that object doesn't exists. this happened because gr
error: ORA-01775: looping chain of synonyms happens when u have public synonym and don't have underlying object
did I explain well ?
 
@kupa yes, you did
It is a surprising error message also mentioned here
 
ORA-00980: synonym translation is no longer valid this one yes?
 
@kupa that would make more sense :)
@gbn, @Mark, @Nick please humour me with a sanity check
table foo(bar, baz, ...)
index on (bar, baz)
query select * from foo where baz=1
query is twice as fast with the index as without
because a full scan on the index plus lookup in the table hits fewer blocks than a full scan on the table
bad conclusion: yea my index is good
good conclusion: my index is pants (ie rubbish @Nick)
am losing the argument here
 
gbn
11:35 AM
@JackDouglas yep, you're insame
table (col1, col2, col3)
PK (col1, col2)
select * from foo where col2=1
index scan to PK lookup (aka key, boomark lookup)
 
@gbn ho ho
 
gbn
even with index (col2, col1), col3 isn't in index so would be ignored or still require the lookup
 
how does it find col2 in the PK if it is not a leading column? Oracle does skip-scans does SQL Server do something similar?
@gbn indeed
 
gbn
SQL Server would scan the index. Not seek
 
@gbn ie full scan?
 
gbn
11:41 AM
index scan, not table scan
but yes, index scan to find col2=1, then lookup back to PK to get rest of data
An index scan on a clustered index is a table scan
 
so full index scan plus lookup(s) to table
 
gbn
I was wrong
if clustered index, no lookup needed
 
@gbn what?!
 
gbn
if non-clustered, then a lookup is needed
 
so sane or insane
 
gbn
11:44 AM
@JackDouglas your eyes deceive you.. shurely
 
so sane or insane
 
gbn
a clustered index is covering by nature: non key columns are already at the bottom index level
 
I didn't mean to repeat that
 
in that case doesn't it result in a full clustered index scan?
it's equivalent to a table scan, isn't it?
 
gbn
yes
 
11:47 AM
@Gonsalu Hi Gonsalu
 
are the primary keys in postgresql similar to sql server in the sense that they're a clustered index?
 
hi @jack
is there even a clustered index concept in postgres?
 
postgres has no equivilent of IOT in Oracle or clustered key in SQL Server
 
so, all indexes in postgres are the equivalent to non-clustered indexes in SQL Server?
 
11:49 AM
but you can 'cluster' a table (which is just a one-off reordering of rows)
@Gonsalu yes
 
does the postgres query processor take advantage of that one-off reordering?
 
@Gonsalu I think that stats include clustering factors so yes
 
@jack interesting, thanks
 
but an index scan filtering on non-leading columns is a full scan not a seek (borrowing gbn's language) and I'm arguing that is not really 'using' the index - it is never going to be 'faster' in the way you expect an index to be faster
even if it is twice as fast (if you see what I mean)
@gbn going back to my original question, I still don't know if I'm sane or insane :)
have you changed your mind?
 
gbn
Sane of course. You're a mod. Don't want a horse's head in my bed tomorrow
2
 
11:58 AM
you make me laugh
 
gbn
@JackDouglas It can be faster in that less data is required in memory: the index pages,not the data pages
hence "covering et"
 
yes agreed
 
gbn
select col1 from foo where col2=1 canbe run from the btree only
select * from foo where col2=1 require alldata in the row
 
also agreed - then it is covering
that's two possible sppedup effects of the index
both edge effects - not using the btree to filter
right?
'filter' meaning range scan or lookup rather than full scan
 
yes, there's isn't an index seek
both cases require a (full) index scan
 
12:17 PM
@Gonsalu thanks, it's helpful to know I've not been barking up totally the wrong tree
 
 
6 hours later…
5:59 PM
@JackDouglas @jcolebrand Would you mind pointing out to Shark that editing your incorrect answer to match a correct answer is somewhat bad form? dba.stackexchange.com/posts/7521/revisions
Assuming it is bad form of course :)
 
it is
 
 
2 hours later…
gbn
 
i think all sql server questions should be migrated to christianity.se
 
gbn
nah, that's for MySQL
thedarkside.se for the rest
 
yeah i was hoping christianity.se would convert the sql server people...
 
gbn
hey, you're the one realising how poor MySQL is :)
in other news, I've got SQL Server onto the menu at work
 
haha
 
gbn
8:32 PM
how do you pay tax on wifi?
some one turns up and checks for a wifi router?
 
hmm
i think that's 'wireless' as in cell phone wireless
but either way, we pay an additional tax on each billing cycle
 
@MarkStoreySmith you may be interested in this comment. I just spent a good hour trying to a make a demo script that shows on the 6th insert into a temp table, relevant stats and plan need to be recompiled. No dice.
 
9:06 PM
@Nick thought this was interesting: english.stackexchange.com/questions/5684/…
 
9:18 PM
@Nick Cheers. I'll have a crack at the demo scripts tomorrow
 
9:37 PM
@DTest nice find. Kosmonaut mentioned "the shit" example gbn and I were discussing
 
yup
 
@MarkStoreySmith Word. The relevant block of text is under "Recompilation threshold (RT)" in that article I mentioned.
 
If you get a chance @Nick, would be interested to see if you get the same tipping point numbers as @MartinSmith and I with the scripts in dba.stackexchange.com/questions/7350/…
 
@MarkStoreySmith is there a way to get SSMS to open the plans output by your script using the graphical plan view (instead of plain XML)?
oh nvm, just had to "show actual execution plan"
 

« first day (302 days earlier)      last day (896 days later) »