@0xweathered

sql | dev | automatic partitions for the lazy kind - automatic list partitioning

Oct 16, 2021 · 3 min read · nah, using sample schema is way easier actually, lets go to HR lets say i want to make a stripped down table identical to the EMPLOYEES table, paritioned, by DEPARTMENT_ID column. so if i am using the HR.EMPLOYEES table, SELECT COUNT(DISTINCT DEPAR...

sql | how to avoid multiple identical subqueries in the SELECT clause

Oct 16, 2021 · 2 min read · continuing from sql | how NOT to avoid multiple identical subqueries in the SELECT clause these won't work if your db is older than 12c. if it is lower, i guess the how NOT to is the how to, not an expert don't quote me on that. and no i don't want ...

sql | how NOT to avoid multiple identical subqueries in the SELECT clause

Oct 15, 2021 · 1 min read · running 12c or higher? how to inline subqueries in SELECT clauses, or simply scalar subqueries, as you can guess by the name, is used to return a single value. but what if, i am having to query a specific table, writing identical subqueries to fet...

dba | ORA-01000: maximum open cursors exceeded

Oct 15, 2021 · 1 min read · not the best place for explanations here, the below query shows the number of open cursors present in a session. if the number reaches the db parameterOPEN_CURSORS, ORA-01000 is thrown. OPEN_CURSORS := number of cursors each session can have open, pe...

sql tuning | calculating logical reads made by a query

Oct 15, 2021 · 1 min read · one day a dba reported high resource usage by a query, but it was 'well-optimized' and the runtime was as expected, then what was wrong? logical read was high. to calculate logical reads made by a query: DECLARE READS NUMBER; OUTPUT NUMBER; ...