The Heap™ – Consultancy ©®

12:26 AM

@Zane "We're no strangers to [MOVE], you know the rules and so do I. Take the database offline, update the new file location with ALTER DATABASE, move the files in the filesystem, bring the database back online ..."

Zane

1:47 AM

@bluefeet Thanks homey!

@wBob I always need to look up syntax. It's not a thing I bother to remember.

Paul Vargas

2:39 AM

@swasheck Hi!

Paul Vargas

2:56 AM

I lost the script of the database of Greek.

Do you have the database on hand?

I am looking for all the occurrences of substantive + conjunction + substantive.

Andriy M

7:37 AM

Linking to that SO answer is probably useful but you aren't adding anything new to the post, are you. You could have just posted the link as a comment. — Andriy M 39 mins ago

The solution itself seems like a nice addition to the other options on that old question, but... I don't know... It's basically a verbatim copy of the answer without any added value.

Maybe it's all right if the linked post is outside the site (even though it's from a sister site within the same network)?

ypercubeᵀᴹ

7:57 AM

@JSapkota using rownum has a dirty feeling but get a +1.

@MartinSmith thnx!

JSapkota

9:08 AM

@ypercubeᵀᴹ well i liked the alternative given. I appreciate your comment and advice. thnx :)

Phil

HELLO EARTHLINGS

JSapkota

@ypercubeᵀᴹ may i know why have dirty feeling on it?

ypercubeᵀᴹ

@JSapkota I just don't like using rownum. I know it's handy sometimes.

You could replace it:

on t1.rn = t2.rn where rownum<=5;

with:

on t1.rn = t2.rn and t1.rn<=5 and t2.rn<=5;

JSapkota

@ypercubeᵀᴹ i will do it.

ypercubeᵀᴹ

I mean it's not obvious which rows will be selected.

Will the outcome be different if I use ORDER BY?

Andriy M

9:13 AM

Why both t1.rn<=5 and t2.rn<=5, though?

ypercubeᵀᴹ

@AndriyM because we want to filter before the join.

But you are right, we might use just one of the 2.

Andriy M

Ah, I see, using just one might not necessarily filter out both subsets (depends on the optimiser, I guess).

(before the join, that is)

ConcernedOfTunbridgeWells

Morning all

Phil

Aha, the bionic man

ConcernedOfTunbridgeWells

Not sure I qualify as better, stronger, faster.

Yet, anyway.

The schedule for my meds takes up an entire side of A4 paper.

12 separate items.

@Phil So, how's life up north?

Phil

9:23 AM

Very busy

Which is good. Takes my mind off the crapper aspects of life

Dragging the business kicking & screaming into the 21st century

ConcernedOfTunbridgeWells

@Phil Fair enough. Hope the crappier aspects in your life improve.

Phil

@ConcernedOfTunbridgeWells It's ok. Self medicate with beer and good friends :)

dezso

@PaulWhite my opinion materialized itself as a NAA flag

then they added the bit from Craig's comment, which I haven't seen until now

now that the OP changed to CSV logging, the first part does not make much sense anymore. At least not on the top.

If I find the time, I'll edit it a bit.

JSapkota

@ypercubeᵀᴹ i think you mean on t1.rn=t2.rn where t1.rn<=5

ConcernedOfTunbridgeWells

@PaulWhite I think partitioning got its reputation back in the glory days of Oracle when the technology du jour was Sun A5200 F/C JBODs with 9-18GB disks in them. Then you had to partition if you wanted a decent sized fact table. Plus it could use parallel query to get I/O from multiple physcial volumes at once.

JSapkota

9:33 AM

@ypercubeᵀᴹ if i add ORDER BY the outcome is same.

ypercubeᵀᴹ

@JSapkota No. I meant what I wrote

You could use: on t1.rn=t2.rn where coalesce(t1.rn, t2.rn)<=5

And while thinking on it, why do we need a FULL join? Why not a simple INNER join?

JSapkota

@ypercubeᵀᴹ if i do that i could not restrict rows to top 5.

ypercubeᵀᴹ

Doesn't the where rownum<=5 restrict the result to 5 rows?

JSapkota

yes i did

yes it did

but if i use on t1.rn = t2.rn and t1.rn<=5 and t2.rn<=5; i get all rows + 1

ypercubeᵀᴹ

You would only need a FULL join if the two result sets were from different tables or from different conditions.

@JSapkota Yeah, that might be wrong. FULL join is confusing.

JSapkota

9:40 AM

yah same here

@ypercubeᵀᴹ i can just use ` on t1.rn=t2.rn where t1.rn<=5` or use union on it what would better in your opinion?

ypercubeᵀᴹ

@JSapkota Add both ways, I think. Depends what the OP wants.

