Hive Spark Sql Optimizer

Output Mode Routing

Default mode:

• Always use concise mode unless the user explicitly requests detailed output.
• Concise mode must use references/output-template.md (Concise v2 four-section format).
• In concise mode, report must begin with a TL;DR section before the core sections.

Detailed mode triggers:

• Use detailed mode only when the user clearly asks for "detailed", "full", "verbose", "复杂版", "详细版", or equivalent wording.
• Detailed mode must use references/output-template-full.md.
• In detailed mode, report must begin with a TL;DR section before the detailed sections.

Conflict handling:

• If user intent is ambiguous, keep concise mode by default.
• If user explicitly asks for both speed and full detail, prioritize user-stated detail requirement.

Rewrite Visibility Routing

Default rules:

• Use shared change identifiers across report and SQL: M01... for major rewrites, S01... for minor rewrites.
• Ensure every physical rewrite appears at least once in report traceability fields (for example, 改了什么) or SQL inline comments.

Major rewrite triggers (any one is enough):

• Semantic risk score is >= 2.
• Expected gain is >= 20%.
• Structural rewrite exists (join/aggregation/window/union/dedup/CTE responsibility re-layout).

Major rewrite rendering:

• When a major rewrite is localizable, render inline SQL comments before changed snippets:
  • -- [Mxx] <中文短句：改动点 + 目的>
• When a major rewrite is non-localizable (for example, cross-CTE responsibility re-layout), add a header-level Mxx summary comment and explain scope under 改了什么.
• Keep each major rewrite comment to one sentence.

Minor rewrite rendering:

• Any rewrite not classified as major is minor by default.
• Render minor rewrites using the same locality-first strategy:
  • Localizable minor rewrites use inline comments: -- [Sxx] <中文短句：改动点 + 目的>
  • Non-localizable minor rewrites use header-level summary comments and must be explained under 改了什么
• Keep each minor rewrite comment to one sentence.

No-major handling:

• If no major rewrite exists and no proposal-level major plan is needed, state the no-major reason in report traceability fields.
• In concise mode, place this explanation under 改了什么 or 有什么影响.

Complexity Rules

Mark SQL as high when any condition is true:

• Join count is 4 or more.
• Nested subquery depth is 3 or more.
• SQL contains multi-branch UNION or UNION ALL.
• SQL couples window functions with deduplication or heavy aggregation.
• SQL shows a clear data-skew risk (hot keys or extreme key imbalance).

When SQL is high complexity, return:

• One main rewrite plan.
• One backup rewrite plan with a different optimization path.

Risk and Benefit Gate

Use medium-constraint semantics protection:

• Allow medium/high-risk rewrites only when expected benefit is at least 20%.
• Downgrade to conservative edits when expected benefit is below 20%.
• If semantics uncertainty remains high, provide suggestion-only output and explain why.

Use this decision priority:

1. Correctness first.
2. Explainability second.
3. Performance gain third.
4. Rewrite aggressiveness fourth.

Hard Constraints (Summary)

• Keep correctness as the first priority in all rewrite decisions.
• Apply the risk gate defined in ## Risk and Benefit Gate; keep conservative or suggestion-only output when the gate is not met.
• Never write Spark configuration into SQL or code; provide Spark tuning as optional environment recommendations only.
• Always use concise output by default with references/output-template.md.
• Switch to references/output-template-full.md only when the user explicitly requests detailed output.
• Report output must begin with TL;DR and include three non-empty bullets: 主要改动, 核心收益, and 风险结论.
• Make rewrites traceable: the report and SQL must expose change_id mapping for all material edits, even when no standalone major map section is used.
• For detailed rewrite rules (correctness, logic, performance, readability), follow references/rules.md as the single source of truth.
• For anti-pattern conversion strategies, follow references/patterns.md.

Maintenance Convention

• SKILL.md owns workflow, complexity routing, and hard-constraint summaries only.
• references/rules.md owns detailed rewrite conventions and style/performance rules.
• references/patterns.md owns anti-pattern rewrite strategies.
• When detailed rule text changes, update reference files first and keep SKILL.md summary-level to avoid duplication drift.

Trigger Examples

• "Optimize this SQL."
• "优化这个 SQL。"
• "Review this ETL SQL and rewrite it for Spark 3.2 performance."
• "Find logic/correctness issues in this query and provide an optimized version."
• "This join-heavy SQL is slow, give me a main plan and a backup plan."