The Heap™ – Consultancy ©®

12:08 AM

Just popped in for a quick "lol" on @gbn and @Nick with the "horlicks" et al :)

A: Efficient INSERT INTO a Table With Clustered Index

12:26 AM

The 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

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

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

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

SQL Server would scan the index. Not seek

@gbn ie full scan?

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

I was wrong

if clustered index, no lookup needed

@gbn what?!

if non-clustered, then a lookup is needed

so sane or insane

11:44 AM

@JackDouglas your eyes deceive you.. shurely

so sane or insane

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?

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?

Sane of course. You're a mod. Don't want a horse's head in my bed tomorrow

11:58 AM

you make me laugh

@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

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 :)

jcolebrand

it is

2 hours later…

8:21 PM

One for us? stackoverflow.com/q/7986783/27535

i think all sql server questions should be migrated to christianity.se

nah, that's for MySQL

thedarkside.se for the rest

yeah i was hoping christianity.se would convert the sql server people...

hey, you're the one realising how poor MySQL is :)

in other news, I've got SQL Server onto the menu at work

haha

8:32 PM

how do you pay tax on wifi?

theregister.co.uk/2011/11/02/us_tax_freeze

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/…