The Heap™ – Consultancy ©®

Phil

00:24

@ConcernedOfTunbridgeWells I'm a Forest fan

and hardcore drunkard ;P

ConcernedOfTunbridgeWells

@Phil Jolly good. Should come to our next shindig.

Mark Storey-Smith

@Phil You'd be in good company

Simon Righarts

@Phil I thought hardcore drunkard went automatically with being a Forest fan?

Jack Douglas

09:31

@Mark, @Concerned, thanks for the excellent banter yesterday and for the links - most amusing, all the more so for being far too true

ConcernedOfTunbridgeWells

@JackDouglas You're welcome.

ypercube

10:03

good for dba.se?

1

Q: How to implement a catalogue for meta-data and automating SQL in a database?

I have read here the discussions on 5NF, EAV and 6NF and the need for a catalogue to handle meta-data and the complex SQL "automatically". How is that implemented in practice?

Jack Douglas

@ypercube I have no idea what he is asking, do you?

Simon Righarts

That question doesn't make any sense at all to me. I know all the words, but what he's trying to get across is lost on me

ypercube

I suspect he has been reading PerformanceDBA's posts, like this one: stackoverflow.com/questions/4011956/…

>
> Of course, if you go with EAV, I am recommending more formality; buy the full quid; **go with 6NF**; **implement a catalogue**; utilities to produce SQL; Views; handle Missing Data consistently; eliminate Nulls altogether. This reduces your vulnerability to the quality of your developers; they can forget about the EAV/6NF esoteric issuses, use Views, and concentrate on the app logic.

Simon Righarts

unless he means something about metadata for EAV (which attributes are allowed which values, constraints on values, etc)

Jack Douglas

10:22

Where/How. Well, I did all the above, and I am very grateful for the great teachers I did have. I ameliorate about four databases per year, for large banks, it is my passion/profession. If I say more here, it would be immodest. If you're interested in more detail, chase me down: profile⇢website⇢email. I would be free to answer your question. Cheers. — PerformanceDBA Oct 26 '10 at 9:29

ameliorate?

is this guy for real?

it's now +24 -4

Simon Righarts

I'm wondering the same thing, that guy uses a lot of words to say not a lot, then it gets crazy

Simon Righarts

10:40

Actually, he reminds me of the slightly-senile lecturer at university that obviously knows what you need to know but can't communicate it clearly and succinctly

Case in point: stackoverflow.com/questions/5279984/…

(Check the comments for extra hilarity)

ypercube

I guess many of the downvotes were the result of the heated comments-discussion.

Jack Douglas

SQLite, Oracle. Sybase's SQL Anywhere also supports DEFERRED constraints, but apparently Transact-SQL doesn't. SQL Server 2008 also doesn't support deferred constraints, but they seem to be late to the party anyway: before SQL Server 2008 you apparently had to write triggers to enforce referential integrity at all. An ON COMMIT trigger is therefore the correct answer. — geekosaur Mar 12 '11 at 1:41

wow

Phil

Yawn

Big day of beer consumption ahead

Prepares liver

ypercube

@Jack: What? No FKs before 2008? wow...

Jack Douglas

Hey Phil

@Thomas. By "recent" I meant long after the established standard (89) that all vendors met. The enterprise SQL vendors have not implemented it, and have no plans to do so, because it has been quite unnecessary for 25 years, and leads to the kind of insanity discussed in this thread. We are getting off on a tangent of a tangent. My initial point, unchanged, is that DCC and jumping through hoops is completely unnecessary: full identified integrity (as per model) can be implemented if you have standard ISO/IEC/ANSI SQL (regardless of 89, 92, DCC, etc). — PerformanceDBA Mar 22 '11 at 10:27

apparently oracle is not an 'enterprise' SQL vendor

this guy is a troll

Phil

10:51

He's a troll, but doesn't realise

Jack Douglas

@Phil I think he know it

have you read his profile?

> Past Boy Scout Leader, now NSW Rural Fireman (Volunteer), Endurance rider (yes, that is my Endurance Arabian, shot with my Canon EOS 5; she has been with me for 20 years; stopped eventing with her 5 years ago), NSW A Grade competition shooter, amateur photographer, Salsa, Mensan.

