If we want to analyse execution plans on the SQL console, we probably need to find a SQL_ID first, which we can the pass to the DBMS_XPLAN.DISPLAY_CURSOR function. One way to find this SQL_ID is by querying the v$sql table first, e.g.: SELECT last_active_time, sql_id, child_number, sql_text FROM v$sql WHERE upper(sql_fulltext) LIKE '%SOME_SQL_TEXT%' ORDER BY … Continue reading Oracle Tip: v$sql Table or View does not Exist
Tag: sql
jOOQ Newsletter: May 21, 2014 – jOOQ Community Video Tutorials
Subscribe to this newsletter here Tweet of the Day Our customers, users, and followers are sharing their love for jOOQ with the world. Here are: Chris Martin who has switched jobs and is now really missing jOOQ. Too bad we can't offer jOOQ for Python, Chris! https://twitter.com/chris__martin/status/463170624408793088 Moutaz Salem who had been waiting for jOOQ for all … Continue reading jOOQ Newsletter: May 21, 2014 – jOOQ Community Video Tutorials
SQL Server Trick: Circumvent Missing ORDER BY Clause
SQL Server is known to have a very strict interpretation of the SQL standard. For instance, the following expressions or statements are not possible in SQL Server: -- Get arbitrarily numbered row_numbers SELECT ROW_NUMBER() OVER () -- Skip arbitrary rows SELECT a FROM (VALUES (1), (2), (3), (4)) t(a) OFFSET 3 ROWS Strictly speaking, that … Continue reading SQL Server Trick: Circumvent Missing ORDER BY Clause
The Index You’ve Added is Useless. Why?
Recently, at the office: Bob: I've looked into that slow query you've told me about yesterday, Alice. I've added the indexes you wanted. Everything should be fine now Alice: Thanks Bob. I'll quickly check ... Nope Bob, still slow, it didn't seem to work Bob: You're right Alice! It looks like Oracle isn't picking up … Continue reading The Index You’ve Added is Useless. Why?
How to Implement Sort Indirection in SQL
I've recently stumbled upon this interesting Stack Overflow question, where the user essentially wanted to ensure that resulting records are delivered in a well-defined order. They wrote SELECT name FROM product WHERE name IN ('CE367FAACDHCANPH-151556', 'CE367FAACEX9ANPH-153877', 'NI564FAACJSFANPH-162605', 'GE526OTACCD3ANPH-149839') They got CE367FAACDHCANPH-151556 CE367FAACEX9ANPH-153877 GE526OTACCD3ANPH-149839 NI564FAACJSFANPH-162605 They wanted CE367FAACDHCANPH-151556 CE367FAACEX9ANPH-153877 NI564FAACJSFANPH-162605 GE526OTACCD3ANPH-149839 Very often, according to your … Continue reading How to Implement Sort Indirection in SQL
jOOQ Newsletter: April 30, 2014
Subscribe to this newsletter here Tweet of the Day Our customers, users, and followers are sharing their love for jOOQ to the world. Here are: Santiago M. Mola who appreciates jOOQ's affinity to SQL features and its correctness https://twitter.com/mola_io/status/456800912917270528 Vlad Mihalcea who's reading the jOOQ docs to learn about SQL https://twitter.com/vlad_mihalcea/status/457055965171687424 Thanks for the shouts, guys! It … Continue reading jOOQ Newsletter: April 30, 2014
NoSQL? No, SQL! – How to Calculate Running Totals
If anything at all, our jOOQ talks at various JUGs and conferences have revealed mostly one thing: Java developers don't know SQL. And it isn't even necessarily our fault. We're just not exposed to SQL nowadays. But consider this: We developers (or our customers) are paying millions of dollars every year to Oracle, Microsoft, IBM, … Continue reading NoSQL? No, SQL! – How to Calculate Running Totals
jOOQ Newsletter: April 16, 2014 – Monthly, Yearly, Perpetual licenses now available
Subscribe to this newsletter here Tweet of the Day Our customers, users, and followers are sharing their love for jOOQ to the world. Here are: Mahmud who cannot wait to make more magic with jOOQ. https://twitter.com/bigthingist/status/455985890125287424 Peter Kopfler who, after hearing about jOOQ and SQL in Vienna is thrilled to take a deep dive into the … Continue reading jOOQ Newsletter: April 16, 2014 – Monthly, Yearly, Perpetual licenses now available
How can I do This? – With SQL of Course!
Haven't we all been wondering: How can I do this? I have these data in Excel and I want to group / sort / assign / combine ... While you could probably pull up a Visual Basic script doing the work or export the data to Java or any other procedural language of choice, why … Continue reading How can I do This? – With SQL of Course!
CUME_DIST(), a Lesser-Known SQL Gem
When doing reporting or statistics with SQL, you better know your window functions. There are many of them, and few SQL developers know about them. CUME_DIST() is one such function. We've recently re-discovered it on Stack Overflow. The following query yields two times the same result for fraction1 and fraction2: SELECT ename, CUME_DIST() OVER (ORDER … Continue reading CUME_DIST(), a Lesser-Known SQL Gem