The Heap™ – Consultancy ©®

swasheck

02:21

@MaxVernon good. you?

Max Vernon

pretty well, thanks. Covid lockdown makes everything way more boring than normal but what are you gonna do.

swasheck

02:37

stay inside. if you're still in winnipeg that's all you were going to do anyway

Tom V

06:42

Morning

Peter Vandivier

07:01

Using that syntax changes the query plan from an 11-step plan including an multi-index-or to an one-step plan using solely the primary key. I just tested this on a read-only snapshot of the database (~1.2TB, 2.3 billion tuples) and the result is basically ready at once. Well done. — std_unordered_map 18 hours ago

i guess that's how? 2.3 billion > 100 million. but even at 32 bytes per row that's still only 68-ish gb of user data. at 100 million rows on my local there was about 60% user data / 40% metadata overhead but maybe it drops off exponentially at some point 🤷‍♂️

or maybe the compression is extra bad if the data's grown organically rather than in a demo

John aka hot2use

07:51

Monring

Tom V

Moonring

nutty about natty

10:40

hi there

quick question to the floor:

with docs.oracle.com/cd/B19306_01/server.102/b14237/… I know how to query the names of all tables and columns

any idea how to go "one level deeper" and also retrieve the first few "rows" / values of the tables?

happy to post a proper question...

this is close, but not there yet:

3

Q: Select from all_tab_columns where table has > 0 rows

I need to search in a large DB a table that matches with a column name, but this table must have more than 0 rows. Here is the query by the way: SELECT * FROM all_tab_columns WHERE column_name LIKE '%ID_SUPPORT%';

I'd like the actual values, if possible

this also looks close:

0

Q: output data from all_tab_columns

i have a table test with many filler columns whose values are actually the column names from some tables in the database. i want to frame a query which returns the owner,table_name,column_name for all the filler columns DROP TABLE TEST; create table test (cat varchar2(10) , filler_1 varchar2(10...

oracle plsql oracle12c

2

Q: Oracle/SQL Sample of data from each column from each table - too much?

I am attempting to grab a sample of data from every column in every table in about 5 schemas. Below is an example of my getting this data from just 1 schema (replace "sde" with whatever your schema is to run). This piece runs fine: select CASE when lead(ROWNUM) over(order by ROWNUM) is null t...

sql oracle toad

seems very complicated... ;(

ypercubeᵀᴹ

11:17

@PeterVandivier in a comment they mention "2.3 bn tuples"

Using that syntax changes the query plan from an 11-step plan including an multi-index-or to an one-step plan using solely the primary key. I just tested this on a read-only snapshot of the database (~1.2TB, 2.3 billion tuples) and the result is basically ready at once. Well done. — std_unordered_map 22 hours ago

nutty about natty

11:33

another question: is there an easy way to get the output tables of multiple queries side-by-side, without any join operation?

this question is related:

10

Q: sql merge tables side-by-side with nothing in common

I'm looking for an sql answer on how to merge two tables without anything in common. So let's say you have these two tables without anything in common: Guys Girls id name id name --- ------ ---- ------ 1 abraham 5 sarah 2 isaak 6 ...

sql

but the answers not quite satisfying yet

would like to just "append" the columns of each query "horizontally"

shouldn't require any join, as the tables have nothing to do with each other

Peter Vandivier

11:47

@ypercubeᵀᴹ yea, i caught that, my remaining confusion is that the worst-case storage size for sqlite integer is 8 bytes, so the max row size is 32 bytes. 32 bytes * 2.3 billion tuples = approx. 68.5 gb. obviously there's storage overhead but... 1,500% data size seems excessive

ypercubeᵀᴹ

true

unless they mean "billion" the old way, as "million millions"

@MaxVernon should know more. He has answered this one

4

Q: How large is SQLites minimum data storage overhead and what generates them?

Motivation: In my company there exists the (probably strange) idea to store pure time series data in a normal SQL table (instead of using BLOBs or just binary files which would probably be a better idea). I have to prove this is a misconception (at least for our embedded device where some kind o...

sqlite database-internals metadata blob database-size

@PeterVandivier I think the most probable reason for 2.3 billion rows using more thna 1TB is the "like" here:

> I have a sqlite table called tuples defined like ...

if the table has a few more columns, 68 GB to 1 TB is plausible

Peter Vandivier

ahhhhh... yea that makes sense

so wise, you are

John aka hot2use

egregious amount of comments:

Indices decreases the performance during any data altering operation which affects the index expression value. This is obvious... — Akina 2 hours ago

bbaird

14:34

This reads like a log of "Today in SQLite is bad"

Morning

Me: Spends a lot of time producing a thoughtful edit, including rewording and formatting. Other user: rushes in, does bare minimum before I can finish typing.

To be fair, it's a fine edit, just one of those annoyances.

John aka hot2use

14:50

Welcome to the community. :-D

If your edit is more substantial, then you can just continue and save it.

Max Vernon

@ypercubeᵀᴹ I have zero recollection of that. It's like reading someone else's answer to me. Although I would say, there are some good bits of advice in there :-)

Josh Darnell

15:07

Is the main site down for everyone?

bbaird

@Johnakahot2use I found out the edit was made after I finished mine. We'll see.

Well, after I began my edit but before I finished it.

@JoshDarnell Seems to be? I was in the middle of an answer and... poof

John aka hot2use

up for me

but there have been major ripples in the internet tody.

Messenger and Facebook with erratic behavior for me.

isitdownrightnow.com/dba.stackexchange.com.html

Josh Darnell

Yeah it's back up now.

@Taryn was probably rebuilding indexes.

twitter.com/Nick_Craver/status/1337051539467333633

@Johnakahot2use 🙄

Taryn

@JoshDarnell running an update in Production

John aka hot2use

@Taryn :-O

don't we all?

Max Vernon

15:20

@Johnakahot2use thanks, that's been taken care of 😊

Josh Darnell

@Taryn #hugops

Glad you got it sorted quickly. I hope it's clear I was just kidding with the index rebuild thing 😃

McNets

@Johnakahot2use is there another way?

kabuto178

15:54

@mornin

swasheck

16:10

@Taryn unbatched, obviously

Max Vernon

@swasheck with no where clause

@kabuto178 morning!

kabuto178

@MaxVernon all is well?

Max Vernon

pretty much. We're in total lockdown so everything is boring.

you?

kabuto178

lol, I can understand. Which country is that if you don't mind sharing?

I am on shift at my day job so i am at work now, heading home in couple hours. Continuing my journey on database mastery (slow and steady)

Max Vernon

16:49

@kabuto178 I'm in The Great White North, aka Canada.

Great White North: Back-Bacon and Long Underwear

►

kabuto178

:D

Cade Roux

17:38

Alrighty, I am back to debugging this issue where one of our SQL Server 2016 instances takes 45s to do 1000 INSERTs while the exact same script on SQL Server 2012 takes 1s. I have slimmed it down from the original case (taking an hour to do 5000 rows). It's now a empty DB with just one function that one of the constraints uses and the table. It takes 1s on my SQL Server 2016.

swasheck

19:51

sounds fun

Max Vernon

20:08

this is too funny.

nutty about natty

20:25

not everyone at once :(

Max Vernon

@nuttyaboutnatty if you have a question, you probably should post it on Database Administrators

swasheck

20:48

wait. what?

John aka hot2use

21:05

@McNets I don't think so

swasheck

this is the way

Max Vernon

21:16

The ~~Mandalorian~~ DBA way

Transcript for

The Heap™ – Consultancy ©®