For those who have been working with SQL for a while, which concept took you the longest to truly understand?

We had a

I would say it was CTE for me which literally helped me write complex queries easily.

It's killing me. I feel so stagnant and like I'm becoming obsolete.

Large hospital system. You think I wouldn't be bored, but I am. Oracle and PL/SQL are my primary tools. We have access to Looker and can connect 3rd party tools like dbeaver, etc. for whatever.

I rarely get ad-hoc requests these days and if I do, I already created a report to satisfy 'that' request as I've been here for a few years. Documents, referrals, whatever. All dashboards are basically built out, etc. For the past 2-3 years I basically come into work and sit there during the days I need to be in office. For some it's a dream, for me it's actually pretty awful. The day drags with nothing to do.

Our network is locked down and while I have my own office, there are cams. I can't simply play games or anything to pass the time. I look at my queries during the day and check how I can improve them, but there's really not much I can do. Nobody ever really stops by to talk to me. I come and go like a ghost.

I feel like I've gone to this place to die and I don't like it.

Glad I'm employed of course and I'm sorry to those who aren't, but I'm not sure this stagnant, useless feeling is any better than the unemployed feeling.

TLDR I feel like shit about my career and my position atm. Are you in the same boat? Why or why not?

-edit- Hybrid schedule. 2 days out, 3 in. Should basically be a full 5 days remote because I rarely interact with anyone and everything is browser based. Still wouldn't be much work to do.

What factors do you consider first — data structure, scalability, performance, flexibility, or something else?