If they want to have the TOP 1 with the BOTTOM 1 and compare, they can use the FULL join.

If they just want the top 5 and bottom 5 in a list, they can use the UNION.

JSapkota

@ypercubeᵀᴹ got it. thanks indeed

Paul White

10:10 AM

@dezso Ok thanks for that.

dezso

@PaulWhite and done

Andriy M

10:35 AM

@ypercubeᵀᴹ @JSapkota Why not get both RNs in one query and then … SELECT * FROM enumerated WHERE rn_asc <= 5 OR rn_desc <= 5;?

ypercubeᵀᴹ

10:45 AM

@AndriyM that, too

dezso

Which is exactly what they did not want to do. — dezso 18 secs ago

Tom V

11:37 AM

yikes How to do that? (I'm afraid to experiment, because I'm on a live db).

(not a rick-roll)

JSapkota

12:11 PM

@AndriyM thats also fine.

JSapkota

12:31 PM

@AndriyM `select * from(select row_number() over (order by sales desc) rn_desc, row_number() over (order by sales asc) rn_asc
from tbl_sales) where rn_desc<=5 or rn_asc<=5;`

ypercubeᵀᴹ

1:48 PM

and if you are on a live DB, take backups before you run random code someone on the internet suggests — Tom V 1 hour ago

@TomV I was thinking of answering the same thing: "First, take a backup..."

Tom V

@ypercubeᵀᴹ yes I had visions of a guy running random code in his live environment and then blaming other people for trying to help

James Lupolt

2:04 PM

@MaxVernon Are you coming over for SQLBits this year?

Max Vernon

@JamesLupolt oh man, I wish. When is it?

James Lupolt

Early May

in Liverpool

Max Vernon

ahhh. No, likely my mum will be visiting us in May

it sounds like a great event though!

James Lupolt

2:22 PM

Chris Adkin is finally doing his updated version of Kejser's course

That's the main thing I'm excited about

Wasn't sure if you might have been thinking about coming. Pound has fallen so much that it's a lot cheaper than it might have been a few months ago

JSapkota

2:42 PM

@PaulWhite i just saw your comment on one of the answer regarding migration. So i have downloaded the documents from MOS and shared through cloud storage that would be legal or not? Also can i do that here stackexchange?

^ in stackexchange.

Paul White

@JSapkota Oh the Oracle documentation that doesn't require a login thing?

Do you have a link that does not require a login to access? — Paul White ♦ 2 hours ago

JSapkota

no i mean from mos knowledge base documents.

Paul White

@JSapkota Yeah I was asking if you were referring to the comment above. That was 2 hours ago, and I look at a lot of questions, so I want to make sure we're talking about the same issue.

If it was a different question/answer/comment, please share a link.

JSapkota

@PaulWhite http://dba.stackexchange.com/questions/130749/migrating-database-from-oracle-11-2-0-03-to-11-2-0-04
here it is.
what i mean is from My Oracle support we get access to download knowledge base documents for specific purpose such as migration that is only available to those who has mos account. so if i have access on it and downloaded that documents and shared over here that would be legal or not?

Paul White

@JSapkota Thank, OK so it is the same question. We don't give legal/copyright advice; it's the users responsibility to check that they are allowed to share information from an external source. There is some general advice here though: How to reference material written by others. I'd hate for you to get into trouble.

Perhaps @Phil or @JackDouglas would have an opinion?

My personal opinion is that it's unlikely to be OK.

JSapkota

2:52 PM

@PaulWhite thats why i asked this before actually getting into trouble ;)

Phil

What the what now?

Paul White

@JSapkota Well it's very nice of you to think of that option, but probably better to err on the safe side.

10 mins ago, by JSapkota

@PaulWhite i just saw your comment on one of the answer regarding migration. So i have downloaded the documents from MOS and shared through cloud storage that would be legal or not? Also can i do that here stackexchange?

@Phil This ^

Phil

@PaulWhite NEVER share anything from MOS on here. Oracle legal are terribad people, and we don't want to open that can of worms

3

The material is behind a support contract paywall for a reason

Paul White

@Phil Sweet, thanks very much.

@Phil MOS = My Oracle Support?

Phil

@PaulWhite Yup

JSapkota

2:55 PM

@Phil @PaulWhite the linked shared http://dba.stackexchange.com/questions/130749/migrating-database-from-oracle-11-2-0-03-to-11-2-0-04
would be legal because that will be accessed by those who have got MOS account.

Paul White

@JSapkota Seems right.

Phil

@JSapkota Legal, but completely a pointless answer, as the content is not viewable to 99.99% of the people that use this site, and also out of the reach of search engines

JSapkota

@Phil thats what i am currently thinking of

Paul White