I have a life. I ride horses, hunt deer, chase women, harmonise databases; in no particular order; and ensure that I don't mix them up.

I am not macho, I work well with adults, sub-adults and older children, but small children who have not left their mother's breast find me a bit scary.

If he sets foot on dba.se please please flag him

Phil

:D

Simon Righarts

@ypercube I'm pretty sure someone doesn't know what they're talking about

Jack Douglas

11:31

@SimonRigharts I'm assuming @ypercube is being ironic there :-)

Ok who wants to mess with Oracles head (on your dev db only please):

select id, (select count(distinct column_value) from table(ids))
from (select id, collect(id) over (order by id) as ids from (select to_number(trunc(level/4)) as id from dual connect by level<20));

Phil

INSERT INTO DUAL VALUES ( 'XXXX');

Jack Douglas

I think I need to open a tar

Simon Righarts

I can't follow that SQL but I'm going to guess you get eleventy bajillion results right before your Oracle instance locks up?

Jack Douglas

@SimonRigharts not quite that bad but you do get ORA-03113: end-of-file on communication channel

so it is (probably) only your session that gets the chop

Phil

@JackDouglas does it on my 11.2.0.2 too

Jack Douglas

11:37

select id, (select count(distinct column_value) from table(ids)) from (select id, collect(id) over (order by id) as ids from (select 1 id from dual union all select 1 from dual union all select 1 from dual));

is happy

select id, (select count(distinct column_value) from table(ids)) from (select id, collect(id) over (order by id) as ids from (select 1 id from dual union all select 1 from dual union all select 2 from dual));

is unhappy

Phil

Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x46B83BB, __intel_new_memcpy()+3579] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/phil112/PHIL112/trace/PHIL112_ora_17866.trc (incident=12169):
ORA-07445: exception encountered: core dump [__intel_new_memcpy()+3579] [SIGSEGV] [ADDR:0x0] [PC:0x46B83BB] [SI_KERNEL(general_protection)] []
Incident details in: /u01/app/oracle/diag/rdbms/phil112/PHIL112/incident/incdir_12169/PHIL112_ora_17866_i12169.trc

@JackDouglas I suspect if you open a tar, the support person will have a brain coredump ;)

Jack Douglas

@Phil I expect they will fob me off with a useless workaround ;-)

but we can hope for something more exciting like a brain dump

we are fully patched so it ain't that

.

SR raised, but I won't hold my breath :-)

Phil

11:55

Hehe

Oracle support is shockingly bad, especially considering how much it costs

3

ypercube

13:12

"I ride horses, hunt deer, chase women, harmonise databases;" sounds like Robin Hood's CV.

(after marriage. Having settled, working for the bank industry)

Phil

13:42

1

Q: Sub-query executes 1 time for a parent query or more?

If i apply a static data in where clause is it faster then applying a sub-query. Example: (The query returns 5 records as a result set) SELECT STARTDATE, ENDDATE FROM TEST WHERE STARTDATE = '2012-08-21'; is faster then : SELECT STARTDATE, ENDDATE FROM TEST WHERE STARTDATE = (SELECT STARTDATE...

mysql sql

Interesting answer

I guess if MySQL was cleverer, and SLEEP(1) was deterministic, it wouldn't work that way

Crikey, the road just turned into a river.

flickr.com/photos/trollied/7856913546/in/photostream

The heavens properly opened

ypercube

14:01

I think I just killed SQL-Fiddle

Aaron Bertrand

14:21

Let's create a new 3-row table for every lookup type we have, because it's more efficient and easier to manage

-1

A: Data modeling for Same tables with same columns

Keep your lookup tables separate. It's faster at lookup time, and you will do millions of lookups between times when you add a new lookup table. A lot of tables is not a big problem.

Jack Douglas

18:20

@aaron are you serious? I can think of a lot of reasons for wanting those lookup tables kept separate.

Mikael Eriksson

20:42

OTLT

Transcript for

The Heap™ – Consultancy ©®