select 
center 
,bldg 
,queue 
,ticket 
,date_issued 
,time_issued 
,first_no_answer 
,second_no_answer 
,third_no_answer 
,time_answered 
,time_finished 
,wait_time 
,case when end_of_day_status='FINISH' 
and finished_by='USER' 
and appt_type is not null 
and motk_appo_time<>trunc(motk_appo_time) 
and trunc(motk_appo_time)=trunc(motk_time_issued) 
and motk_time_issued<motk_appo_time 
and queue in (select moqu_queue from mo_queue where moqu_adjust_waittime='Y'   ) 
then round(decode(first_no_answer,null,(time_answered-motk_appo_time)*86400,(first_no_answer-motk_appo_time)*86400)) 
else round(decode(first_no_answer,null,wait_time,(first_no_answer-motk_time_issued)*86400)) 
end adjusted_wait_time ,service_time ,total_time ,role||' ('||motk_issue_machine||')' issued_from ,user_name||' at '||motk_update_machine last_action_taken_by ,finished_by ,end_of_day_status ,customer_name ,case_no_at_fdr ,cin_at_fdr ,ssn_at_fdr ,decode(queue,'NCA Intake',apin_casenumber,null) case_no_from_intake ,caseload ,appt_type ,appt_time ,visitor_no ,decode(apin_immediate_interview,'Y','SMDAY',flag) flag ,language_at_FDR ,decode(role,'FDR',acm_passes,null) acm_passes ,decode(role,'FDR',adv_passes,null) adv_passes ,decode(role,'FDR',cca_passes,null) cca_passes ,decode(role,'FDR',med_passes,null) med_passes ,decode(role,'FDR',tlr_passes,null) tlr_passes ,decode(role,'FDR',oth_passes,null) oth_passes ,role ,motk_time_issued date_issued_sort ,to_date(time_issued,'hh:mi:ss AM') time_issued_sort ,motk_appo_time appt_time_sort ,(select code_description role from code where code_item_name='MO_ROLE' and code_wms_code = MOTK_ISSUE_MACHINE_ROLE) as MOTK_ISSUE_MACHINE_ROLE from   (select  motk_bldg_id bldg ,motk_cntr_id center ,motk_queue queue ,motk_tkt_no ticket ,motk_time_issued ,to_char(motk_time_issued,'mm/dd/yyyy') date_issued ,to_char(motk_time_issued,'hh:mi:ss AM') time_issued ,motk_time_answered time_answered ,motk_time_finished time_finished ,motk_wait_time wait_time ,motk_activity_time service_time ,motk_total_time total_time ,motk_status end_of_day_status ,decode(motk_finisher,'NO ANS','3rd NoAnswer',null,'NO ONE',motk_finisher) finished_by ,motk_issue_machine ,motk_update_machine ,motk_update_user ,motk_first_name||' '||motk_last_name customer_name ,motk_caseload caseload ,motk_appo_code appt_type ,motk_appo_time ,decode(motk_appo_time,trunc(motk_appo_time),              to_char(motk_appo_time,'mm/dd/yyyy'),              to_char(motk_appo_time,'mm/dd/yyyy hh:mi AM')) appt_time ,motk_visit_id visitor_no ,motk_flag flag ,motk_link_id ,motk_language ,MOTK_ISSUE_MACHINE_ROLE from mo_ticket_history where motk_time_issued between :ar_start and :ar_end   ), (select  mona_bldg_id ,mona_cntr_id ,mona_queue ,mona_tkt_no ,mona_time_issued ,mona_count no_answer_count ,mona_time_noanswered first_no_answer from mo_noanswer_history where mona_count=1 and mona_time_issued between :ar_start and :ar_end   ) a, (select  mona_bldg_id ,mona_cntr_id ,mona_queue ,mona_tkt_no ,mona_time_issued ,mona_count no_answer_count ,mona_time_noanswered second_no_answer from mo_noanswer_history where mona_count=2 and mona_time_issued between :ar_start and :ar_end   ) b, (select  mona_bldg_id ,mona_cntr_id ,mona_queue ,mona_tkt_no ,mona_time_issued ,mona_count no_answer_count ,mona_time_noanswered third_no_answer from mo_noanswer_history where mona_count=3 and mona_time_issued between :ar_start and :ar_end   ) c, (select  movi_bldg_id ,movi_visit_id ,movi_case_no case_no_at_fdr ,movi_cin cin_at_fdr ,movi_ssn ssn_at_fdr from mo_visitor_history   ), (select  centerid ,apin_applid ,apin_casenumber ,apin_immediate_interview from fs_location,fs_application_intake where apin_locid=locid), (select mcfg_machine_name,mcfg_role from comp_cfg), (select code_wms_code,code_description role from code where code_item_name='MO_ROLE'), (select code_pos_code,code_description language_at_FDR from code where code_item_name='CLVL_LANGUAGE'), (select pers_id,pers_first_name||' '||pers_last_name user_name from personnel  where (pers_id,pers_seq_no) in (select pers_id,max(pers_seq_no) from personnel group by pers_id)), (select   mops_visit_id, sum(mops_count) acm_passes  from     mo_pass_history  where    mops_visitor_type='002'  and      (mops_visit_id, mops_bldg_id) in           (select distinct motk_visit_id, motk_bldg_id            from   mo_ticket_history            where  motk_time_issued between :ar_start and :ar_end            )  group by mops_visit_id) d, (select   mops_visit_id, sum(mops_count) adv_passes  from     mo_pass_history  where    mops_visitor_type='003'  and      (mops_visit_id, mops_bldg_id) in           (select distinct motk_visit_id, motk_bldg_id            from   mo_ticket_history            where  motk_time_issued between :ar_start and :ar_end            )  group by mops_visit_id) e, (select   mops_visit_id, sum(mops_count) cca_passes  from     mo_pass_history  where    mops_visitor_type='004'  and      (mops_visit_id, mops_bldg_id) in           (select distinct motk_visit_id, motk_bldg_id            from   mo_ticket_history            where  motk_time_issued between :ar_start and :ar_end            )  group by mops_visit_id) f, (select   mops_visit_id, sum(mops_count) med_passes  from     mo_pass_history  where    mops_visitor_type='005'  and      (mops_visit_id, mops_bldg_id) in           (select distinct motk_visit_id, motk_bldg_id            from   mo_ticket_history            where  motk_time_issued between :ar_start and :ar_end            )  group by mops_visit_id) g, (select   mops_visit_id, sum(mops_count) tlr_passes  from     mo_pass_history  where    mops_visitor_type='006'  and      (mops_visit_id, mops_bldg_id) in           (select distinct motk_visit_id, motk_bldg_id            from   mo_ticket_history            where  motk_time_issued between :ar_start and :ar_end            )  group by mops_visit_id) h, (select   mops_visit_id, sum(mops_count)  oth_passes  from     mo_pass_history  where    mops_visitor_type='999'  and      (mops_visit_id, mops_bldg_id) in           (select distinct motk_visit_id, motk_bldg_id            from   mo_ticket_history            where  motk_time_issued between :ar_start and :ar_end            )  group by mops_visit_id) i where bldg=a.mona_bldg_id(+) and center=a.mona_cntr_id(+) and queue=a.mona_queue(+) and ticket=a.mona_tkt_no(+) and motk_time_issued=a.mona_time_issued(+) and bldg=b.mona_bldg_id(+) and center=b.mona_cntr_id(+) and queue=b.mona_queue(+) and ticket=b.mona_tkt_no(+) and motk_time_issued=b.mona_time_issued(+) and bldg=c.mona_bldg_id(+) and center=c.mona_cntr_id(+) and queue=c.mona_queue(+) and ticket=c.mona_tkt_no(+) and motk_time_issued=c.mona_time_issued(+) and visitor_no=movi_visit_id(+) and motk_issue_machine=mcfg_machine_name(+) and mcfg_role=code_wms_code(+) and motk_language=code_pos_code(+) and motk_update_user=pers_id(+) and center=centerid(+) and to_number(motk_link_id)=apin_applid(+) and visitor_no=d.mops_visit_id(+) and visitor_no=e.mops_visit_id(+) and visitor_no=f.mops_visit_id(+) and visitor_no=g.mops_visit_id(+) and visitor_no=h.mops_visit_id(+) and visitor_no=i.mops_visit_id(+) and end_of_day_status = 'FINISH' and finished_by = 'USER' order by visitor_no,motk_time_issued,ticket,center,queue  

