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 (
@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?
@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
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
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 :)
@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.