Geek asks and answers

Do not repeat yourself: the same SQL query, but two different tables

I have an SQL query with exactly the same code, but two different tables (AUDIT_TRAIL_ARCHIVE and AUDIT_TRAIL). I use "UNION ALL" to get one result.

Good programmers use Don't Repeat Yourself . Good programmers avoid WET (write everything twice).

How to rewrite this code with the principle of "Do not repeat yourself"?

SELECT REPLACE (ENTITY_KEY, 'rss_user_name=CN=', '')
 FROM    AUDIT_TRAIL_ARCHIVE AU
   INNER JOIN
      (SELECT RSS_USER_NAME
         FROM RSS_USER
        WHERE RSS_NAME = 'rmad'
              AND ADD_INFO_MASTER LIKE '%__47__UPN=%@richemont.com%') FALSCH
   ON REPLACE (AU.ENTITY_KEY, 'rss_user_name=CN=', '') =
         FALSCH.RSS_USER_NAME
WHERE     AU.RSS_NAME = 'rmad'
   AND AU.TABLE_NAME = 'rss_user'
   AND AU.ACTION = 'Insert'
   AND AU.ENTITY_KEY LIKE 'rss_user_name=CN=%'
   AND AU.ORIGIN != 'RSS'
UNION ALL
SELECT REPLACE (ENTITY_KEY, 'rss_user_name=CN=', '')
 FROM    AUDIT_TRAIL AU
   INNER JOIN
      (SELECT RSS_USER_NAME
         FROM RSS_USER
        WHERE RSS_NAME = 'rmad'
              AND ADD_INFO_MASTER LIKE '%__47__UPN=%@richemont.com%') FALSCH
   ON REPLACE (AU.ENTITY_KEY, 'rss_user_name=CN=', '') =
         FALSCH.RSS_USER_NAME
WHERE     AU.RSS_NAME = 'rmad'
   AND AU.TABLE_NAME = 'rss_user'
   AND AU.ACTION = 'Insert'
   AND AU.ENTITY_KEY LIKE 'rss_user_name=CN=%'
   AND AU.ORIGIN != 'RSS'
+5
source share
3 answers

For instance:

SELECT REPLACE (ENTITY_KEY, 'rss_user_name=CN=', '')
 FROM    (select * --or relevant columns
          from AUDIT_TRAIL_ARCHIVE AU
          union all
          select * 
          from AUDIT_TRAIL AU
           ) AU
   INNER JOIN
      (SELECT RSS_USER_NAME
         FROM RSS_USER
        WHERE RSS_NAME = 'rmad'
              AND ADD_INFO_MASTER LIKE '%__47__UPN=%@richemont.com%') FALSCH
   ON REPLACE (AU.ENTITY_KEY, 'rss_user_name=CN=', '') =
         FALSCH.RSS_USER_NAME
WHERE     AU.RSS_NAME = 'rmad'
   AND AU.TABLE_NAME = 'rss_user'
   AND AU.ACTION = 'Insert'
   AND AU.ENTITY_KEY LIKE 'rss_user_name=CN=%'
   AND AU.ORIGIN != 'RSS'
+4
source

Good programmers use the Do Not Repeat Yourself principle. Good programmers avoid WET (write everything twice).

Heh. I like this. thin.

, , , , :

CREATE [OR REPLACE] PROCEDURE <name_of_procedure> [ (<ENTITY_KEY_variable>) ]
IS
    <ENTITY_KEY=ENTITY_KEY_variable>
BEGIN
    <your code goes here>

    [EXCEPTION
        exception_section]
END [procedure_name];

edit: , ? .

0

You just can't. SQL is a compiled language (even if it looks like scripts), and Oracle remembers the OBJECT_ID that the query depends on. Each half of the request has different dependencies, different "byte codes" and a different execution plan.

You can

  • Use table space partitioning. Then you will have only one table. Requests against live data can be limited using "select * from AUDIT_TRAIL partition ACTIVE".

  • Use query factoring, for example

    WITH AU AS
(SELECT * from AUDIT_TRAIL union all select * from AUDIT_TRAIL_ARCHIVE)
SELECT REPLACE (ENTITY_KEY, 'rss_user_name=CN=', '')
FROM AU JOIN ...
...

    But I'm not sure if Oracle will guarantee the same efficiency of the execution plan in this case.

0
source

More articles:


All Articles