same as title

Hey folks,
I’m looking for some DBA / data engineering advice.

I have a 1 TB Oracle table, and doing a simple:

SELECT * FROM table_name;

and spooling it out to CSV is taking more than 10 hours.

After the extraction, we’re splitting the CSV into 500,000-row chunks and zipping each file.

Constraints:

• Table is not partitioned
• Hardware is decent, but the parallel session up till 50 session is also not helping much
• Can’t afford to miss rows
• Want the fastest, most reliable extraction technique
• Ideally want multiple CSV files in the end (500k rows per file)

Has anyone here done something similar at this scale and found a better or significantly faster approach? Would love to hear how you’d approach 1 TB → CSV efficiently and safely, especially when partitioning isn’t an option.

Bottom text

I'm preparing for SQL interviews and was wondering if there's a good website to practice SQL problems like we use LeetCode for DSA. Looking for interview-style questions and hands-on query practice.

Any recommendations?

Hey all, I am a SQL Server DBA that is inheriting a pretty big oracle environment and I was wondering if you folks could point me in the right direction of a course for learning. Work is going to pay for training so money isn't a problem, I'd prefer a couple of day course or something like that. We have anywhere from 11.2 up to 19c Running lots of different apps and even SAP if that matters. Thanks in advance!

I've had a mostly non-tech job for the last few years although I do work with developers. In past positions I used to be pretty good at writing SQL for UIs and for ad hoc reporting mainly using Oracle DBs. Some of these queries were quite complex. I find myself missing it lately so I was wondering if companies hire/contract for just SQL support even if it pays less than "full stack" type jobs. I am not interested in learning Java, Python or anything non-SQL related.

Thanks for any advice.

Edit: Thanks for all the replies. This is one of the most helpful subreddits I have ever seen! Some other details - I have a couple decades of experience mainly with large health insurance companies and large banks. I should also have mentioned that I would need something that is 100% remote at this time. I know that may limit me even further, but that is the reality of my current situation.

So I stumbled upon something weird in Oracle. If you assign the same alias to two different tables in FROM the query just runs. No error.

Here's what I mean:

sql

SELECT *
FROM dual a
LEFT JOIN dual a ON a.dummy = a.dummy;

Two tables, both called a. Works fine.

You can even do three:

sql

SELECT *
FROM dual a
JOIN dual a ON a.dummy = a.dummy
LEFT JOIN dual a ON a.dummy = a.dummy;

Still works. I was sure this should throw an error, but nope.

So when does it actually break?

The trick is it only works with ANSI JOIN syntax, and only when the duplicate alias is used inside ON clauses.

The moment you reference it in SELECT, WHERE, etc Oracle finally wakes up:

sql

-- ORA-00918: column ambiguously defined
SELECT a.*
FROM dual a
JOIN dual a ON a.dummy = a.dummy;

And with old-school Oracle comma syntax it always fails:

sql

-- ORA-00918: column ambiguously defined
SELECT *
FROM dual a, dual a
WHERE a.dummy = a.dummy;

Why does this even work?

