create10

create10

A dynamic SQL query scans timestamp columns across tables to find recent data, leveraging XMLTABLE for cross-table analysis. It dynamically identifies tables with INTERNTIMESTAMP columns and counts rows from the last 45 days.

Why This Matters

The query addresses the technical reality of fragmented data schemas where timestamped records are spread across multiple tables. While ideal models assume centralized data, real-world systems often require cross-table analytics. This approach, however, incurs performance overhead due to repeated dynamic SQL generation and XML parsing, which can degrade scalability in large datasets.

Key Insights

• “Dynamic table scanning with XMLTABLE, 2025”: Demonstrates Oracle’s use of XMLTABLE for runtime query generation.
• “Cross-table analytics for timestamped data”: The query aggregates activity metrics across heterogeneous tables using a shared timestamp column.
• “Oracle DBMS_XMLGEN for dynamic SQL”: The script uses Oracle-specific functions to generate and execute ad-hoc queries.

Working Example

SELECT
utc.table_name,
xt.rows_last_45_days
FROM (
SELECT table_name
FROM user_tab_columns
WHERE UPPER(column_name) = 'INTERNTIMESTAMP'
AND data_type LIKE 'TIMESTAMP%'
) utc
CROSS JOIN XMLTABLE(
'/ROWSET/ROW/C/text()'
PASSING DBMS_XMLGEN.GETXMLTYPE(
'SELECT COUNT(*) c FROM "' || utc.table_name ||
'" WHERE INTERNTIMESTAMP >= SYSDATE - 45'
)
COLUMNS rows_last_45_days NUMBER PATH '.'
) xt
ORDER BY xt.rows_last_45_days DESC NULLS LAST, utc.table_name;

Practical Applications

• Use Case: Monitoring recent activity across legacy databases with inconsistent schemas.
• Pitfall: Overuse of XMLTABLE and dynamic SQL can lead to query plan instability and performance bottlenecks.

References:

• https://dev.to/query_filter_591122b53770/create10-bef

---