@Phil Oh that's not entirely fair. The answer has other text besides the very small link at the end. So it's harsh to say it's pointless. Better than the question comments that essentially say the same thing.

JSapkota

@Phil what would the best way to share the knowledge that we have learnt from these MOS knowledge base documents? Just explain that over here as answer? or any better way?

Paul White

2:59 PM

The community has closed the question anyway, so this might all be moot.

@JSapkota No one can stop you writing about things you know in your own words (unless you've signed some specific NDA, I suppose).

JSapkota

@PaulWhite i agree that.

Paul White

@JSapkota If you decide you want to add a better answer to that question (without any MOS content!) ask a moderator to reopen it for you.

JSapkota

@PaulWhite it depends upon the environment of the OP so the area that i should cover would be bigger. If i get something precise and useful i will ask moderator.

Paul White

@JSapkota Oh OK, so it really is "too broad" then :)

JSapkota

@PaulWhite best answer would be contact MOS if that is critical production environment. :)

@PaulWhite i think so 'too broad'

wBob

3:24 PM

@JamesLupolt lol I'm sat next to Chris right now.

JSapkota

4:20 PM

for this dba.stackexchange.com/questions/130917/…

ypercubeᵀᴹ

@Phil I like the "paywall"

This afternoon: Operation "Restore". Step 1: coffee

JSapkota

@PaulWhite i got similar question on SO dba.stackexchange.com/questions/130917/… for this dba.stackexchange.com/questions/130917/…

Paul White

@JSapkota Sorry I don't understand what you're saying. All three links are the same.

JSapkota

@PaulWhite stackoverflow.com/questions/29116396/…

i found the similar question and answer for this dba.stackexchange.com/questions/130917/…

Paul White

@JSapkota Ha OK posting the same link four times is quite enough :) What are you asking though? I'm not an Oracle person, so I can't say if those are the same issue or not. Are you asking what you should do? Perhaps provide the link to the DBA question author in a comment? See what s/he makes of it?

Or write an answer?

JSapkota

4:30 PM

@PaulWhite sorry for that. okay i got it,

i was thinking to write an answer for this anyway lets see what the OP wants.

Paul White

No worries. Right, I'm off for a while now.

ypercubeᵀᴹ

@JackDouglas still planning on going to the Postgres meeting next week?

James Lupolt

@wBob Cool. Are you both working in London?

wBob

@JamesLupolt Yes. Chris is doing his deck when he should be working lol

James Lupolt

4:52 PM

Good to hear. I was afraid I'd have to start working at work if I became a contractor.

wBob

@JamesLupolt Down with work!

Phil

5:06 PM

@ypercubeᵀᴹ Larry has boats to pay for, you know!

Andriy M

5:21 PM

Or pays for boats to have? It's no difference, I guess

Jack Douglas

6:56 PM

@JSapkota it would breach their copyright and the T&Cs of MOS — as Phil says, please don't do it.

2

@ypercubeᵀᴹ I am, how about you, can you still make it?

ypercubeᵀᴹ

7:17 PM

@JackDouglas Yes. Unless anything weird happens in the mean time, I'll be there.

Lamak

@JackDouglas heeey, you are alive :-)

Jack Douglas

@Lamak life does just seems to get busier, do you find that too?

Lamak

nah

Jack Douglas

homeschooling is the main culprit I'm in the middle of a maths lesson :)

Tom V

@JackDouglas Life may bet busier, but I feel I get better at planning/separating work from private life as time goes by which makes it actually feel less busy

Jack Douglas

7:29 PM

having to re-learn stuff I've literally never used once since leaving school

@TomV I envy you a bit

Tom V

@JackDouglas It's some sort of revelation, I couldn't put work aside earlier and was forced to rethink the way I lived life. I'm much calmer now.

dezso

@TomV I'll master it by the time I retire

Tom V

The way somebody put it to me: you don't have to be in jail to be captured

dezso

and hi @JackDouglas

Tom V

but it sounded better in Dutch :)

Jack Douglas

7:34 PM

@dezso hi :)

dezso

@TomV but the written form would have been just awful, right?

Tom V

@dezso Not sure what gefangen would translate to to make it sound English :)

dezso

@TomV catched :D

well, reading Dutch text with a notion it is German has its, well, catches

Lamak

@JackDouglas homeschooling?, sounds hard

Tom V

@dezso When I read German I understand almost everything, when I write or speak German I get every grammatische fall wrong :)

and the caps too

dezso

7:45 PM

@TomV my Dutch colleague is telling the same

Zane

Yikes. Just found a table that's set to grow at mb

Tom V

@PaulWhite the bounty on the AWS/SSIS question I don't really understand. It's borderline unclear to me what the requirements about the end result are

Not that I know anything about AWS so I'm not commenting or voting