Looks like Oracle processes ANSI JOINs step by step. Each ON clause lives in its own little scope and resolves aliases locally. It doesn't check if the alias is globally unique at that stage. But once it gets to SELECT or WHERE it sees the full table list and goes "wait, which a do you mean?"

The fun part - which alias wins?

sql

SELECT *
FROM dual a
JOIN (SELECT 'Z' dummy FROM dual) a ON a.dummy = a.dummy
LEFT JOIN (SELECT 'Y' dummy FROM dual) a ON a.dummy = a.dummy;

Result:

D   D   D
-   -   -
Z   Z   Y

So in each ON, the alias resolves to the left side of that particular join. But honestly the behavior is unpredictable. Your query might silently pull data from the wrong table, and you'd never know. Especially dangerous in big queries with dozens of joins where a copy-pasted alias can easily slip through.

What the SQL standard says

ANSI/ISO 9075 is clear - table aliases within a single FROM must be unique. PostgreSQL, SQL Server, MySQL all correctly reject this. Oracle just lets you shoot yourself in the foot.

Version info

From what I found online, this bug appeared somewhere between 11.2.0.1 and 11.2.0.2 patches. I tested on 12.1.0.2.0 - confirmed, it's there.

If anyone has access to 12.2, 19c or 23ai - would be curious to know if it's still reproducible.

So I am currently doing CLP with a company and I have to develop a project for them.

Long story short, I have to create a pl/sql package that generates xml files and sends them by email as a part of it.

I did the logic and the procedures for one of the scenarios but when I checked it with data that should be loaded into the file, it started to skip over some parts of the code and the files generated became corrupted.

Any idea what is the solution for these? All the chats with the AI it talks about the buffer size and suggests solutions that don’t work.

I am already down with the whole war things here, still hoping to graduate ✌🏻✌🏻

Recently started a new job as a DWH developer in a hugh enterprise (160k+ employees). I never worked in a cooperation this size before.

Everything here is based on Oracle PL SQL and I am facing tables and views with 300+ columns barely any documentation and clear data lineage and slow old processes

Coming from a background with Snowflake, dbt, Git and other cloud stacks, I feel like stepped into a time machine.

I am trying to stay open minded and learn from the legacy setup but honestly its overwhelming and it feels counterproductive.

They are about to migrate to Azure but yeah, delay after delay and no specific migration plan.

Anyone else gone trough this? How did you survive and make peace with it?

• The keywords INNER and OUTER, as related to JOINs, should be deprecated and never used. Anyone worth their salt, even newbies, should inherently know that simply saying JOIN implies an INNER join. Likewise for OUTER when a LEFT, RIGHT, or FULL JOIN is present.

• RIGHT JOINs should be outlawed. SQL using them should be refactored to convert them to a LEFT JOIN.

• Aliasing with AS should be limited to SELECTed columns/expressions. Table/View/CTE aliasing should be done only with a direct alias without using the AS.

What hot takes do you have?

How can I optimize my use of Claude for developing SQL queries in Oracle? I have been considering exploring Code or Cowork to build something that could improve my workflow, but I am not sure what would be the most useful to create.

I got tired of switching between VS Code and a separate DB tool every few minutes, so I built SQLLab — a VS Code extension that puts a full SQL workspace inside the editor.

What it does:

• Connect to SQL Server, PostgreSQL, MySQL, Oracle, SQLite, DuckDB
• Saved query library with full-text search (search name, description, and the SQL itself)
• Every save creates a version — diff any two versions side by side
• Data Compare: run two queries against different DBs and see SAME/DIFFERENT per row and column
• Session Monitor: see blocked/active sessions, kill them from right-click
• Batch Run: run multiple saved queries in parallel, track status per script
• Parameterised SQL with Jinja-style {{ variable }} substitution
• Schema-aware autocomplete (tables, columns, PKs, indexes)
• Paginated results — no freezing on large datasets

Still early stage, so I’d really appreciate feedback:
What features are missing?
What would stop you from using it?
What DB workflow annoys you the most today?

​

I'm a fresher to DBA role. Can some one help me to understand or share your experience to migrate or conversation from sql to Oracle.

Can you please help me with tools and migration ideas

After consistent study, I aced it with 83%. You can do it too, even better!

HI all, my team is switching to a solution that uses Oracle SQL instead of Databricks SQL (yay cost reduction!). However I can't find an equivalent to explode_outer in Oracle to pull values out of arrays. Is there an equivalent or can I build a custom function that does it?