but "straight SQL or a different ETL (if I knew what would work on AWS) depending on your requirements" seems about it for an answer with the given information

Jack Douglas

8:17 PM

@Lamak not sure we would have started if we'd realised, but I'm too stubborn to give up now :S

swasheck

@JackDouglas we're adopting and homeschool looks like the only truly viable solution for at least the first year. hopefully i can learn from you :)

Lamak

@swasheck just curious, why does it seem like the only solution?

also, you are adopting?, congrats!

Jack Douglas

@swasheck adopting! wow

Tom V

@Lamak Yeah I was thinking the same. Are schools that bad that homeschooling is a better option. It seems like a terrible cost-reward deal.

Nobody does that over here

swasheck

@Lamak thanks. first reason is district requirements. the child wont officially be "ours" until after they've been in our home for a while and the school district is ... odd about things like that. second - we could go private but it's both expensive and challenging, and mild learning disabilities tend to get lost in the middle

third - language barrier will make it difficult to jump right into public schools

Lamak

8:22 PM

@swasheck ah, I see

swasheck

@TomV schools are pretty good. actually may be too rigorous considering the circumstances

Lamak

@TomV I don't think that's even allowed here, unless very special circumstances

Tom V

@swasheck I can see the adoption difficulties with my uncle being foreign in the states with a mexican wife and kids

But in general

@Lamak Over here you can do what you want as long as you meet the final terms (exams) at the end of every year

Lamak

@TomV yeah, don't worry, I was talking out of my ass apparently. You can do it here too if you meet some criteria and do the exams

swasheck

@Lamak taking after @billinkc i see?

Lamak

8:31 PM

@swasheck sigh that's depressing

Zane

it's 4 100 GB files btw

James Lupolt

@swasheck Congrats on the new family member

Would you guys say that 'draw a picture of the execution plan you'd expect given this query and this schema' is easy or hard compared to the job interview questions you're used to seeing?

Lamak

@JamesLupolt hard

James Lupolt

I drew a picture of Trump boarding Air Force One and was asked to leave. I guess that was the wrong answer

swasheck

@JamesLupolt i'd have more followup questions, honestly

Tom V

8:40 PM

@JamesLupolt I'd reply with "what's the histogram, selectivity, table definition and estimated number of rows then?", that should get you off the hook for a while and you could force them into admitting their answer isn't always right either

swasheck

@TomV exactly

James Lupolt

@TomV The interviewer did a pretty good job of asking the question in this case, so that info was given (at some level of detail).

I guess I was just a little surprised, because it's a good question but not one I've seen before.

Tom V

but I can see how a drawing of Trump boarding Air Force One can limit your career options :)

James Lupolt

Heh

I actually got it pretty close to the plan that SQL Server produced IRL. Forgot a 'gather streams' iterator

Tom V

During election time political opinions are best kept to one self

Lamak

8:46 PM

@TomV darn, I was just about to talk about michelle bachelet

James Lupolt

Didn't know Chile had a socialist president tbh

Lamak

yeah, that went well

James Lupolt

First since Allende?

Tom V

Doesn't a lot of South-America have a socialist government? (too lazy to look it up)

Lamak

@TomV yeah, but she is "socialist"

Tom V

8:50 PM

But socialism depends on context, I bet Belgium or France socialism would be considered extreme left-wing in the States

Lamak

@TomV doesn't every country seems like that in comparison to the US ;-)

sorry, sorry, totally joking

Tom V

@Lamak Not sure, the Balkan in the EU and maybe Russia seem to have pretty right-wing opinions too, and I'm not expressing any moral valuation on that opinion

Lamak

yeah, I know, I'm not serious

Tom V

I think there is an in-between and as with everything a big "it depends" to everything, but I think that's somewhat lost in the US two party system

But then again, I must remind myself not to get into political discussions :)

That just strikes a chord with me, geopolitics and macroeconomics are my second hobby

swasheck

9:07 PM

@TomV what's your first hobby?

Tom V

@swasheck Strange thing to say maybe but what I'm paid for

Phil

Cassetteboy vs Donald Trump: youtube.com/watch?v=OgUAQ4ZGtgk (NSFW)

Tom V

@swasheck Solve puzzles and learn stuff, and see the world on company expenses :)

swasheck

@TomV sounds fantastic

Phil

9:32 PM

"’m part of the local UK MySQL team and based on your profile I thought you might be interested in the upcoming MySQL tech seminar we’re organizing in Manchester" -- LULZ. Nope!

swasheck

9:52 PM

@Phil shortest keynote ever ... "migrate to postgres"

Phil

10:08 PM

obviously just saw "Oracle DBA" and got confused

swasheck

Oracle and MySQL are easily conflated

Transcript for

The Heap™ – Consultancy ©®